template1=# select '2006-03-01 10:49 America/New_York'::timetz;
  timetz
-------------
10:49:00-05


This is slightly misleading though, as the result isnt really america/new_york and the transform wont go back the other direction. (think of the insertion side of the coin)

There was talk awhile ago of storing actual timezone identifiers of
some kind in timestamptz and timetz values.  If that ever gets done
then I think '16:40 America/New_York' would be a useful value of
timetz --- for instance, "date plus timetz" could yield a meaningful
timestamptz.

This is probably the way this should be handled.

....

Here is the use case I ran into a while ago trying to use all this stuff.

I used to work for a VoIP company; at that company we were trying to setup after-the-fact selection rules (think calculating a calling-invoice) that applied during a specific time period @ a specific place. Eg calls that occured in the evening in Vancouver. (6pm+ say). The storage of this data was insufficient with a timetz as it would try to solve a gmt offset for the time on insert. This wasnt valid, and when dst rolled around there would be a problem and the calcs would be out by an hour. What we ended up doing was storing 3 cols, (time,time,varchar) and using a stored proc to calculate, but it was far from ideal.

If a proper timetz implementation is added, it should be mindful of this use case.

The ability to see if timestamptz falls between two timetz rules is what this case boils down to and has implications for anything that operates with hourly precision within dst zones.

Kevin

----- Original Message ----- From: "Joachim Wieland" <[EMAIL PROTECTED]>
To: "Tom Lane" <[EMAIL PROTECTED]>
Cc: <pgsql-patches@postgresql.org>
Sent: Thursday, June 01, 2006 11:36 AM
Subject: Re: [PATCHES] TODO-Item: full timezone names


On Thu, Jun 01, 2006 at 12:35:44PM -0400, Tom Lane wrote:
Joachim Wieland <[EMAIL PROTECTED]> writes:
> I'm talking about the timetz type that does not carry a date. So you > don't > know if daylight savings time is active or not. How would you interpret > the
> full timezone in this case without a date?

Oh, doh, I managed to miss that detail.  Yeah, you're right, you need an
arbitrary assumption in that case.  Or we could forbid these timezones
in timetz input, but that's probably not very helpful.

After sending my last mail, I concluded that it was in fact me who missed
something and that you were right. I came to the conclusion that you were
talking about the fact that you can specify a timetz also with a date:

template1=# select '2006-06-01 10:49 America/New_York'::timetz;
  timetz
-------------
10:49:00-04

This date can then be used to infer the timezone:

template1=# select '2006-03-01 10:49 America/New_York'::timetz;
  timetz
-------------
10:49:00-05

I have updated my patch to do so. Just specifying a timestamp

select '10:49 America/New_York'::timetz;

does now return an error.

Is that a suitable compromise?



Joachim




--------------------------------------------------------------------------------



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Attachment: smime.p7s
Description: S/MIME cryptographic signature

Reply via email to