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
smime.p7s
Description: S/MIME cryptographic signature
