Skip navigation links

MySQL Forums :: Database Design & Data Modelling :: store SET time_zone as part of a VIEW statement


Advanced Search

Re: store SET time_zone as part of a VIEW statement
Posted by: Rick James ()
Date: April 07, 2012 12:15PM

What datatype are you using?

If you store everything in TIMESTAMP fields (and have the timezone for the _server_ set correctly), then everything should just work.

Here's what happens for TIMESTAMP, _not_ for DATETIME:
INSERT ... -- the date+time you have (such as NOW()) is converted to UTC based on the server's timezone. This value is stored in the table as seconds since 1970. (You never see this value).
SELECT ... -- the reverse process is used. The unixtime in the table is converted the other direction according to the server's timezone.
If you are on the same machine, the SELECT comes back with exactly what you put in. If you are in a different timezone, you get a different time (and possibly a different date).

OTOH, if a noon event in Australia should be displayed as noon for the US user, then DATETIME is a better choice. Think of it as storing a picture of the clock on the wall.

Options: ReplyQuote


Subject Written By Posted
store SET time_zone as part of a VIEW statement kavasmlikon kavasmlikon 04/05/2012 06:06PM
Re: store SET time_zone as part of a VIEW statement Rick James 04/07/2012 12:15PM
Re: store SET time_zone as part of a VIEW statement kavasmlikon kavasmlikon 04/10/2012 02:33AM
Re: store SET time_zone as part of a VIEW statement Peter Brawley 04/08/2012 01:18PM
Re: store SET time_zone as part of a VIEW statement kavasmlikon kavasmlikon 04/10/2012 02:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.