mysql HOUR() accounting for timezones

Question

I have a query that returns results grouped by the hour, like so.

SELECT COUNT(*) FROM (`shipped_products`) WHERE HOUR(timestamp) = 8

The issue is, I get different results on localhost than on the live server. The data is exactly the same on both (UTC). I believe that my localhost database is New York while the live Database is UTC, and because of this, HOUR() is working differently on the two environments. I don't have permission to change the live server timezone, and would like to avoid changing the local database timezone. Is there a way inside the query to ensure the HOUR() function is returning the same results regardless of database timezone?

Answer 1

Try the CONVERT_TZ() function:

SELECT COUNT(*) FROM (`shipped_products`)
WHERE HOUR(CONVERT_TZ(timestamp, 'UTC', 'America/New York')) = 8

The manual warns:

Note

To use named time zones such as 'MET' or 'Europe/Moscow', the time zone tables must be properly set up. See Section 10.6, “MySQL Server Time Zone Support”, for instructions.

You can also use hour values instead, but will lose any timezone knowledge such as historic timezone changes, DST, etc.

SELECT COUNT(*) FROM (`shipped_products`)
WHERE HOUR(CONVERT_TZ(timestamp, '+00:00', '-05:00')) = 8

Answer 2

You could just set the session timezone (or the global time zone) to UTC, as described in the docs.

set time_zone = '+00:00';

Then HOUR and other functions will interpret timestamp types in UTC. See also the differences between timestamp and datetime in this part of the docs.

However, another concern is that WHERE HOUR(timestamp) = 8 is going to make your query non-sargable, so it will have to scan the entire table, and won't be able to take advantage of any indexes you may have. The more records in the table, the slower this will get. (Especially if you have thousands or millions of records).

A better approach is to rewrite this as a range query. Get the starting and stopping times you want to filter by, and use a half-open interval.

WHERE timestamp >= @start AND timestamp < @end

Of course, you will lock down the value to a specific date when you calculate the start and end values. If instead you really do want the 8:00 UTC hour of every day, then you should create a separate integer column on the table with that information, so you can use it in a sargable query.

WHERE your_utc_hour_column = 8

This could be a computed column in some databases, but I don't think those are supported in MySQL, so you'd have to create and populate it manually.