Re: TODO-Item: full timezone names - Mailing list pgsql-patches

> 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" <joe@mcknight.de>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
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 <joe@mcknight.de> 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
>