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

Written By
Re: store SET time_zone as part of a VIEW statement
April 07, 2012 12:15PM

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.