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