Re: 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.
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(dot)mohekey(at)telogis(dot)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.

On Tue, Sep 15, 2009 at 5:29 AM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Scott Mohekey <scott(dot)mohekey(at)telogis(dot)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
>

Scott Mohekey <scott(dot)mohekey(at)telogis(dot)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(dot)mohekey(at)telogis(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david(at)fetter(dot)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(dot)Grittner(at)wicourts(dot)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(at)fetter(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter <david(at)fetter(dot)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

Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:
>>>>>> ""Kevin" == "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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(at)kineticode(dot)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(dot)Grittner(at)wicourts(dot)gov> writes:
> Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)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(dot)Grittner(at)wicourts(dot)gov> writes:

>> 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.

Kevin> Codd went on at some length about why this is the right thing
Kevin> to do. He was highly critical of systems where adding a month
Kevin> to a date and then subtracting month from the result could
Kevin> result in a date which was off from the original date by as
Kevin> much as three days. As a mathematician he felt strongly that
Kevin> "(x + y) - y" should equal x -- even when x is a date and y is
Kevin> 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 TIMESTAMP
Kevin> WITH TIME ZONE data type would go a long way toward making
Kevin> 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(at)sss(dot)pgh(dot)pa(dot)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(at)tao11(dot)riddles(dot)org(dot)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(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Scott Mohekey <scott(dot)mohekey(at)telogis(dot)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
>

This is exactly what we do. All of our servers are set to GMT time, all
times in the database are stored in GMT time, and are converted to a users
local time within the application.
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.