SEO

Hi,

I propose the appended patch for the Todo item:

o Allow timezone names in SQL strings, '2006-05-24 21:11
Americas/New_York'::timestamptz

I changed the ParseDateTime function as well as DecodeDateTime to support
those timezones in timestamps and DecodeTimeOnly to support it in the
timetz type.

'2006-05-24 21:11 America/New_York'::timestamptz is now the same as
'2006-05-24 21:11'::timestamptz at time zone 'America/New_York'

With a timetz it's more tricky, because "America/New_York" does not specify
a timezone offset by itself, this could change due to daylight savings time
for example. So my idea was to apply whatever offset is valid in this region
at the moment of parsing the string representation.

It passes all existing regression tests, I will supply additional regression
tests as well as documentation patches as soon as you have told me that I'm
on the right track :-)


Joachim


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Joachim Wieland <joe@mcknight.de> writes:
> With a timetz it's more tricky, because "America/New_York" does not specify
> a timezone offset by itself, this could change due to daylight savings time
> for example. So my idea was to apply whatever offset is valid in this region
> at the moment of parsing the string representation.

You can't be serious. The correct interpretation of
'2006-06-01 10:49 America/New_York'
has to be 10:49 in whatever time was then in use in New York. Not when
you read the string.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Thu, Jun 01, 2006 at 11:00:12AM -0400, Tom Lane wrote:
> Joachim Wieland <joe@mcknight.de> writes:
> > With a timetz it's more tricky, because "America/New_York" does not specify
> > a timezone offset by itself, this could change due to daylight savings time
> > for example. So my idea was to apply whatever offset is valid in this region
> > at the moment of parsing the string representation.

> You can't be serious. The correct interpretation of
> '2006-06-01 10:49 America/New_York'
> has to be 10:49 in whatever time was then in use in New York. Not when
> you read the string.

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?


Joachim

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

I'll chime in here, seeing as I pointed out these bugs many months ago.

The concept of a timetz type is simply invalid. A timezone in order to work,
must have a date component. If you want a way to get a current timestamptz,
its a fairly simple thing to append the current date to a time and use the
at time zone modifier.

My vote is that you guys drop timetz completely.

The only possible use for a timetz type that i could see is to add a special
extract function to be able to get the timezone name from the type. Any kind
of date math should ignore this property, but the ability to store it would
mean only needing to store two columns not 3 in the scenario where you want
to define a daily timespan in a specific timezone. Eg timetz,timetz instead
of time,time,timezone(varchar)

Without a date, any math on timetz should be identical to a time type and
ignore timezone completely.

Syntactically, adding the ability to say 'on date' might be nice, but can
already be achieved with concatenations. eg select '6:00'::time at time zone
'Canada/Pacific' on '02-10-2006'::date; With that syntax a timetz could be
used with the on operation to make it valid and allow a cast to an adjusted
time.

In short, inferring the date from the time the string is read is bad bad
bad.

Kevin McArthur




----- 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 10:31 AM
Subject: Re: [PATCHES] TODO-Item: full timezone names


> On Thu, Jun 01, 2006 at 11:00:12AM -0400, Tom Lane wrote:
>> Joachim Wieland <joe@mcknight.de> writes:
>> > With a timetz it's more tricky, because "America/New_York" does not
>> > specify
>> > a timezone offset by itself, this could change due to daylight savings
>> > time
>> > for example. So my idea was to apply whatever offset is valid in this
>> > region
>> > at the moment of parsing the string representation.
>
>> You can't be serious. The correct interpretation of
>> '2006-06-01 10:49 America/New_York'
>> has to be 10:49 in whatever time was then in use in New York. Not when
>> you read the string.
>
> 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?
>
>
> Joachim
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

"Kevin McArthur" <Kevin@StormTide.ca> writes:
> My vote is that you guys drop timetz completely.

I can already give you the final score on that one:
SQL standard 1, Kevin 0

The problem here is the same old bugaboo that the standard pretends
daylight-savings time doesn't exist. So we are in the standards
extension business to try to find semi-reasonable semantics for the
standard datatypes when faced with DST-aware timezone definitions.
But dropping a type required by the spec isn't going to happen.

I can however see a good argument for rejecting DST-dependent input
for timetz. We aren't required by the spec to accept that, and as
Kevin says it's just not well defined.

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. But given our current limited implementation of timetz,
there's a lot to be said for rejecting DST-dependent input.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

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

Patch applied. Thanks.

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


Joachim Wieland wrote:
> 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
>

[ Attachment, skipping... ]

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

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match