Timestamp to time_t
in [PgSql]
|
From: "Kevin Grittner" on 14 Sep 2009 13:29 Scott Mohekey <scott.mohekey(a)telogis.com> wrote: > What is the relationship between Timestamp and TimestampTz? TIMESTAMP WITHOUT TIME ZONE does not identify a moment in time without first associating it with a time zone. When Daylight Saving Time ends, the same TIMESTAMP WITHOUT TIME ZONE values will recur, without any way to distinguish them from those from the previous hour. The only use case I have been able to think of, personally, for TIMESTAMP WITHOUT TIME ZONE is a release date for, say a Harry Potter book or World of Warcraft expansion set, where as the given moment arrives in each time zone, stores in that time zone can begin to sell the given work. I suspect there's probably one or two other valid uses, but most uses are just mistakes, waiting to be exposed. For almost every reasonable use, the right data type is TIMESTAMP WITH TIME ZONE. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Scott Mohekey on 14 Sep 2009 18:12 I think the issue is that we treat TIMESTAMP WITHOUT TIME ZONE as TIMESTAMP at GMT. We then convert it to a users local timezone within application code. Scott Mohekey Systems/Application Specialist OnTrack Telogis, Inc. www.telogis.com www.telogis.co.nz +1 949 625-4115 ext. 207 (USA) +64 3339 2825 x207 (NZ) Leading Global Platform for Location Based Services -- This e-mail, and any attachments, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. It is the property of Telogis. If you are not the intended recipient of this e-mail, you are hereby notified that any dissemination, distribution or copying of this e-mail, any attachments thereto, and use of the information contained, is strictly prohibited. If you have received this e-mail in error, please notify the sender and permanently delete the original and any copy there of. On Tue, Sep 15, 2009 at 5:29 AM, Kevin Grittner <Kevin.Grittner(a)wicourts.gov > wrote: > Scott Mohekey <scott.mohekey(a)telogis.com> wrote: > > > What is the relationship between Timestamp and TimestampTz? > > TIMESTAMP WITHOUT TIME ZONE does not identify a moment in time without > first associating it with a time zone. When Daylight Saving Time > ends, the same TIMESTAMP WITHOUT TIME ZONE values will recur, without > any way to distinguish them from those from the previous hour. > > The only use case I have been able to think of, personally, for > TIMESTAMP WITHOUT TIME ZONE is a release date for, say a Harry Potter > book or World of Warcraft expansion set, where as the given moment > arrives in each time zone, stores in that time zone can begin to sell > the given work. > > I suspect there's probably one or two other valid uses, but most uses > are just mistakes, waiting to be exposed. For almost every reasonable > use, the right data type is TIMESTAMP WITH TIME ZONE. > > -Kevin >
From: "Kevin Grittner" on 15 Sep 2009 10:23 Scott Mohekey <scott.mohekey(a)telogis.com> wrote: > I think the issue is that we treat TIMESTAMP WITHOUT TIME ZONE as > TIMESTAMP at GMT. We then convert it to a users local timezone > within application code. That sounds like an accident waiting to happen. Sure, you can make it work, but you're doing things the hard way, and the defaults will probably be to do the wrong thing. TIMESTAMP WITH TIME ZONE is not completely ANSI-compliant, in that it doesn't store a time zone with the timestamp. What it does do is store the timestamp in GMT, so that it represents a moment in time, changing the representation of the moment to local time in any time zone as needed. This sounds a lot like what you're trying to do -- a natural fit. If you want to see it in GMT, that easy enough. If you want to see it as local time in any other time zone, that's easily done without risk of actually getting a timestamp representing the wrong moment. TIMESTAMP WITHOUT TIME ZONE is stored "raw" and is not considered to be associated to a time zone until you do so. It will default to assigning the time zone set on your server, which is normally your local time zone. Unless that's GMT, you will need to be very careful to always localize the timestamp to GMT before doing anything with it. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: David Fetter on 15 Sep 2009 10:40 On Tue, Sep 15, 2009 at 09:23:09AM -0500, Kevin Grittner wrote: > Scott Mohekey <scott.mohekey(a)telogis.com> wrote: > > I think the issue is that we treat TIMESTAMP WITHOUT TIME ZONE as > > TIMESTAMP at GMT. We then convert it to a users local timezone > > within application code. > > That sounds like an accident waiting to happen. Sure, you can make > it work, but you're doing things the hard way, and the defaults will > probably be to do the wrong thing. > > TIMESTAMP WITH TIME ZONE is not completely ANSI-compliant, in that > it doesn't store a time zone with the timestamp. I've looked through SQL:2008 (well, through 6WD2_02_Foundation_2007-12.pdf), and I didn't find anything that implies that the input time zone needs to be retrievable, nor anything that would specify the syntax for doing so. Can you point me to a section? Lots of people, including your humble emailer, would find it very handy to be able to access such information, but I thought TIMESTAMP WITH TIME ZONE only needed to be retrieved either as default time zone, or as whatever AT TIME ZONE specified. Cheers, David. -- David Fetter <david(a)fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter(a)gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 15 Sep 2009 11:02
David Fetter <david(a)fetter.org> writes: > I've looked through SQL:2008 (well, through 6WD2_02_Foundation_2007-12.pdf), > and I didn't find anything that implies that the input time zone needs > to be retrievable, nor anything that would specify the syntax for > doing so. EXTRACT()? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |