Re: [PATCHES] TODO-Item: full timezone names
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 [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 smime.p7s Description: S/MIME cryptographic signature
Re: [PATCHES] TODO-Item: full timezone names
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
Re: [PATCHES] INSERT ... RETURNING
Here here on this one. With the deprecation of oids on the horizon insert returning is to be extremely important. It's use with the uniqueidentifier mod would be really really helpful. On a similar note, is anyone working on the ability to have a column default as the product of a function on another column of the same row. I know this can be done as a trigger but something like CREATE TABLE abc (name text not null unique, hash not null default somehashfunc(name)); would be very convenient, and of course with the ability to get the product back with insert returning. Kevin McArthur - Original Message - From: Bruce Momjian pgman@candle.pha.pa.us To: [EMAIL PROTECTED] Cc: pgsql-patches@postgresql.org Sent: Friday, July 29, 2005 7:26 PM Subject: Re: [PATCHES] INSERT ... RETURNING Are you still working on completing this? --- [EMAIL PROTECTED] wrote: Hi there, Attached is a patch (by Gavin Sherry, fixed up to apply to 8.1 by me) that implements INSERT ... RETURNING functionality. It does work for the common case of RETURNING the value of a serial/sequence column, but gets confused when returning results out-of-order (CREATE TABLE x (a int, b int), INSERT ... RETURNING b, a) and doesn't let you specify the same column multiple times (INSERT ... RETURNING b, b). These will be addressed soon. Regards, Omar Kilani [ Attachment, skipping... ] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] PLPGSQL OID Bug
This patch will resolve the oid retrieval bugs from
plpgsql. There are however several other places where isnull=false was removed
and replaced with isnull which may also need to be corrected.
Kevin McArthur
StormTide Digital Studios Inc.
Index:
src/pl/plpgsql/src/pl_exec.c===RCS
file: /projects/cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,vretrieving
revision 1.149diff -c -r1.149 pl_exec.c***
src/pl/plpgsql/src/pl_exec.c 26 Jun
2005 22:05:42 - 1.149---
src/pl/plpgsql/src/pl_exec.c 27 Jul
2005 20:38:25 -** 1143,1149
{
PLpgSQL_diag_item *diag_item =
(PLpgSQL_diag_item *)
lfirst(lc);
PLpgSQL_datum
*var;!
bool
isnull;
if (diag_item-target =
0)
continue;--- 1143,1149
{
PLpgSQL_diag_item *diag_item =
(PLpgSQL_diag_item *)
lfirst(lc);
PLpgSQL_datum
*var;!
bool
isnull=false;
if (diag_item-target =
0)
continue;
smime.p7s
Description: S/MIME cryptographic signature
