How to get the last entry for each day using MySQL with CONVERT_TZ()

Question

So I'm using this code to get the last entry for each day from a database.

SELECT a.id, a.userid, a.jurisdiction, a.country, a.date
FROM dk_location_records AS a,
        (SELECT userid, DATE(date) AS just_date, MAX(date) AS date
            FROM dk_location_records
            GROUP BY 1, 2 --userid, DATE(date)
        ) AS b
WHERE a.userid = b.userid
AND a.date = b.date;

My question is, how can I incorporate something like: CONVERT_TZ( date, '+00:00', '+01:00' ) to get the last entry per day for a different timezone. I've so far managed to use the CONVERT_TZ to simply display the converted dates. However, can you use MAX() and CONVERT_TZ() together?

Answer 1

If you want to select a single row in an extreme, you can try it by ordering the resultset by the desired criteria and limiting the output to a single row. Something, perhaps, like

SELECT a.id, a.userid, a.jurisdiction, a.country, a.date
ORDER BY CONVERT_TZ( date, '+00:00', '+01:00') DESC
LIMIT 1

It should be fairly faster than doing a subquery table scan.

Although, I may be misunderstanding what you're trying to do here. Converting the timezone won't change the order of the dates. If you'd like to search for entries within a day in another timezone, it's possible to try

SELECT a.id, a.userid, a.jurisdiction, a.country, a.date
WHERE CAST(CONVERT_TZ(a.date, '+00:00', '+01:00') AS DATE) = CURDATE()
ORDER BY a.date DESC
LIMIT 1

Of course, modify '+01:00' (destination timezone) and CURDATE() (date you're searching for) to fit your needs.