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

Question

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)?

Please note that I'm not asking how to convert a date and time to another timezone. What I'm asking is how [best] to localize a date range comparison.

You probably need a query. Pls show us what you have tried and what went wrong with that. — Shadow, Apr 17 '16 at 00:30
I hadn't made a query yet; I'm not quite sure where to start. — HardlyNoticeable, Apr 17 '16 at 00:37
I don't think it is a duplicate. I need an INTERVAL filter that takes into account a timezone. I'm not just converting a time from one timezone to another. — HardlyNoticeable, Apr 17 '16 at 00:41
You are converting from utc to a timezone and a simple between. I presume, you know how to use between. — Shadow, Apr 17 '16 at 00:49
If you give me a working example query, I'll flag it as the correct answer. — HardlyNoticeable, Apr 17 '16 at 00:51
Since you have not put any effort in the question, I'm not interested to put any more effort into the answer. — Shadow, Apr 17 '16 at 01:26
Honestly I don't know what you want me to do. I think I've explained what I'm looking for and I don't have a query to start with. Am I not communicating what I'm looking for clearly enough? Do you want me to provide sample data? — HardlyNoticeable, Apr 17 '16 at 01:50
I recommend you *not* use BETWEEN with date range comparisons, because of the equality on both sides. That is, don't include records from the next interval. To get datetime/timestamp values (col) in a given day we want to use a "less than" comparison on the high side. i.e. `dt <= col < dt + interval 1 day`. (Valid points discussed here:[https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common) — spencer7593, Apr 17 '16 at 02:45
Given the straightforward nature of the question, I don't think example table and sample data is really required. If the problem was more complex, with timezone conversion being conditionally applied, some sample data and expected results would be prudent. (We'd need a setup for testing anyway.) The question would likely have been better received if there had at least been a `SELECT t.* FROM mytable t` query with an explanation that it's including/returning rows that aren't wanted. — spencer7593, Apr 17 '16 at 02:54

spencer7593 · Accepted Answer · 2016-04-17T02:24:12.197

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:

SELECT ...
  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

e.g.

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`
     , CONVERT_TZ(DATE(CONVERT_TZ(NOW(),'UTC','CST6CDT')),'CST6CDT','UTC')
       AS midnight_CST6CDT_utc

Returns:

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

FOLLOWUP

If I needed to do that kind of timezone conversion in the query, I would use an inline view to return the value from that fairly complicated expression, to make the outer statement simpler. For example, the inline view aliased as "d" returns the value as column named "dt", which can be referenced in the outer query.

Since that inline view returns exactly one row, we can use a JOIN to mytable without duplicating any rows. We can move the predicates from the WHERE clause to an ON clause. e.g.

SELECT ...
  FROM ( SELECT CONVERT_TZ(DATE(CONVERT_TZ(NOW(),'UTC','CST6CDT')),'CST6CDT','UTC') AS dt
       ) d
  JOIN mytable t
    ON t.utc_timestamp_col  >= d.dt + INTERVAL -1 DAY
   AND t.utc_timestamp_col  <  d.dt + INTERVAL  0 DAY

If the timezone conversion isn't consistent for every row in the table, or at least every row to be returned by the query, significant modifications would need to be made. (Keeping the where clause predicates on the bare table column makes it possible for MySQL to use a range scan operation on a suitable index.) If the time_zone for the MySQL database session is not set to UTC or +00:00, this same approach may work, substituting @@session.time_zone in place of the literal 'UTC'... no guarantee... would need to be tested. (Not sure what happens when time_zone is set to SYSTEM.) — spencer7593, Apr 17 '16 at 02:38

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

1 Answers1