Обсуждение: Timestamp to time_t
Is it possible to convert from a Timestamp to time_t ? I see functions mentioned in utils/timestamp.h for converting between TimestampTz and time_t, but nothing for Timestamp.
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.
What is the relationship between Timestamp and TimestampTz?
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.
Scott Mohekey <scott.mohekey@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
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.
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@wicourts.gov> wrote:
Scott Mohekey <scott.mohekey@telogis.com> wrote:TIMESTAMP WITHOUT TIME ZONE does not identify a moment in time without
> What is the relationship between Timestamp and TimestampTz?
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
Scott Mohekey <scott.mohekey@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
On Tue, Sep 15, 2009 at 09:23:09AM -0500, Kevin Grittner wrote: > Scott Mohekey <scott.mohekey@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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@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
>>>>> ""Kevin" == "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: Kevin> TIMESTAMP WITH TIME ZONE is not completely ANSI-compliant, Given that the spec requires that 2009-01-31 + interval 1 month = 2009-02-31 (yes, really! see general rule 4 in subsection 6.30), I think we can safely ignore virtually everything it says about date/time handling. -- Andrew (irc:RhodiumToad)
On Tue, Sep 15, 2009 at 11:02:52AM -0400, Tom Lane wrote: > David Fetter <david@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()? I see that EXTRACT() can take a time zone as input, but I don't see anywhere that could distinguish among the following inputs, once stored, as they have identical representations in UTC: SELECT now() AS "West Oakland", now() AT TIME ZONE 'UTC' AS "Greenwich", now() AT TIME ZONE 'Asia/Shanghai' AS "Pudong"; West Oakland | Greenwich | Pudong -------------------------------+----------------------------+----------------------------2009-09-15 08:27:00.306403-07 |2009-09-15 15:27:00.306403 | 2009-09-15 23:27:00.306403 (1 row) The way we store TIMESTAMP WITH TIME ZONE, the database converts to UTC, discarding the input time zone in the process. SQL:2008 appears to allow this, and doesn't appear to have a way to retrieve that input time zone once a TIMESTAMP WITH TIME ZONE field has been stored. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes: > On Tue, Sep 15, 2009 at 11:02:52AM -0400, Tom Lane wrote: >> EXTRACT()? > I see that EXTRACT() can take a time zone as input, but I don't see > anywhere that could distinguish among the following inputs, once > stored, as they have identical representations in UTC: See TIMEZONE_HOUR, TIMEZONE_MINUTE field specifications, in particular b) Otherwise, let TZ be the interval value of the implicit or explicit time zone associated with the<datetime value expression>. If <extract field> is TIMEZONE_HOUR, then the result is calculatedas EXTRACT (HOUR FROM TZ) Otherwise, the result is calculated as EXTRACT (MINUTE FROM TZ) I haven't tracked down whether the phrase "implicit or explicit time zone" is hiding any interesting weasel words, but it sure *looks* like you are supposed to be able to pull out the TZ offset. regards, tom lane
Andrew Gierth <andrew@tao11.riddles.org.uk> wrote: >>>>>> ""Kevin" == "Kevin Grittner" <Kevin.Grittner@wicourts.gov> > writes: > > Kevin> TIMESTAMP WITH TIME ZONE is not completely ANSI-compliant, > > Given that the spec requires that 2009-01-31 + interval 1 month = > 2009-02-31 (yes, really! see general rule 4 in subsection 6.30), I > think we can safely ignore virtually everything it says about > date/time handling. Codd went on at some length about why this is the right thing to do. He was highly critical of systems where adding a month to a date and then subtracting month from the result could result in a date which was off from the original date by as much as three days. As a mathematician he felt strongly that "(x + y) - y" should equal x -- even when x is a date and y is an interval. Of course, you need to support the whole, coherent set of operations for it to make sense; if you take this particular operation out of context and put it together with other operations which don't follow his coherent set of rules, it does look silly. Treating stored dates as an abstraction which is mapped to the actual calendar as needed is different, but hardly foolish. Such features would make it a bit easier for software, for example, to properly handle a court order that someone make an initial payment on a given date (say January 30th) and then the same day of each subsequent month until the amount is paid in full. From what review I've done of it, it holds together as a complete system; the question is how many little bits and pieces can be adopted into a fundamentally different system and still have them make sense. Personally, I think that including time zone in the TIMESTAMP WITH TIME ZONE data type would go a long way toward making some useful features work. -Kevin
On Sep 15, 2009, at 8:50 AM, Tom Lane wrote: > See TIMEZONE_HOUR, TIMEZONE_MINUTE field specifications, in particular > > b) Otherwise, let TZ be the interval value of the implicit > or explicit time zone associated with the <datetime value > expression>. If <extract field> is TIMEZONE_HOUR, then > the > result is calculated as > > EXTRACT (HOUR FROM TZ) > > Otherwise, the result is calculated as > > EXTRACT (MINUTE FROM TZ) > > I haven't tracked down whether the phrase "implicit or explicit time > zone" is hiding any interesting weasel words, but it sure *looks* like > you are supposed to be able to pull out the TZ offset try=# select extract(timezone_hour from '2001-02-16 20:38:40 America/ Los_Angeles'::timestamptz); date_part ----------- 0 (1 row) That doesn't look right. AFAICT, timestamptz converts the TZ to the default TZ and discards the specified TZ. Same with offsets: try=# select extract(timezone_hour from '2001-02-16 20:38:40 -08:00'::timestamptz); date_part ----------- 0 (1 row) Best, David
"David E. Wheeler" <david@kineticode.com> writes: > On Sep 15, 2009, at 8:50 AM, Tom Lane wrote: >> See TIMEZONE_HOUR, TIMEZONE_MINUTE field specifications, in particular > try=# select extract(timezone_hour from '2001-02-16 20:38:40 America/ > Los_Angeles'::timestamptz); You appear to be confusing what PG currently does with what the spec says. regards, tom lane
On Sep 15, 2009, at 10:17 AM, Tom Lane wrote: >> try=# select extract(timezone_hour from '2001-02-16 20:38:40 America/ >> Los_Angeles'::timestamptz); > > You appear to be confusing what PG currently does with what the spec > says. Sorry, I thought you were referring to what PostgreSQL does. Would I be wrong in thinking that the current behavior might be surprising to some? I mean, I'd really like a timestamptz that tracked the tz or offset that was used to create its value… I'm sure that's been on the ToDo list for time immemorial. Best, David
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > Andrew Gierth <andrew@tao11.riddles.org.uk> wrote: >> Given that the spec requires that 2009-01-31 + interval 1 month = >> 2009-02-31 (yes, really! see general rule 4 in subsection 6.30), I >> think we can safely ignore virtually everything it says about >> date/time handling. > Codd went on at some length about why this is the right thing to do. > He was highly critical of systems where adding a month to a date and > then subtracting month from the result could result in a date which > was off from the original date by as much as three days. As a > mathematician he felt strongly that "(x + y) - y" should equal x -- > even when x is a date and y is an interval. [ shrug... ] We *have* that property, for sane cases such as adding and subtracting a fixed number of days. For less sane cases, I would point out to Codd that the current calendar system was not designed by mathematicians, and trying to superimpose strict mathematical rules on it just leads to nonsense (like the spec's requirements). regards, tom lane
>>>>> "Kevin" == Kevin Grittner <Kevin.Grittner@wicourts.gov> writes: >> Given that the spec requires that 2009-01-31 + interval 1 month =>> 2009-02-31 (yes, really! see general rule 4 in subsection6.30), I>> think we can safely ignore virtually everything it says about>> date/time handling.Kevin> Codd wenton at some length about why this is the right thingKevin> to do. He was highly critical of systems where adding a monthKevin>to a date and then subtracting month from the result couldKevin> result in a date which was off from the originaldate by asKevin> much as three days. As a mathematician he felt strongly thatKevin> "(x + y) - y" should equal x-- even when x is a date and y isKevin> an interval. Mathematical elegance is all very well, but until you convince the real world to abandon inelegant concepts like months with unequal lengths, the database has to behave in ways that are useful within the constraints of actual practice. (To me, the fact that the spec's idea of 2009-01-31 + 1 month corresponds to a value that current_date will never be equal to is a far greater show-stopper.) To look specifically at timezones, the problem with the spec here is that it doesn't store _timezones_, it stores _timezone offsets_. So per the spec, (timestamp with time zone '2009-01-01 12:00:00 +0000' + interval 6 months) would be equal to '2009-07-01 12:00:00 +0000' (REGARDLESS of what the server's timezone is configured as), which is remarkably non-useful; also, the spec's idea of + interval 24 hours is equal to + interval 1 day, whereas here in the real world those aren't the same thing at all. Worse still, the spec defines the behaviour of DST as follows: the server has a specific timezone _offset_, that offset _changes_ on DST start/end, and conversions between timestamp w/o tz and timestamptz are done USING THE SERVER'S CURRENT OFFSET, NOT THE OFFSET AS IT WOULD HAVE BEEN AT THE TIME VALUE BEING CONVERTED. This is so wrong there aren't even words to describe how wrong it is.Kevin> Personally, I think that including time zone in the TIMESTAMPKevin> WITH TIME ZONE data type wouldgo a long way toward makingKevin> some useful features work. It would break far too many other things in the process. If you want to store both a timestamp and an associated timezone you can do it right now, using a composite type or two columns, with the advantage that you get semantics that you can rely on. -- Andrew.
Tom Lane wrote: > For less sane cases, I would point > out to Codd that the current calendar system was not designed by > mathematicians, and trying to superimpose strict mathematical rules on > it just leads to nonsense (like the spec's requirements). > > > He's not listening ... Strangely (or perhaps not), he stopped listening around the time I started working on Postgres ... cheers andrew
Tom Lane <tgl@sss.pgh.pa.us> wrote: > [ shrug... ] We *have* that property, for sane cases such as > adding and subtracting a fixed number of days. Adding and subtracting months is very common in business software. I have seen application bugs related to this many times. I suspect that such bugs would occur less often with a more abstract date type and a date normalization strategy for mapping to the calendar than it does with typical techniques; but it's not something I would propose that PostgreSQL move toward. (Well, maybe some day as a pgfoundry project or something, given that such a system could plug right in, but not as the default date handling -- for compatibility, if nothing else.) I was just reacting to the assertion that date abstraction was such a stupid thing to do that nothing else proposed in a document which supports it is worth considering. The Turing Award isn't usually awarded to those proposing complete nonsense. -Kevin
Andrew Gierth <andrew@tao11.riddles.org.uk> wrote: > (To me, the fact that the spec's idea of 2009-01-31 + 1 month > corresponds to a value that current_date will never be equal to is > a far greater show-stopper.) You get to pick which way you want to normalize that to the calendar -- 31 days past the start of the next month, or pulled back to the last day of the next month which is not greater than 31. The latter is more common, but I've seen both practices in real world business applications. -Kevin
On Sep 15, 2009, at 11:01 AM, Andrew Gierth wrote: > If you want to store both a timestamp and an associated timezone you > can do > it right now, using a composite type or two columns, with the > advantage that > you get semantics that you can rely on. How would a composite work in practice? Can you index it on the timestamp? Or would you have to use two columns for that? I could see a real advantage to a type that stored the TZ with which it was created, with the ability to fetch it back out. Internally the data could be stored just like it is with timestamptz, and by default, perhaps, it would display in $PGTZ, but if $PGTZ was set to a value like "original" or something, it should display the originals. Now *that* would be really useful IMHO. Best, David
On Wed, Sep 16, 2009 at 2:23 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:
Scott Mohekey <scott.mohekey@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.
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
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.