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?

Naltharial · Accepted Answer · 2011-07-18T12:57:28.927

0

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.

edited Jul 18 '11 at 12:57

answered Jul 18 '11 at 06:32

Naltharial

2,132
14
21

Hi Nalthariel, thanks for this. I'll try to explain it a bit more. At the moment, all of the dates are in UTC. The app I'm building stores multiple entries of data for each day. I want to go through and get the last entry from every single day and just output the information from it. The above works well if we assume I'm talking about the last entry in UTC. But, of course, the last entry if I were in Singapore would be different. So, using the same data set, I need to get the last entry from each day from different timezones... – Jack Lenox Jul 18 '11 at 12:34
...So, an example. I have an entry that says I'm in the UK at 11:58pm on 17 July in UTC (ignoring daylight savings time for now). In Singapore, this same entry would show me as being in the UK at 7:58am on 18 July. Therefore, in Singapore's timezone, this is not the last entry of the day as there might be another entry at 3:58pm, 18 July (UTC) which would be 11:58pm, 18 July (Singapore). I appreciate this is a bit mad but I think, if I can do this, this is the best way to achieve what it is I'm trying to do. – Jack Lenox Jul 18 '11 at 12:35
In that case, the above should work if you add a conditional constraint on the resultset, defining the day you're trying to restrict to, as I've added to the answer. – Naltharial Jul 18 '11 at 12:50
Thanks Naltharial. This is great. Combined with something like that seen at the link shown I can do this in a much more efficient, less database intensive way: http://edrackham.com/php/get-days-between-two-dates-using-php/ – Jack Lenox Jul 19 '11 at 17:01

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

1 Answers1