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?
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?
Thank you for correcting me on this.Stephan van Hulst wrote:
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.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?
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".
Can you give me an example of a query with a time zone?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.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?
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?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.
Thanks, I'll look those up.If you want to compare a local time to a TIMESTAMP column, you can use CONVERT_TZ() or AT TIME ZONE.
Carey Brown wrote: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?
Some people, when well-known sources tell them that fire will burn them, don't put their hands in the fire.
Some people, being skeptical, will put their hands in the fire, get burned, and learn not to put their hands in the fire.
And some people, believing that they know better than well-known sources, will claim it's a lie, put their hands in the fire, and continue to scream it's a lie even as their hands burn down to charred stumps.
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.
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.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.
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime. |