Обсуждение: database design with timestamp
I'm new to database models, so thanks in advance! The database server is on the west coast, and this task is performed by a field technician on the east coast... TASK 1: (note- and overlaps a dst change at 1:30am) START : 2002-10-27 01:00-04 (05:00-GMT) END : 2002-10-27 02:00-05 (07:00-GMT) Postgres is cool - it stored them in GMT, and shows the offset based on the timezone in which I sit, which makes sense to me being physically here, but when I print a report for the manager on the EAST coast, its not going to make sense to them. TASK 1 : START : 2002-10-26 22:00-07 END : 2002-10-27 00:00-07 --------------------------- DURATION: 02:00 I think I need to somehow display these timestamps back into their timezone of origin...so as to be more intuitive for the managers. Some direction or an example solution would be great. Thanks, TJ
On Tue, Nov 05, 2002 at 20:03:37 +0000, TJ <tj@nospam.com> wrote: > > I think I need to somehow display these timestamps back into their timezone > of origin...so as to be more intuitive for the managers. You can use SET TIME ZONE in a session to change the time zone used for outputting time. For example: area=> select current_time; timetz -------------------- 10:03:02.390849-06 (1 row) area=> set time zone 'PST8PDT'; SET area=> select current_time; timetz -------------------- 08:03:19.241939-08 (1 row)
Bruno Wolff III <bruno@wolff.to> writes: > On Tue, Nov 05, 2002 at 20:03:37 +0000, > TJ <tj@nospam.com> wrote: >> I think I need to somehow display these timestamps back into their timezone >> of origin...so as to be more intuitive for the managers. > You can use SET TIME ZONE in a session to change the time zone used for > outputting time. BTW, a lot of people don't realize at first that PG's notion of timekeeping is similar to the Unix model: it's all UTC under the hood, and timezones are just syntactic sugar for input and output. IMHO this makes it a lot easier to do databases that will be used by people in multiple timezones: the stored data is really UTC, but each person can enter times in his own zone, and will see times displayed in his own zone (where "your own zone" means "whatever you've currently got TimeZone set to"). regards, tom lane