MySQL and time zones

[Carey Brown]

I've been reading up on MySQL's support of time zones and I'm a bit confused.

First off, I've been working with MySQL all this time with the 'time_zone' variable set to the default which is 'SYSTEM'.

It appears (unless I'm wrong) that all date, time, and timestamp fields assume the LOCAL ZONE values as set on the machine hosting the database (which at this point just happens to be my desktop). So, selects, inserts, and updates all work as expected so far with query expressions for time being in local time. I'm going to make a BIG ASSUMPTION here that internally MySQL stores all date/times in UTC (milliseconds since epoch?) even though my inputs and queries are in Mountain Standard Time (-7:00).

I'm planning a trip to NY (Eastern Standard Time (-5:00)) and I'm going to copy my database to my laptop to take with me. When I get there I will reset the Window's time zone to EST. While I'm there I will be making new inserts and performing queries. I'm thinking that inserts will take my dates/times in EST and store them in UTC and that my queries will also be in EST. So, for new inserts I can do new queries in LOCAL time and all's good. If I try to run a query on the data recorded in MST while I'm in EST I'd have to subtract 2 hours from my query times. Does this sound right?

If I wanted to do a MST query, could I first SET TIME_ZONE '-07:00' and then do the query as though I was back home?

This whole thing wouldn't matter so much except a few of the fields keep track of various file's modification times, which, while Windows displays this in local time, Java's File.lastModified() returns milliseconds since Epoch UTC. So far one of the things I've done is to store mtime as a biginteger(20) in the database and bypass the date/time thing altogether, this works but is not a useful long term solution, which is why I'm trying to puzzle this out now.

[Stephan van Hulst]

Carey Brown wrote:If I try to run a query on the data recorded in MST while I'm in EST I'd have to subtract 2 hours from my query times. Does this sound right?

Not really. Let's say that you inserted a row at noon in mountain time. The data will be recorded as 7 o'clock in the evening UTC. Then when you want to retrieve that specific row by local date while you're in New York, you'd ask for a row with time 2 o'clock in the afternoon.

To query the row in eastern time, you had to ADD 2 hours to the local time you used in mountain time to insert the row. But really, you didn't add any time to your queries at all, because 2 o'clock in the afternoon in New York IS noon in eastern time. When you retrieve the row that was inserted at noon in mountain time, and interpret its timestamp as eastern time, it will just say "2 o'clock in the afternoon".

If I wanted to do a MST query, could I first SET TIME_ZONE '-07:00' and then do the query as though I was back home?

I wouldn't do it. If you want to perform a query in a different time zone than your application/database time zone, then just specify the time zone explicitly in your query.

[Carey Brown]

Stephan van Hulst wrote:

Carey Brown wrote:If I try to run a query on the data recorded in MST while I'm in EST I'd have to subtract 2 hours from my query times. Does this sound right?

Not really. Let's say that you inserted a row at noon in mountain time. The data will be recorded as 7 o'clock in the evening UTC. Then when you want to retrieve that specific row by local date while you're in New York, you'd ask for a row with time 2 o'clock in the afternoon.

To query the row in eastern time, you had to ADD 2 hours to the local time you used in mountain time to insert the row. But really, you didn't add any time to your queries at all, because 2 o'clock in the afternoon in New York IS noon in eastern time. When you retrieve the row that was inserted at noon in mountain time, and interpret its timestamp as eastern time, it will just say "2 o'clock in the afternoon".

Thank you for correcting me on this.

If I wanted to do a MST query, could I first SET TIME_ZONE '-07:00' and then do the query as though I was back home?

I wouldn't do it. If you want to perform a query in a different time zone than your application/database time zone, then just specify the time zone explicitly in your query.

Can you give me an example of a query with a time zone?

I'm guessing

SELECT * FROM mytable WHERE mytime > '2021-01-01T12:00:00-0700'

Is that correct?

[Stephan van Hulst]

My first post assumed you used TIMESTAMP in your database and not DATETIME. If you used DATETIME then there's no way to know whether you've inserted a row in mountain time or in eastern time.

If you want to compare a local time to a TIMESTAMP column, you can use CONVERT_TZ() or AT TIME ZONE.

[Carey Brown]

Stephan van Hulst wrote:My first post assumed you used TIMESTAMP in your database and not DATETIME. If you used DATETIME then there's no way to know whether you've inserted a row in mountain time or in eastern time.

That touches on the other part of my question. If I use DATETIME does it not store the value in the database in UTC? If I pull the value out with Java into a sql.Date object it will ignore both the zone it was saved in and the zone it was queried in?

If you want to compare a local time to a TIMESTAMP column, you can use CONVERT_TZ() or AT TIME ZONE.

Thanks, I'll look those up.

[Stephan van Hulst]

Carey Brown wrote:If I use DATETIME does it not store the value in the database in UTC?

No. DATETIME is like LocalDateTime and has no associated time zone, not even UTC. It just stores whatever you give it in the database, without performing conversions.

If I pull the value out with Java into a sql.Date object it will ignore both the zone it was saved in and the zone it was queried in?

It depends on the JDBC driver implementation. Sorry.

[Tim Holloway]

You have 3 MySQL data types: DATE, DATETIME, and TIMESTAMP. DATE has a precision of one day whereas the other 2 have a precision of 1 microsecond. DATETIME and TIMESTAMP are identical in storage format, but TIMESTAMP assumes the UTC timezone while DATETIME is a "pure" timestamp with no assumed time zone. If you want to associate a DATETIME with an absolute/universal time, you have to carry the zone information externally.

You can reasonably expect that passing DATE or DATETIME through a java.sql.Date will give you an incorrect time - assuming you're not sitting in Campbell Ritchie's parlour*. No JDBC driver is going to be able to correct for a timezone when there is no timezone in the data to begin with. So if you use DATE or DATETIME, you'd need to be careful that what you're doing isn't dependent on absolute time or be able to correct using external knowledge. Nor, now that I think of it, is the driver going to be able to adjust for Daylight Savings.

The java.sql.Date and java.util.Date classes assume that you've constructed them using a UTC date/time. If you want anything else, you'll have to look at the Calendar/locale-related classes.

* When he's not on Summer Time, anyway.

[Paul Clapham]

Carey Brown wrote:I'm planning a trip to NY (Eastern Standard Time (-5:00)) and I'm going to copy my database to my laptop to take with me. When I get there I will reset the Window's time zone to EST. While I'm there I will be making new inserts and performing queries.

Quite apart from everything that's been posted so far, my initial feeling was "You could try this right now and see what happens." Unless you're already en route to EST, that is.

[Carey Brown]

Another one of those topics that seems like it should be simple but yet has all these dark corners.

[Carey Brown]

Paul Clapham wrote:

Carey Brown wrote:I'm planning a trip to NY (Eastern Standard Time (-5:00)) and I'm going to copy my database to my laptop to take with me. When I get there I will reset the Window's time zone to EST. While I'm there I will be making new inserts and performing queries.

Quite apart from everything that's been posted so far, my initial feeling was "You could try this right now and see what happens." Unless you're already en route to EST, that is.

I was starting to come to that conclusion myself. I'll have to setup a simple table and write some java code to exercise it.