Thread: TODO-Item: full timezone names
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
Attachment
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
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
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
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
Attachment
"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
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
> 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 >
Attachment
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. +