HardlyNoticeable HardlyNoticeable - 1 year ago 187
SQL Question

MySQL; SELECT all records from yesterday (midnight to midnight) using a location's timezone

How do I SELECT all records from yesterday, from Midnight to Midnight, for a specific location's timezone (timestamps all in UTC and locations have named timezones - e.g. America/Vancouver)?

Answer Source

Assuming the timezone for your MySQL database session is UTC (i.e. time_zone='+00:00')...

And assuming that you want to use the same timezone for all of the rows (i.e. not different timezones based on contents of the row ...

Take the value for "midnight" in the user's specified timezone, and convert that to UTC. e.g. 2016-04-16 00:00 CST6CDT (i.e. America/Chicago) converts to 2016-04-16 05:00 UTC.

Assuming that your table column is named utc_timestamp_col, and is datatype TIMESTAMP, your query would look look something like this:

  FROM mytable t
 WHERE t.utc_timestamp_col  >= '2016-04-16 05:00' + INTERVAL -1 DAY
   AND t.utc_timestamp_col  <  '2016-04-16 05:00' + INTERVAL  0 DAY

If you have populated the MySQL timezone tables, you can make use of the MySQL support for named timezones. http://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html

You can build an expression that gets you previous "midnight" in a named timezone.

Below is a demonstration query:

  • verify session time_zone is UTC
  • return current date and time in UTC
  • convert to local time zone CST6CDT
  • truncate to midnight
  • convert back to UTC


SELECT @@session.time_zone AS `time_zone`
     , NOW()  AS `now_utc` 
     , CONVERT_TZ(NOW(),'UTC','CST6CDT')  AS `now_CST6CDT`
     , DATE(CONVERT_TZ(NOW(),'UTC','CST6CDT'))  AS `midnight_CST6CDT`
       AS midnight_CST6CDT_utc


time_zone now_utc             now_CST6CDT         midnight_CST6CDT midnight_CST6CDT_utc  
--------- ------------------- ------------------- ---------------- --------------------
UTC       2016-04-17 01:53:31 2016-04-16 20:53:31 2016-04-16       2016-04-16 05:00:00

Demonstrating the same thing, using time zone named 'America/Chicago'

SELECT @@session.time_zone  AS `time_zone`
     , NOW()  AS now_utc
     , CONVERT_TZ(NOW(),'UTC','America/Chicago')  AS `now_America/Chicago`
     , DATE(CONVERT_TZ(NOW(),'UTC','America/Chicago'))  AS `midnight_America/Chicago`
     , CONVERT_TZ(DATE(CONVERT_TZ(NOW(),'UTC','America/Chicago')),'America/Chicago','UTC')  
       AS `midnight_America/Chicago_utc`

returns the same result:

time_zone now_utc             now_America/Chicago midnight_America/Chicago  midnight_America/Chicago_utc  
--------- ------------------- ------------------- ------------------------  ----------------------------
UTC       2016-04-17 01:57:19 2016-04-16 20:57:19 2016-04-16                2016-04-16 05:00:00