Timestamp to time_t
in [PgSql]
|
From: Andrew Gierth on 15 Sep 2009 11:10 >>>>> ""Kevin" == "Kevin Grittner" <Kevin.Grittner(a)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) -- 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 11:32 On Tue, Sep 15, 2009 at 11:02:52AM -0400, Tom Lane wrote: > 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()? 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(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:50 David Fetter <david(a)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 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. 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
From: "Kevin Grittner" on 15 Sep 2009 12:10 Andrew Gierth <andrew(a)tao11.riddles.org.uk> wrote: >>>>>> ""Kevin" == "Kevin Grittner" <Kevin.Grittner(a)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 -- 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 E. Wheeler" on 15 Sep 2009 12:34 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: WIP: generalized index constraints Next: PGCluster-II Progress |