From 32f4ae568c1185cc6f5b96b2ce0bf48f958415c7 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sun, 5 Jun 2005 00:18:42 +0000 Subject: [PATCH] Add TODO.detail of standard timezone data type. --- doc/TODO.detail/timezone | 2829 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 2829 insertions(+) create mode 100644 doc/TODO.detail/timezone diff --git a/doc/TODO.detail/timezone b/doc/TODO.detail/timezone new file mode 100644 index 0000000000..f4fabc16b5 --- /dev/null +++ b/doc/TODO.detail/timezone @@ -0,0 +1,2829 @@ +From pgsql-hackers-owner+M60207=pgman=candle.pha.pa.us@postgresql.org Thu Oct 21 07:25:41 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9LBPdf26430 + for ; Thu, 21 Oct 2004 07:25:40 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 0548B32A593 + for ; Thu, 21 Oct 2004 12:25:37 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 74257-06 for ; + Thu, 21 Oct 2004 11:25:33 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id B0DB532A544 + for ; Thu, 21 Oct 2004 12:25:36 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 1124A329FAB + for ; Thu, 21 Oct 2004 12:23:00 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 70900-09 + for ; + Thu, 21 Oct 2004 11:22:43 +0000 (GMT) +Received: from zigo.dhs.org (as2-4-3.an.g.bonet.se [194.236.34.191]) + by svr1.postgresql.org (Postfix) with ESMTP id 301C532A301 + for ; Thu, 21 Oct 2004 12:22:43 +0100 (BST) +Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1]) + by zigo.dhs.org (Postfix) with ESMTP id A022C8467 + for ; Thu, 21 Oct 2004 13:22:40 +0200 (CEST) +Date: Thu, 21 Oct 2004 13:22:40 +0200 (CEST) +From: Dennis Bjorklund +To: pgsql-hackers@postgresql.org +Subject: [HACKERS] timestamp with time zone a la sql99 +Message-ID: +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=ISO-8859-1 +Content-Transfer-Encoding: 8BIT +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +I've made a partial implementation of a datatype "timestamp with time +zone" as described in the sql standard. The current type "timestamptz" +does not store the time zone as a standard one should do. So I've made a +new type I've called timestampstdtz that does store the time zone as the +standard demands. + +Let me show a bit of what currently works in my implementation: + + dennis=# CREATE TABLE foo ( + a timestampstdtz, + + primary key (a) + ); + dennis=# INSERT INTO foo VALUES ('1993-02-04 13:00 UTC'); + dennis=# INSERT INTO foo VALUES ('1999-06-01 14:00 CET'); + dennis=# INSERT INTO foo VALUES ('2003-08-21 15:00 PST'); + + dennis=# SELECT a FROM foo; + a + ------------------------ + 1993-02-04 13:00:00+00 + 1999-06-01 14:00:00+01 + 2003-08-21 15:00:00-08 + + dennis=# SELECT a AT TIME ZONE 'CET' FROM foo; + timezone + ------------------------ + 1993-02-04 14:00:00+01 + 1999-06-01 14:00:00+01 + 2003-08-22 00:00:00+01 + +My plan is to make a GUC variable so that one can tell PG that constructs +like "timestamp with time zone" will map to timestampstdtz instead of +timestamptz (some old databases might need the old so unless we want to +break old code this is the easiest solution I can find). + +I've made an implicit cast from timestampstdtz to timestamptz that just +forgets about the time zone. In the other direction I've made an +assignment cast that make a timestamp with time zone 0 (that's what a +timestamptz is anyway). Would it be possible to make it implicit in both +directions? I currently don't think that you want that, but is it +possible? + +With the implicit cast in place I assume it would be safe to change +functions like now() to return a timestampstdtz? I've not tried yet but I +will. As far as I can tell the cast would make old code that use now() to +still work as before. + +Any comments before I invest more time into this subject? + +-- +/Dennis Björklund + + +---------------------------(end of broadcast)--------------------------- +TIP 2: you can get off all lists at once with the unregister command + (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) + +From pgsql-hackers-owner+M60208=pgman=candle.pha.pa.us@postgresql.org Thu Oct 21 10:34:36 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9LEYYf08049 + for ; Thu, 21 Oct 2004 10:34:35 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 34B0F32A2AB + for ; Thu, 21 Oct 2004 15:34:30 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 38287-03 for ; + Thu, 21 Oct 2004 14:34:26 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 6A38132A1B6 + for ; Thu, 21 Oct 2004 15:34:29 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 535FE32A9EE + for ; Thu, 21 Oct 2004 15:29:17 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 34535-09 + for ; + Thu, 21 Oct 2004 14:29:10 +0000 (GMT) +Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) + by svr1.postgresql.org (Postfix) with ESMTP id 41E4F32A9D6 + for ; Thu, 21 Oct 2004 15:29:12 +0100 (BST) +Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) + by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9LET86O015233; + Thu, 21 Oct 2004 10:29:10 -0400 (EDT) +To: Dennis Bjorklund +cc: pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: +References: +Comments: In-reply-to Dennis Bjorklund + message dated "Thu, 21 Oct 2004 13:22:40 +0200" +Date: Thu, 21 Oct 2004 10:29:07 -0400 +Message-ID: <15232.1098368947@sss.pgh.pa.us> +From: Tom Lane +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Dennis Bjorklund writes: +> I've made a partial implementation of a datatype "timestamp with time +> zone" as described in the sql standard. The current type "timestamptz" +> does not store the time zone as a standard one should do. + +I'm aware that there are aspects of the spec behavior that appear to +require that, but is it really an improvement over the implementation +we have? This is an area in which the standard is pretty brain-dead +--- the entire concept of a "time with time zone" datatype is rather +suspect, for instance. + +In particular, I wonder how you will handle daylight-savings issues. +The spec definition seems to preclude doing anything intelligent with +DST, as they equate a timezone with a fixed offset from UTC. That's +not how it works in (large parts of) the real world. + + regards, tom lane + +---------------------------(end of broadcast)--------------------------- +TIP 6: Have you searched our list archives? + + http://archives.postgresql.org + +From pgsql-hackers-owner+M60210=pgman=candle.pha.pa.us@postgresql.org Thu Oct 21 11:08:02 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9LF7xf13992 + for ; Thu, 21 Oct 2004 11:08:00 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id AEBEE32AB8E + for ; Thu, 21 Oct 2004 16:07:55 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 51273-03 for ; + Thu, 21 Oct 2004 15:07:55 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 506C832AB6A + for ; Thu, 21 Oct 2004 16:07:55 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 59130329F96 + for ; Thu, 21 Oct 2004 16:02:14 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 48602-06 + for ; + Thu, 21 Oct 2004 15:01:59 +0000 (GMT) +Received: from zigo.dhs.org (as2-4-3.an.g.bonet.se [194.236.34.191]) + by svr1.postgresql.org (Postfix) with ESMTP id 8ED0932A095 + for ; Thu, 21 Oct 2004 16:01:54 +0100 (BST) +Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1]) + by zigo.dhs.org (Postfix) with ESMTP + id D08B88467; Thu, 21 Oct 2004 17:01:52 +0200 (CEST) +Date: Thu, 21 Oct 2004 17:01:52 +0200 (CEST) +From: Dennis Bjorklund +To: Tom Lane +cc: pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <15232.1098368947@sss.pgh.pa.us> +Message-ID: +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=ISO-8859-1 +Content-Transfer-Encoding: 8BIT +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +On Thu, 21 Oct 2004, Tom Lane wrote: + +> > I've made a partial implementation of a datatype "timestamp with time +> > zone" as described in the sql standard. The current type "timestamptz" +> > does not store the time zone as a standard one should do. +> +> I'm aware that there are aspects of the spec behavior that appear to +> require that, but is it really an improvement over the implementation +> we have? + +Improvement and improvement. The actual time value is of course the same +(the utc part of a timestamp) and the only thing extra you get is that the +time zone is stored. The extra information you do have now, when stored in +this way, is that you store both a utc time and a local time. Will any +application ever need that? Who knows? I think it makes sense and is an +easier model to think about then what pg uses today. So I would use it +even if it means using 2 bytes more storage then what timestamptz do + +Just that it is standard also makes it useful. The more things of the +standard we support the easier it is to move between databases. This is +important to me. + +I also want to make a general statement that I think that whenever we use +standard syntax we should give it a standard semantics. I don't mind +extensions at all, but as much as we can we should make sure that they +don't clash with standard syntax and semantics. + +> This is an area in which the standard is pretty brain-dead +> --- the entire concept of a "time with time zone" datatype is rather +> suspect, for instance. + +I havn't look that much at "time with time zone" yet, just timestamps. + +I can't see why time with time zone should not also be supported. I can't +really imagine it being used without a date, but if someone wants to store +timestamps as a date+time with time zone, then why not. It would be extra +work tu is it instead of a timestamp (especially for cases where the time +wraps over to the prev/next day), but hey. + +> In particular, I wonder how you will handle daylight-savings issues. +> The spec definition seems to preclude doing anything intelligent with +> DST, as they equate a timezone with a fixed offset from UTC. That's +> not how it works in (large parts of) the real world. + +The tz in the standard is a offset from utc, yes. So when you store a +value you tell it what offset you use. If you are using daylight-savings +time it might be +02 and if not dst it might be +01. What else would you +want to do with it? It's not like you can do anything else with it in pg +as of today, can you? + +The stored tz does not say what region of the globe you are in, it says +the distance away from utc in minutes that you are. I could imagine +another datatype that stores the time zone as name, but that's not what +timestamp with time zone does. + +-- +/Dennis Björklund + + +---------------------------(end of broadcast)--------------------------- +TIP 8: explain analyze is your friend + +From pgsql-hackers-owner+M60232=pgman=candle.pha.pa.us@postgresql.org Fri Oct 22 08:43:06 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9MCh4f16646 + for ; Fri, 22 Oct 2004 08:43:04 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id D389532A4BA + for ; Fri, 22 Oct 2004 13:42:58 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 01914-09 for ; + Fri, 22 Oct 2004 12:42:51 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 82BD832A4B3 + for ; Fri, 22 Oct 2004 13:42:58 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 05C2132A3F5 + for ; Fri, 22 Oct 2004 13:39:37 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 03281-02 + for ; + Fri, 22 Oct 2004 12:39:20 +0000 (GMT) +Received: from lakermmtao07.cox.net (lakermmtao07.cox.net [68.230.240.32]) + by svr1.postgresql.org (Postfix) with ESMTP id 1971732A32B + for ; Fri, 22 Oct 2004 13:39:26 +0100 (BST) +Received: from [192.168.0.9] (really [24.250.237.182]) + by lakermmtao07.cox.net + (InterMail vM.6.01.03.04 201-2131-111-106-20040729) with ESMTP + id <20041022123912.IJSP14063.lakermmtao07.cox.net@[192.168.0.9]>; + Fri, 22 Oct 2004 08:39:12 -0400 +From: Robert Treat +To: Dennis Bjorklund +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +Date: Fri, 22 Oct 2004 08:37:33 -0400 +User-Agent: KMail/1.6.2 +cc: Tom Lane , pgsql-hackers@postgresql.org +References: +In-Reply-To: +MIME-Version: 1.0 +Content-Disposition: inline +Content-Type: text/plain; + charset="iso-8859-1" +Content-Transfer-Encoding: 7bit +Message-ID: <200410220837.33886.xzilla@users.sourceforge.net> +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +On Thursday 21 October 2004 11:01, Dennis Bjorklund wrote: +> On Thu, 21 Oct 2004, Tom Lane wrote: +> > I'm aware that there are aspects of the spec behavior that appear to +> > require that, but is it really an improvement over the implementation +> > we have? +> +> Improvement and improvement. The actual time value is of course the same +> (the utc part of a timestamp) and the only thing extra you get is that the +> time zone is stored. The extra information you do have now, when stored in +> this way, is that you store both a utc time and a local time. Will any +> application ever need that? Who knows? I think it makes sense and is an +> easier model to think about then what pg uses today. So I would use it +> even if it means using 2 bytes more storage then what timestamptz do +> + +In a fit of early morning, pre-coffee thoughts, I'm thinking this might be +just what I've been looking for. In one of my apps we take calls from around +the country for customers and store the time that call came in. Unfortunately +we need to know things like how many calls did we take in an hour across +customers, but also how many calls did we take at 6AM local time to the +customer. The way PostgreSQL works now, you have to store some extra bits +of info in another column and then reassemble it to be able to determine +those two queries, but it sounds like your timestampstdtz would allow that +information to be stored together, as it should be. + +-- +Robert Treat +Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL + +---------------------------(end of broadcast)--------------------------- +TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org + +From pgsql-hackers-owner+M60235=pgman=candle.pha.pa.us@postgresql.org Fri Oct 22 10:18:44 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9MEIhf03000 + for ; Fri, 22 Oct 2004 10:18:44 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 63ACB329FE3 + for ; Fri, 22 Oct 2004 15:18:38 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 35128-09 for ; + Fri, 22 Oct 2004 14:18:30 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 3439A329E73 + for ; Fri, 22 Oct 2004 15:18:38 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id C332832A5AA + for ; Fri, 22 Oct 2004 15:13:18 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 32986-06 + for ; + Fri, 22 Oct 2004 14:13:09 +0000 (GMT) +Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) + by svr1.postgresql.org (Postfix) with ESMTP id E7E6F32A576 + for ; Fri, 22 Oct 2004 15:13:16 +0100 (BST) +Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) + by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9MEDIA4006541; + Fri, 22 Oct 2004 10:13:18 -0400 (EDT) +To: Robert Treat +cc: Dennis Bjorklund , pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <200410220837.33886.xzilla@users.sourceforge.net> +References: <200410220837.33886.xzilla@users.sourceforge.net> +Comments: In-reply-to Robert Treat + message dated "Fri, 22 Oct 2004 08:37:33 -0400" +Date: Fri, 22 Oct 2004 10:13:18 -0400 +Message-ID: <6540.1098454398@sss.pgh.pa.us> +From: Tom Lane +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Robert Treat writes: +> In a fit of early morning, pre-coffee thoughts, I'm thinking this might be +> just what I've been looking for. In one of my apps we take calls from around +> the country for customers and store the time that call came in. Unfortunately +> we need to know things like how many calls did we take in an hour across +> customers, but also how many calls did we take at 6AM local time to the +> customer. The way PostgreSQL works now, you have to store some extra bits +> of info in another column and then reassemble it to be able to determine +> those two queries, but it sounds like your timestampstdtz would allow that +> information to be stored together, as it should be. + +As far as I can tell, Dennis is planning slavish adherence to the spec, +which will mean that the datatype is unable to cope effectively with +daylight-savings issues. So I'm unconvinced that it will be very +helpful to you for remembering local time in addition to true +(universal) time. + + regards, tom lane + +---------------------------(end of broadcast)--------------------------- +TIP 3: 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 + +From pgsql-hackers-owner+M60237=pgman=candle.pha.pa.us@postgresql.org Fri Oct 22 10:33:04 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9MEX3f05134 + for ; Fri, 22 Oct 2004 10:33:03 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id AB3B432A907 + for ; Fri, 22 Oct 2004 15:32:57 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 42366-04 for ; + Fri, 22 Oct 2004 14:32:49 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 28B5E32A6BE + for ; Fri, 22 Oct 2004 15:32:56 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 5366A32A923 + for ; Fri, 22 Oct 2004 15:28:12 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 41328-02 + for ; + Fri, 22 Oct 2004 14:28:03 +0000 (GMT) +Received: from zigo.dhs.org (as2-4-3.an.g.bonet.se [194.236.34.191]) + by svr1.postgresql.org (Postfix) with ESMTP id 0277A32A916 + for ; Fri, 22 Oct 2004 15:28:10 +0100 (BST) +Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1]) + by zigo.dhs.org (Postfix) with ESMTP + id 336C88467; Fri, 22 Oct 2004 16:28:12 +0200 (CEST) +Date: Fri, 22 Oct 2004 16:28:12 +0200 (CEST) +From: Dennis Bjorklund +To: Tom Lane +cc: Robert Treat , + +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <6540.1098454398@sss.pgh.pa.us> +Message-ID: +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=ISO-8859-1 +Content-Transfer-Encoding: 8BIT +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +On Fri, 22 Oct 2004, Tom Lane wrote: + +> As far as I can tell, Dennis is planning slavish adherence to the spec, +> which will mean that the datatype is unable to cope effectively with +> daylight-savings issues. So I'm unconvinced that it will be very +> helpful to you for remembering local time in addition to true +> (universal) time. + +And exactly what issues is it that you see? The only thing I can think of +is if you have a timestamp and then add an interval to it so we jump past +the daylight saving time change date. Then the new timestamp will keep the +old timezone data of say +01 even though we now have jumped into the +daylight saving period of +02. + +If you are just storing actual timestamps then the standard definition +works just fine. If I store '2004-10-22 16:20:04 +02' then that's exactly +what I get back. No problem what so ever. There is no DST problem with +that. + +It's possible that I will introduce some daylight saving bit or something +like that, I'm not sure yet and I will not commit to anything until I've +thought it over. I don't think there are that much of a problem as you +claim however. Could you give a concret example where it will be a +problem? + +My current thinking is that storing the time zone value as HH:MM is +just fine and you avoid all the problems with political changes of when +the DST is in effect or not. + +-- +/Dennis Björklund + + +---------------------------(end of broadcast)--------------------------- +TIP 3: 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 + +From pgsql-hackers-owner+M60240=pgman=candle.pha.pa.us@postgresql.org Fri Oct 22 10:58:26 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9MEwOf10545 + for ; Fri, 22 Oct 2004 10:58:24 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 8F3BC32A0AC + for ; Fri, 22 Oct 2004 15:58:18 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 53613-02 for ; + Fri, 22 Oct 2004 14:58:10 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 32CD532A0A2 + for ; Fri, 22 Oct 2004 15:58:18 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 65D9932A0AC + for ; Fri, 22 Oct 2004 15:54:30 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 49578-08 + for ; + Fri, 22 Oct 2004 14:54:12 +0000 (GMT) +Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) + by svr1.postgresql.org (Postfix) with ESMTP id A7D2A329FB7 + for ; Fri, 22 Oct 2004 15:54:17 +0100 (BST) +Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) + by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9MEsJsB006995; + Fri, 22 Oct 2004 10:54:19 -0400 (EDT) +To: Dennis Bjorklund +cc: Robert Treat , pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: +References: +Comments: In-reply-to Dennis Bjorklund + message dated "Fri, 22 Oct 2004 16:28:12 +0200" +Date: Fri, 22 Oct 2004 10:54:19 -0400 +Message-ID: <6994.1098456859@sss.pgh.pa.us> +From: Tom Lane +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Dennis Bjorklund writes: +> And exactly what issues is it that you see? The only thing I can think of +> is if you have a timestamp and then add an interval to it so we jump past +> the daylight saving time change date. Then the new timestamp will keep the +> old timezone data of say +01 even though we now have jumped into the +> daylight saving period of +02. + +Isn't that sufficient? You can't design a datatype by thinking only of +the data values it stores; you have to think about the operations you +intend to provide as well. A non-DST-capable timestamp datatype is +inherently a few bricks shy of a load. (BTW we really need to fix +the interval type as well...) + +At bottom, what I want to be able to do is say + '2004-10-22 10:50:16.916003 America/New_York' +and have the datatype preserve *all* of the information in that. You +are complaining because the existing type only remembers the equivalent +universal time and not the timezone spec. Why should I be satisfied if +it stores only the GMT offset and not the knowledge of which timezone +this really is? + +> My current thinking is that storing the time zone value as HH:MM is +> just fine and you avoid all the problems with political changes of when +> the DST is in effect or not. + +This is fundamentally misguided. Time zones *are* political whether you +like it or not, and people *do* expect DST-awareness whether you like it +or not. If you still use any computer systems that need to be reset +twice a year because their designers thought DST was not their problem, +don't you roundly curse them every time you have to do it? + +If you were planning to store a real (potentially DST-aware) timezone +spec in the data values, I'd be happy. But storing a fixed GMT offset +is going to be a step backwards compared to existing functionality. The +fact that it's sufficient to satisfy the DST-ignorant SQL spec does not +make it a reasonable design for the real world. + +One way to do this would be to create a system catalog with entries for +all known timezones, and then represent timestamptz values as universal +time plus an OID from that catalog. There are other ways that small +integer codes could be mapped to timezones of course. + + regards, tom lane + +---------------------------(end of broadcast)--------------------------- +TIP 2: you can get off all lists at once with the unregister command + (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) + +From pgsql-hackers-owner+M60239=pgman=candle.pha.pa.us@postgresql.org Fri Oct 22 10:49:13 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9MEnCf08566 + for ; Fri, 22 Oct 2004 10:49:12 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 5B4D432A0A2 + for ; Fri, 22 Oct 2004 15:49:07 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 50773-01 for ; + Fri, 22 Oct 2004 14:48:59 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 230EA329F96 + for ; Fri, 22 Oct 2004 15:49:07 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 7CCE332A20E + for ; Fri, 22 Oct 2004 15:46:10 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 45902-10 + for ; + Fri, 22 Oct 2004 14:45:52 +0000 (GMT) +Received: from wolff.to (wolff.to [66.93.249.74]) + by svr1.postgresql.org (Postfix) with SMTP id 30F0F32A1FA + for ; Fri, 22 Oct 2004 15:45:58 +0100 (BST) +Received: (qmail 17526 invoked by uid 500); 22 Oct 2004 14:56:44 -0000 +Date: Fri, 22 Oct 2004 09:56:44 -0500 +From: Bruno Wolff III +To: Dennis Bjorklund +cc: Tom Lane , Robert Treat , + pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +Message-ID: <20041022145644.GA17238@wolff.to> +Mail-Followup-To: Dennis Bjorklund , + Tom Lane , + Robert Treat , + pgsql-hackers@postgresql.org +References: <6540.1098454398@sss.pgh.pa.us> +MIME-Version: 1.0 +Content-Type: text/plain; charset=us-ascii +Content-Disposition: inline +In-Reply-To: +User-Agent: Mutt/1.5.6i +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +On Fri, Oct 22, 2004 at 16:28:12 +0200, + Dennis Bjorklund wrote: +> On Fri, 22 Oct 2004, Tom Lane wrote: +> +> > As far as I can tell, Dennis is planning slavish adherence to the spec, +> > which will mean that the datatype is unable to cope effectively with +> > daylight-savings issues. So I'm unconvinced that it will be very +> > helpful to you for remembering local time in addition to true +> > (universal) time. +> +> And exactly what issues is it that you see? The only thing I can think of +> is if you have a timestamp and then add an interval to it so we jump past +> the daylight saving time change date. Then the new timestamp will keep the +> old timezone data of say +01 even though we now have jumped into the +> daylight saving period of +02. + +I think for just storing values you are fine. When it comes to adding or +subtracting intervals you might get some unexpected results. + +---------------------------(end of broadcast)--------------------------- +TIP 3: 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 + +From pgsql-hackers-owner+M60253=pgman=candle.pha.pa.us@postgresql.org Fri Oct 22 14:44:05 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9MIi3f14607 + for ; Fri, 22 Oct 2004 14:44:03 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id B336CEAEDAF + for ; Fri, 22 Oct 2004 19:43:39 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 21148-08 for ; + Fri, 22 Oct 2004 18:43:46 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 4FC1AEAEDAB + for ; Fri, 22 Oct 2004 19:43:39 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 178B1EAEF44 + for ; Fri, 22 Oct 2004 19:38:04 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 20287-01 + for ; + Fri, 22 Oct 2004 18:38:00 +0000 (GMT) +Received: from www.postgresql.com (www.postgresql.com [200.46.204.209]) + by svr1.postgresql.org (Postfix) with ESMTP id D1F79EAEE4F + for ; Fri, 22 Oct 2004 19:37:45 +0100 (BST) +Received: from zigo.dhs.org (as2-4-3.an.g.bonet.se [194.236.34.191]) + by www.postgresql.com (Postfix) with ESMTP id 358575A1CED + for ; Fri, 22 Oct 2004 16:34:33 +0100 (BST) +Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1]) + by zigo.dhs.org (Postfix) with ESMTP + id 89B7E8467; Fri, 22 Oct 2004 17:34:19 +0200 (CEST) +Date: Fri, 22 Oct 2004 17:34:19 +0200 (CEST) +From: Dennis Bjorklund +To: Tom Lane +cc: Robert Treat , + +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <6994.1098456859@sss.pgh.pa.us> +Message-ID: +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=ISO-8859-1 +Content-Transfer-Encoding: 8BIT +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +On Fri, 22 Oct 2004, Tom Lane wrote: + +> At bottom, what I want to be able to do is say +> '2004-10-22 10:50:16.916003 America/New_York' + +Yes, that's what we said in the last mail and I think there is a value in +having something like this. + +> universal time and not the timezone spec. Why should I be satisfied if +> it stores only the GMT offset and not the knowledge of which timezone +> this really is? + +You don't need to be satisfied with it. I think a type like the above +would be fine to have. It should however not be called "TIMESTAMP WITH +TIME ZONE" because there is already a definition of that type. We can not +hijack standard types. I would not mind a type like TIMESTAMP WITH TIME +ZONE NAME (or some other name). I could even imagine that I could +implement something like that one day. + +> > My current thinking is that storing the time zone value as HH:MM is +> > just fine and you avoid all the problems with political changes of when +> > the DST is in effect or not. +> +> This is fundamentally misguided. Time zones *are* political whether you +> like it or not, and people *do* expect DST-awareness whether you like it +> or not. + +And I never said that time zones are not political, just that HH:MM is a +usable approximation that works fairly well. + +> But storing a fixed GMT offset is going to be a step backwards compared +> to existing functionality. + +It's not a step backwards since you can do everything you can do with the +current type plus a little bit more. It's however not a step to the +datatype discussed above. + +> One way to do this would be to create a system catalog with entries for +> all known timezones, and then represent timestamptz values as universal +> time plus an OID from that catalog. There are other ways that small +> integer codes could be mapped to timezones of course. + +This is just fine. You try to make it sound like I am against such a +datatype, I am not. It's however not the datatype that we can expect +applications and other databases to use. So why should we settle for only +that type. Just because you can make a perfect datatype it doesn't mean +that the standard datatype should just be ignored. + +What would you store when the user supplies a timestamp like '2004-10-22 +17:21:00 +0200'. Should you reject that because you don't know the +time zone name? So your datatype will not work for applications that try +to be compatable with many databases by using the standard? + +Maybe one could make a datatype called TIMESTAMP WITH TIME ZONE that can +accept both HH:MM and TimeZoneName. Whenever you store values with HH:MM +time zones you will get the same problem when you add an interval as the +standard type has. + +-- +/Dennis Björklund + + +---------------------------(end of broadcast)--------------------------- +TIP 8: explain analyze is your friend + +From pgsql-hackers-owner+M60266=pgman=candle.pha.pa.us@postgresql.org Fri Oct 22 17:04:46 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9ML4jf06676 + for ; Fri, 22 Oct 2004 17:04:45 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id C13F9EAE46B + for ; Fri, 22 Oct 2004 22:04:20 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 66740-03 for ; + Fri, 22 Oct 2004 21:04:28 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 75764EAE1A2 + for ; Fri, 22 Oct 2004 22:04:20 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 2E4AFEAE486 + for ; Fri, 22 Oct 2004 22:01:22 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 63290-09 + for ; + Fri, 22 Oct 2004 21:01:22 +0000 (GMT) +Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) + by svr1.postgresql.org (Postfix) with ESMTP id 88B3FEAE492 + for ; Fri, 22 Oct 2004 22:01:13 +0100 (BST) +Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) + by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9ML1WrB018877; + Fri, 22 Oct 2004 17:01:34 -0400 (EDT) +To: Dennis Bjorklund +cc: Robert Treat , pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: +References: +Comments: In-reply-to Dennis Bjorklund + message dated "Fri, 22 Oct 2004 17:34:19 +0200" +Date: Fri, 22 Oct 2004 17:01:32 -0400 +Message-ID: <18876.1098478892@sss.pgh.pa.us> +From: Tom Lane +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Dennis Bjorklund writes: +> You don't need to be satisfied with it. I think a type like the above +> would be fine to have. It should however not be called "TIMESTAMP WITH +> TIME ZONE" because there is already a definition of that type. We can not +> hijack standard types. + +Sure we can, as long as they are upward compatible with the standard +behavior. The spec says you can put a numeric-GMT-offset zone in and +get a numeric-GMT-offset zone out. We can do that and also support +named, possibly DST-aware zones. This seems a whole lot better to me +than having two different types (the idea of a GUC variable to choose +which one is selected by a given type name is just horrid). + +>> But storing a fixed GMT offset is going to be a step backwards compared +>> to existing functionality. + +> It's not a step backwards since you can do everything you can do with the +> current type plus a little bit more. + +... except get useful answers from interval addition ... + +> What would you store when the user supplies a timestamp like '2004-10-22 +> 17:21:00 +0200'. Should you reject that because you don't know the +> time zone name? + +You are attacking a straw man. + +We have put a great deal of work into 8.0 to add the ability to support +real-world zones fully. We did not import src/timezone because we +needed it to implement the SQL spec; we did so because we needed it to +implement what real users want. We are not fully there yet (can't do AT +TIME ZONE conversions with all zones yet, for instance) but I am hoping +to be there by 8.1. It would be folly to invent a timestamp with time +zone type that is going in the other direction while we are trying to +bring the rest of the system up to full speed by allowing all timezone +kinds everywhere. + + regards, tom lane + +---------------------------(end of broadcast)--------------------------- +TIP 3: 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 + +From pgsql-hackers-owner+M60267=pgman=candle.pha.pa.us@postgresql.org Fri Oct 22 17:22:15 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9MLMEf09207 + for ; Fri, 22 Oct 2004 17:22:14 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 0B2D7EAE4AC + for ; Fri, 22 Oct 2004 22:21:49 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 70387-04 for ; + Fri, 22 Oct 2004 21:21:56 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 0930BEAE4AA + for ; Fri, 22 Oct 2004 22:21:48 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 292E8EAC955 + for ; Fri, 22 Oct 2004 22:18:05 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 68984-06 + for ; + Fri, 22 Oct 2004 21:18:04 +0000 (GMT) +Received: from zigo.dhs.org (as2-4-3.an.g.bonet.se [194.236.34.191]) + by svr1.postgresql.org (Postfix) with ESMTP id A3C4DEAE489 + for ; Fri, 22 Oct 2004 22:17:51 +0100 (BST) +Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1]) + by zigo.dhs.org (Postfix) with ESMTP + id 065508469; Fri, 22 Oct 2004 23:18:08 +0200 (CEST) +Date: Fri, 22 Oct 2004 23:18:07 +0200 (CEST) +From: Dennis Bjorklund +To: Tom Lane +cc: Robert Treat , + +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <18876.1098478892@sss.pgh.pa.us> +Message-ID: +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=ISO-8859-1 +Content-Transfer-Encoding: 8BIT +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +On Fri, 22 Oct 2004, Tom Lane wrote: + +> than having two different types (the idea of a GUC variable to choose +> which one is selected by a given type name is just horrid). + +That is needed no matter what change you do if you want old programs that +use the current timestamp with time zone to work. Today you don't get back +the same time zone as you insert, programs might depend on that. + +> We are not fully there yet (can't do AT TIME ZONE conversions with all +> zones yet, for instance) + +Why is that? When one start with a utc value, performing a AT TIME ZONE +operation doesn't look so complicated. + +-- +/Dennis Björklund + + +---------------------------(end of broadcast)--------------------------- +TIP 5: Have you checked our extensive FAQ? + + http://www.postgresql.org/docs/faqs/FAQ.html + +From pgsql-hackers-owner+M60268=pgman=candle.pha.pa.us@postgresql.org Fri Oct 22 17:41:36 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9MLfYf12294 + for ; Fri, 22 Oct 2004 17:41:35 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id EFAEAEAE4AA + for ; Fri, 22 Oct 2004 22:41:08 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 75185-08 for ; + Fri, 22 Oct 2004 21:41:16 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 311A7EAE4AF + for ; Fri, 22 Oct 2004 22:41:08 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id AC0CFEAE486 + for ; Fri, 22 Oct 2004 22:38:14 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 76579-03 + for ; + Fri, 22 Oct 2004 21:38:13 +0000 (GMT) +Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) + by svr1.postgresql.org (Postfix) with ESMTP id BD0AAEAD7E2 + for ; Fri, 22 Oct 2004 22:38:03 +0100 (BST) +Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) + by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9MLcOnT019118; + Fri, 22 Oct 2004 17:38:25 -0400 (EDT) +To: Dennis Bjorklund +cc: Robert Treat , pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: +References: +Comments: In-reply-to Dennis Bjorklund + message dated "Fri, 22 Oct 2004 23:18:07 +0200" +Date: Fri, 22 Oct 2004 17:38:24 -0400 +Message-ID: <19117.1098481104@sss.pgh.pa.us> +From: Tom Lane +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Dennis Bjorklund writes: +> On Fri, 22 Oct 2004, Tom Lane wrote: +>> than having two different types (the idea of a GUC variable to choose +>> which one is selected by a given type name is just horrid). + +> That is needed no matter what change you do if you want old programs that +> use the current timestamp with time zone to work. Today you don't get back +> the same time zone as you insert, programs might depend on that. + +[ shrug... ] We've made much larger changes than that in the name of +standards compliance. In practice I think the majority of apps are +working in contexts where they will get back the same zone as they +inserted, if they inserted a zone explicitly at all, so the risk of +breakage is not that high. Having a GUC variable that changes the +semantics underneath you is *much* riskier, to judge by past experience. + +>> We are not fully there yet (can't do AT TIME ZONE conversions with all +>> zones yet, for instance) + +> Why is that? + +Because it's not done yet. There's a set of GMT-offset-only zone names +wired into the datetime code (look in the "datetime token table") and +those are what AT TIME ZONE knows how to deal with. We need to unify +that old stuff with the src/timezone code, but we ran out of time to do +it in 8.0. + +The way I see it, we have three sorts of zones to deal with: fixed +numeric offsets from UTC, names that represent fixed offsets (eg, "EST" +is the same as UTC-5), and names that represent DST-variable offsets +(eg, "EST5EDT"). For what are now entirely historical reasons, various +parts of the system cope with different subsets of these three types. +I want to get to a state where you can use any of them in any context +and it Just Works. (While we are at it, we need to make the set of +recognized zone names user-configurable; the australian_timezones kluge +satisfies our contributors Down Under, but there are a lot of unhappy +people still, because for instance IST means different things in Israel +and India.) + + regards, tom lane + +---------------------------(end of broadcast)--------------------------- +TIP 2: you can get off all lists at once with the unregister command + (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) + +From pgsql-hackers-owner+M60269=pgman=candle.pha.pa.us@postgresql.org Fri Oct 22 17:51:14 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9MLpCf14192 + for ; Fri, 22 Oct 2004 17:51:13 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 43D6BEAE489 + for ; Fri, 22 Oct 2004 22:50:47 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 78632-05 for ; + Fri, 22 Oct 2004 21:50:55 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id EFC45EAE486 + for ; Fri, 22 Oct 2004 22:50:46 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 42518EAC955 + for ; Fri, 22 Oct 2004 22:48:36 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 77399-07 + for ; + Fri, 22 Oct 2004 21:48:35 +0000 (GMT) +Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) + by svr1.postgresql.org (Postfix) with ESMTP id 8BB75EAE46B + for ; Fri, 22 Oct 2004 22:48:27 +0100 (BST) +Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) + by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9MLmm1l019192; + Fri, 22 Oct 2004 17:48:48 -0400 (EDT) +To: Dennis Bjorklund +cc: Robert Treat , pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <19117.1098481104@sss.pgh.pa.us> +References: <19117.1098481104@sss.pgh.pa.us> +Comments: In-reply-to Tom Lane + message dated "Fri, 22 Oct 2004 17:38:24 -0400" +Date: Fri, 22 Oct 2004 17:48:48 -0400 +Message-ID: <19191.1098481728@sss.pgh.pa.us> +From: Tom Lane +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +>> That is needed no matter what change you do if you want old programs that +>> use the current timestamp with time zone to work. Today you don't get back +>> the same time zone as you insert, programs might depend on that. + +> [ shrug... ] We've made much larger changes than that in the name of +> standards compliance. + +BTW, even if you do want output like that, that doesn't make two +datatypes a good idea. It'd be better to add a couple of DateStyle-like +formatting options: + * rotate all timestamps into current TimeZone for display, or not; + * display the timezone numerically, or as originally given. + +A DateStyle kind of GUC variable is a lot less dangerous than what you +were proposing, because getting it wrong doesn't mean you have the wrong +data stored in the database ... + + regards, tom lane + +---------------------------(end of broadcast)--------------------------- +TIP 4: Don't 'kill -9' the postmaster + +From pgsql-hackers-owner+M60274=pgman=candle.pha.pa.us@postgresql.org Sat Oct 23 02:11:58 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9N6Buf20583 + for ; Sat, 23 Oct 2004 02:11:57 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id B3D75EAE4AC + for ; Sat, 23 Oct 2004 07:11:22 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 75750-09 for ; + Sat, 23 Oct 2004 06:11:32 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 560D3EADBC0 + for ; Sat, 23 Oct 2004 07:11:22 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 9D862EAE4CB + for ; Sat, 23 Oct 2004 07:08:48 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 77538-02 + for ; + Sat, 23 Oct 2004 06:08:54 +0000 (GMT) +Received: from zigo.dhs.org (as2-4-3.an.g.bonet.se [194.236.34.191]) + by svr1.postgresql.org (Postfix) with ESMTP id 29BC1EAC8F4 + for ; Sat, 23 Oct 2004 07:08:40 +0100 (BST) +Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1]) + by zigo.dhs.org (Postfix) with ESMTP + id 846788467; Sat, 23 Oct 2004 08:09:05 +0200 (CEST) +Date: Sat, 23 Oct 2004 08:09:05 +0200 (CEST) +From: Dennis Bjorklund +To: Tom Lane +cc: Robert Treat , + +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <18876.1098478892@sss.pgh.pa.us> +Message-ID: +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=ISO-8859-1 +Content-Transfer-Encoding: 8BIT +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +On Fri, 22 Oct 2004, Tom Lane wrote: + +> behavior. The spec says you can put a numeric-GMT-offset zone in and +> get a numeric-GMT-offset zone out. We can do that and also support +> named, possibly DST-aware zones. + +So if I understand you correctly you are planning to extend the current +timestamp type to work with both named time zones and HH:MM ones? I didn't +think you wanted the last one since your plan was to store a UTC+OID where +the OID pointed to a named time zone. And I guess that you don't plan to +add 00:00, 00:01, 00:02, ... as named zones with an OID. + +-- +/Dennis Björklund + + +---------------------------(end of broadcast)--------------------------- +TIP 9: the planner will ignore your desire to choose an index scan if your + joining column's datatypes do not match + +From pgsql-hackers-owner+M60329=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 12:45:39 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PGjcf25576 + for ; Mon, 25 Oct 2004 12:45:38 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 27F433A4146 + for ; Mon, 25 Oct 2004 17:45:35 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 63030-04 for ; + Mon, 25 Oct 2004 16:45:34 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id D15153A413C + for ; Mon, 25 Oct 2004 17:45:34 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 4D4653A415D + for ; Mon, 25 Oct 2004 17:43:32 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 60516-10 + for ; + Mon, 25 Oct 2004 16:43:26 +0000 (GMT) +Received: from davinci.ethosmedia.com (server226.ethosmedia.com [209.128.84.226]) + by svr1.postgresql.org (Postfix) with ESMTP id CAB1E3A4170 + for ; Mon, 25 Oct 2004 17:43:26 +0100 (BST) +Received: from [63.195.55.98] (account josh@agliodbs.com HELO spooky) + by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) + with ESMTP id 6553366 for pgsql-hackers@postgresql.org; Mon, 25 Oct 2004 09:44:52 -0700 +From: Josh Berkus +Organization: Aglio Database Solutions +To: pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +Date: Mon, 25 Oct 2004 09:42:38 -0700 +User-Agent: KMail/1.6.2 +References: <20041022184636.62D39EAEE02@svr1.postgresql.org> +In-Reply-To: <20041022184636.62D39EAEE02@svr1.postgresql.org> +MIME-Version: 1.0 +Content-Disposition: inline +Content-Type: text/plain; + charset="utf-8" +Message-ID: <200410250942.38212.josh@agliodbs.com> +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Content-Transfer-Encoding: 8bit +X-MIME-Autoconverted: from quoted-printable to 8bit by candle.pha.pa.us id i9PGjcf25576 +X-Spam-Checker-Version: SpamAssassin 2.61 (1.212.2.1-2003-12-09-exp) on + candle.pha.pa.us +X-Spam-Status: No, hits=-4.9 required=5.0 tests=BAYES_00 autolearn=ham + version=2.61 +Status: OR + +Tom, + +> As far as I can tell, Dennis is planning slavish adherence to the spec, +> which will mean that the datatype is unable to cope effectively with +> daylight-savings issues.  So I'm unconvinced that it will be very +> helpful to you for remembering local time in addition to true +> (universal) time. + +As somebody who codes calendar apps, I have to say that I have yet to see an +implementation of time zones which is at all useful for this purpose, +including the current implementation. My calendar apps on PostgreSQL 7.4 +use "timestamp without time zone" and keep the time zone in a seperate field. + +The reason is simple: our current implementation, which does include DST, +does not include any provision for the exceptions to DST -- such as Arizona +-- or for the difference between "1 day" and "24 hours". (Try adding "30 +days" to "2004-10-05 10:00 PDT", you'll see what I mean). Nor do I see a +way out of this without raising the complexity, and configurability, level of +timezones significantly. + +So if we're going to be broken (at least from the perspective of calendar +applications) we might as well be broken in a spec-compliant way. + +-- +Josh Berkus +Aglio Database Solutions +San Francisco + +---------------------------(end of broadcast)--------------------------- +TIP 6: Have you searched our list archives? + + http://archives.postgresql.org + +From pgsql-hackers-owner+M60334=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 13:56:27 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PHuQf06230 + for ; Mon, 25 Oct 2004 13:56:26 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 024493A3C12 + for ; Mon, 25 Oct 2004 18:56:23 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 90234-01 for ; + Mon, 25 Oct 2004 17:56:21 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id B12643A3BDD + for ; Mon, 25 Oct 2004 18:56:22 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 038CC3A412E + for ; Mon, 25 Oct 2004 18:54:57 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 89206-06 + for ; + Mon, 25 Oct 2004 17:54:54 +0000 (GMT) +Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) + by svr1.postgresql.org (Postfix) with ESMTP id 0D5D13A3B06 + for ; Mon, 25 Oct 2004 18:54:54 +0100 (BST) +Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) + by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9PHsrvv021835; + Mon, 25 Oct 2004 13:54:53 -0400 (EDT) +To: Josh Berkus +cc: pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <200410250942.38212.josh@agliodbs.com> +References: <20041022184636.62D39EAEE02@svr1.postgresql.org> <200410250942.38212.josh@agliodbs.com> +Comments: In-reply-to Josh Berkus + message dated "Mon, 25 Oct 2004 09:42:38 -0700" +Date: Mon, 25 Oct 2004 13:54:53 -0400 +Message-ID: <21834.1098726893@sss.pgh.pa.us> +From: Tom Lane +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Josh Berkus writes: +> The reason is simple: our current implementation, which does include DST, +> does not include any provision for the exceptions to DST -- such as Arizona + +Say what? + +regression=# set timezone to 'MST7MDT'; +SET +regression=# select now(); + now +------------------------------- + 2004-10-25 11:52:47.093538-06 +(1 row) + +regression=# set timezone to 'US/Arizona'; +SET +regression=# select now(); + now +------------------------------- + 2004-10-25 10:52:49.441559-07 +(1 row) + +> -- or for the difference between "1 day" and "24 hours". (Try adding "30 +> days" to "2004-10-05 10:00 PDT", you'll see what I mean). + +This is the point about how interval needs to treat "day" as different +from "24 hours". I agree with that; the fact that it's not done already +is just a reflection of limited supply of round tuits. I think it's +orthogonal to the question of how flexible timestamp with time zone +needs to be, though. + +> Nor do I see a way out of this without raising the complexity, and +> configurability, level of timezones significantly. + +This does not seem to me to be an argument why timestamp with time zone +ought to be incapable of dealing with DST-aware time zones. That simply +guarantees that calendar apps won't be able to use the datatype. If +they still can't use it when it can do that, then we can look at the +next blocking factor. + +> So if we're going to be broken (at least from the perspective of calendar +> applications) we might as well be broken in a spec-compliant way. + +I have not said that we can't comply with the spec. I have said that +our ambitions need to be higher than merely complying with the spec. + + regards, tom lane + +---------------------------(end of broadcast)--------------------------- +TIP 3: 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 + +From pgsql-hackers-owner+M60335=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 14:08:52 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PI8of08458 + for ; Mon, 25 Oct 2004 14:08:51 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id EB0003A4181 + for ; Mon, 25 Oct 2004 19:08:47 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 94404-01 for ; + Mon, 25 Oct 2004 18:08:46 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 984283A417A + for ; Mon, 25 Oct 2004 19:08:47 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 058F13A3B6E + for ; Mon, 25 Oct 2004 19:06:53 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 91573-10 + for ; + Mon, 25 Oct 2004 18:06:42 +0000 (GMT) +Received: from davinci.ethosmedia.com (server226.ethosmedia.com [209.128.84.226]) + by svr1.postgresql.org (Postfix) with ESMTP id B6FB73A3AC2 + for ; Mon, 25 Oct 2004 19:06:43 +0100 (BST) +Received: from [64.81.245.111] (account josh@agliodbs.com HELO temoku.sf.agliodbs.com) + by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) + with ESMTP id 6553760; Mon, 25 Oct 2004 11:08:08 -0700 +From: Josh Berkus +Reply-To: josh@agliodbs.com +Organization: Aglio Database Solutions +To: pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +Date: Mon, 25 Oct 2004 11:08:52 -0700 +User-Agent: KMail/1.6.2 +cc: Tom Lane +References: <20041022184636.62D39EAEE02@svr1.postgresql.org> <200410250942.38212.josh@agliodbs.com> <21834.1098726893@sss.pgh.pa.us> +In-Reply-To: <21834.1098726893@sss.pgh.pa.us> +MIME-Version: 1.0 +Content-Disposition: inline +Content-Type: text/plain; + charset="utf-8" +Content-Transfer-Encoding: 7bit +Message-ID: <200410251108.52164.josh@agliodbs.com> +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Tom, + +> regression=# set timezone to 'US/Arizona'; +> SET +> regression=# select now(); +> now +> ------------------------------- +> 2004-10-25 10:52:49.441559-07 + +Wow! When did that get fixed? How do I keep track of this stuff if you +guys keep fixing it? ;-) + +Of course, it would be very helpful if the result above could display +"Arizona" instead of the non-specific "-07", but I'm pretty sure that's +already a TODO. + +> This is the point about how interval needs to treat "day" as different +> from "24 hours". I agree with that; the fact that it's not done already +> is just a reflection of limited supply of round tuits. + +Well, when I first brought up the issue (2001) I was shot down on the basis of +spec-compliance, since SQL92 recognizes only Year/Month and +Day/Hour/Minute/etc. partitions. Glad it's up for consideration again. + +Come to think of it, it was Thomas Lockhart who shot down the idea of fixing +Interval, and he's retired now ... + +> This does not seem to me to be an argument why timestamp with time zone +> ought to be incapable of dealing with DST-aware time zones. That simply +> guarantees that calendar apps won't be able to use the datatype. If +> they still can't use it when it can do that, then we can look at the +> next blocking factor. + +That's definitely a progressive attitude .... pardon me for being pessimistic. + +> I have not said that we can't comply with the spec. I have said that +> our ambitions need to be higher than merely complying with the spec. + +Hmmm ... well, does the spec specifically prohibit DST, or just leave it out? + +-- +--Josh + +Josh Berkus +Aglio Database Solutions +San Francisco + +---------------------------(end of broadcast)--------------------------- +TIP 7: don't forget to increase your free space map settings + +From pgsql-hackers-owner+M60336=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 14:21:12 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PILAf10029 + for ; Mon, 25 Oct 2004 14:21:11 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 96C283A3B0E + for ; Mon, 25 Oct 2004 19:21:07 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 96954-06 for ; + Mon, 25 Oct 2004 18:21:06 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 528B73A3B06 + for ; Mon, 25 Oct 2004 19:21:07 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id E010F3A4192 + for ; Mon, 25 Oct 2004 19:19:46 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 96137-04 + for ; + Mon, 25 Oct 2004 18:19:41 +0000 (GMT) +Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) + by svr1.postgresql.org (Postfix) with ESMTP id A1BDA3A4190 + for ; Mon, 25 Oct 2004 19:19:42 +0100 (BST) +Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) + by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9PIJegg022094; + Mon, 25 Oct 2004 14:19:40 -0400 (EDT) +To: josh@agliodbs.com +cc: pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <200410251108.52164.josh@agliodbs.com> +References: <20041022184636.62D39EAEE02@svr1.postgresql.org> <200410250942.38212.josh@agliodbs.com> <21834.1098726893@sss.pgh.pa.us> <200410251108.52164.josh@agliodbs.com> +Comments: In-reply-to Josh Berkus + message dated "Mon, 25 Oct 2004 11:08:52 -0700" +Date: Mon, 25 Oct 2004 14:19:40 -0400 +Message-ID: <22093.1098728380@sss.pgh.pa.us> +From: Tom Lane +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Josh Berkus writes: +>> regression=# set timezone to 'US/Arizona'; +>> SET +>> regression=# select now(); +>> now +>> ------------------------------- +>> 2004-10-25 10:52:49.441559-07 + +> Wow! When did that get fixed? How do I keep track of this stuff if you +> guys keep fixing it? ;-) + +> Of course, it would be very helpful if the result above could display +> "Arizona" instead of the non-specific "-07", but I'm pretty sure that's +> already a TODO. + +Well, that is *exactly what I'm talking about*. I want timestamp with +time zone to carry "US/Arizona" not just "-07". Obviously there needs +to be some option to get the latter displayed when that's all you want, +but internally a value of the datatype needs to be able to carry full +knowledge of which timezone it's supposed to be in. Dumbing that down +to a simple numeric GMT offset isn't good enough. + +>> I have not said that we can't comply with the spec. I have said that +>> our ambitions need to be higher than merely complying with the spec. + +> Hmmm ... well, does the spec specifically prohibit DST, or just leave it out? + +It just doesn't talk about it AFAICS. + +To comply with the spec we definitely need to be *able* to support +timezone values that are simple numeric GMT offsets. But I think we +ought also to be able to store values that are references to any of +the zic database entries. This looks to me like a straightforward +extension of the spec. + +We went to all the trouble of importing src/timezone in order that we +could make a significant upgrade in our timezone capability, and now +it's time to take the steps that that enables. Before we were limited +to the lowest-common-denominator of the libc timezone routines on all +our different platforms, but now we are not... + + regards, tom lane + +---------------------------(end of broadcast)--------------------------- +TIP 6: Have you searched our list archives? + + http://archives.postgresql.org + +From pgsql-hackers-owner+M60337=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 14:28:59 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PISvf11118 + for ; Mon, 25 Oct 2004 14:28:58 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 928B13A3B43 + for ; Mon, 25 Oct 2004 19:28:54 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 00197-04 for ; + Mon, 25 Oct 2004 18:28:53 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 518C33A3B06 + for ; Mon, 25 Oct 2004 19:28:54 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 3AA743A3B0E + for ; Mon, 25 Oct 2004 19:27:17 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 99849-02 + for ; + Mon, 25 Oct 2004 18:27:07 +0000 (GMT) +Received: from zigo.dhs.org (as2-4-3.an.g.bonet.se [194.236.34.191]) + by svr1.postgresql.org (Postfix) with ESMTP id B62843A418A + for ; Mon, 25 Oct 2004 19:27:01 +0100 (BST) +Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1]) + by zigo.dhs.org (Postfix) with ESMTP + id 5AD8C8467; Mon, 25 Oct 2004 20:26:59 +0200 (CEST) +Date: Mon, 25 Oct 2004 20:26:59 +0200 (CEST) +From: Dennis Bjorklund +To: Josh Berkus +cc: pgsql-hackers@postgresql.org, Tom Lane +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <200410251108.52164.josh@agliodbs.com> +Message-ID: +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=ISO-8859-1 +Content-Transfer-Encoding: 8BIT +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +On Mon, 25 Oct 2004, Josh Berkus wrote: + +> Hmmm ... well, does the spec specifically prohibit DST, or just leave it +> out? + +It doesn't discuss it. According to the spec a timestamp with time zone is +a UTC value + a HH:MM offset from GMT. And intervals in the spec is either +a year-month value or a day-time value. One can only compare year-month +values with each other and day-time values with each other. So they avoid +the problem of the how many days is a month by not allowing it. + +The spec is not a full solution, it's also not a useless solution. I'm +happy as long as the spec is a subset of what pg implements. If not then I +would like to be able to have both but with different names or something +similar (but I think that should not be needed). + +-- +/Dennis Björklund + + +---------------------------(end of broadcast)--------------------------- +TIP 3: 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 + +From pgsql-hackers-owner+M60338=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 14:54:31 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PIsUf15052 + for ; Mon, 25 Oct 2004 14:54:30 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 3F9063A41BC + for ; Mon, 25 Oct 2004 19:54:27 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 09766-01 for ; + Mon, 25 Oct 2004 18:54:25 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id E25B63A41BA + for ; Mon, 25 Oct 2004 19:54:26 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 8FBF23A41A0 + for ; Mon, 25 Oct 2004 19:52:27 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 05475-10 + for ; + Mon, 25 Oct 2004 18:52:24 +0000 (GMT) +Received: from davinci.ethosmedia.com (server226.ethosmedia.com [209.128.84.226]) + by svr1.postgresql.org (Postfix) with ESMTP id E7C333A4182 + for ; Mon, 25 Oct 2004 19:52:24 +0100 (BST) +Received: from [64.81.245.111] (account josh@agliodbs.com HELO temoku.sf.agliodbs.com) + by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) + with ESMTP id 6553982; Mon, 25 Oct 2004 11:53:49 -0700 +From: Josh Berkus +Reply-To: josh@agliodbs.com +Organization: Aglio Database Solutions +To: pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +Date: Mon, 25 Oct 2004 11:54:33 -0700 +User-Agent: KMail/1.6.2 +cc: Dennis Bjorklund , Tom Lane +References: +In-Reply-To: +MIME-Version: 1.0 +Content-Disposition: inline +Content-Type: text/plain; + charset="iso-8859-1" +Content-Transfer-Encoding: 7bit +Message-ID: <200410251154.33532.josh@agliodbs.com> +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Dennis, + +> It doesn't discuss it. According to the spec a timestamp with time zone is +> a UTC value + a HH:MM offset from GMT. And intervals in the spec is either +> a year-month value or a day-time value. One can only compare year-month +> values with each other and day-time values with each other. So they avoid +> the problem of the how many days is a month by not allowing it. + +That's not what Tom and I were talking about. The issue is that the spec +defines Days/Weeks as being an agglomeration of hours and not an atomic +entity like Months/Years are. This leads to some wierd and +calendar-breaking behavior when combined with DST, for example: + +template1=> select '2004-10-09 10:00 PDT'::TIMESTAMPTZ + '45 days'::INTERVAL +template1-> ; + ?column? +------------------------ + 2004-11-23 09:00:00-08 +(1 row) + +Because of the DST shift, you get an hour shift which is most decidely not +anything real human beings would expect from a calendar. The answer is to +try-partition INTERVAL values, as: + +Hour/Minute/Second/ms +Day/Week +Month/Year + +However, this could be considered to break the spec; certainly Thomas thought +it did. My defense is that the SQL committee made some mistakes, and +interval is a big one. + +-- +--Josh + +Josh Berkus +Aglio Database Solutions +San Francisco + +---------------------------(end of broadcast)--------------------------- +TIP 2: you can get off all lists at once with the unregister command + (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) + +From pgsql-hackers-owner+M60339=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 15:07:28 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PJ7Qf17120 + for ; Mon, 25 Oct 2004 15:07:27 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 666B73A41B0 + for ; Mon, 25 Oct 2004 20:07:23 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 13808-02 for ; + Mon, 25 Oct 2004 19:07:21 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 207F03A41AE + for ; Mon, 25 Oct 2004 20:07:23 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id D243F3A4182 + for ; Mon, 25 Oct 2004 20:04:23 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 12122-06 + for ; + Mon, 25 Oct 2004 19:04:18 +0000 (GMT) +Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) + by svr1.postgresql.org (Postfix) with ESMTP id 52FFB3A3CF2 + for ; Mon, 25 Oct 2004 20:04:18 +0100 (BST) +Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) + by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9PJ4GUm022503; + Mon, 25 Oct 2004 15:04:16 -0400 (EDT) +To: josh@agliodbs.com +cc: pgsql-hackers@postgresql.org, Dennis Bjorklund +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <200410251154.33532.josh@agliodbs.com> +References: <200410251154.33532.josh@agliodbs.com> +Comments: In-reply-to Josh Berkus + message dated "Mon, 25 Oct 2004 11:54:33 -0700" +Date: Mon, 25 Oct 2004 15:04:16 -0400 +Message-ID: <22502.1098731056@sss.pgh.pa.us> +From: Tom Lane +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Josh Berkus writes: +>> It doesn't discuss it. According to the spec a timestamp with time zone is +>> a UTC value + a HH:MM offset from GMT. And intervals in the spec is either +>> a year-month value or a day-time value. One can only compare year-month +>> values with each other and day-time values with each other. So they avoid +>> the problem of the how many days is a month by not allowing it. + +> That's not what Tom and I were talking about. The issue is that the spec +> defines Days/Weeks as being an agglomeration of hours and not an atomic +> entity like Months/Years are. + +I think though that these points are closely related. The reason the +spec does that is exactly that they are ignoring DST and so they can +assume that 1 day == 24 hours == 86400 seconds. In a DST-aware world +you have to make a separation between days and the smaller units, just +as months are separated from smaller units because there's not a fixed +conversion factor. + +To some extent the interval and timestamptz issues are orthogonal, but +I think it would be good to fix them in the same release if possible. +There will undoubtedly be some backwards-compatibility problems, and +I suppose that users would prefer to take them all at once than via +the chinese water torture method ... + +> However, this could be considered to break the spec; certainly Thomas +> thought it did. My defense is that the SQL committee made some +> mistakes, and interval is a big one. + +I'm not clear to what extent we have to actually break the spec, as +opposed to extend it, in order to do this to the "interval" type. To do +everything the spec says we need to do, we'll have to be able to make +some comparisons that aren't strictly valid (which amounts to assuming +that 1 day == 24 hours for some limited purposes) but we already do much +the same things with respect to months. (See other thread about whether +1 year == 360 days...) + + regards, tom lane + +---------------------------(end of broadcast)--------------------------- +TIP 5: Have you checked our extensive FAQ? + + http://www.postgresql.org/docs/faqs/FAQ.html + +From pgsql-hackers-owner+M60340=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 15:12:25 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PJCNf17962 + for ; Mon, 25 Oct 2004 15:12:24 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id AA6B73A41C2 + for ; Mon, 25 Oct 2004 20:12:20 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 13059-08 for ; + Mon, 25 Oct 2004 19:12:19 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 50BAF3A41C1 + for ; Mon, 25 Oct 2004 20:12:20 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 2035E3A41B9 + for ; Mon, 25 Oct 2004 20:11:08 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 13808-08 + for ; + Mon, 25 Oct 2004 19:11:04 +0000 (GMT) +Received: from zigo.dhs.org (as2-4-3.an.g.bonet.se [194.236.34.191]) + by svr1.postgresql.org (Postfix) with ESMTP id 5D4C03A41B2 + for ; Mon, 25 Oct 2004 20:11:04 +0100 (BST) +Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1]) + by zigo.dhs.org (Postfix) with ESMTP + id 156808467; Mon, 25 Oct 2004 21:11:04 +0200 (CEST) +Date: Mon, 25 Oct 2004 21:11:04 +0200 (CEST) +From: Dennis Bjorklund +To: Josh Berkus +cc: pgsql-hackers@postgresql.org, Tom Lane +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <200410251154.33532.josh@agliodbs.com> +Message-ID: +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=ISO-8859-1 +Content-Transfer-Encoding: 8BIT +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +On Mon, 25 Oct 2004, Josh Berkus wrote: + +> Dennis, +> +> > It doesn't discuss it. According to the spec a timestamp with time zone is +> > a UTC value + a HH:MM offset from GMT. And intervals in the spec is either +> > a year-month value or a day-time value. One can only compare year-month +> > values with each other and day-time values with each other. So they avoid +> > the problem of the how many days is a month by not allowing it. +> +> That's not what Tom and I were talking about. + +You wanted to know what the standard said, and I told what I knew. + +> The issue is that the spec defines Days/Weeks as being an agglomeration +> of hours and not an atomic entity like Months/Years are. + +I don't know what you mean with this. The standard does treat them as + +year +month +day +hour +minute +second (with fractions) + +There is no weeks there, if that is what you mean. + +> This leads to some wierd and calendar-breaking behavior when combined +> with DST, for example: +> +> template1=> select '2004-10-09 10:00 PDT'::TIMESTAMPTZ + '45 days'::INTERVAL +> template1-> ; +> ?column? +> ------------------------ +> 2004-11-23 09:00:00-08 +> (1 row) +> +> Because of the DST shift, you get an hour shift which is most decidely not +> anything real human beings would expect from a calendar. + +I don't see how the above can be caused by the representation of an +interval. The above timestamp is + +2004-10-09 10:00 PDT + +which in the standard would be + +2004-10-09 10:00 -07 + +and after the additon would be + +2004-11-23 10:00:00-07 + +Here the time zone is wrong since the standard does not know about named +zones and dst. + +An implementation like the one Tom (and I) want would start with + +2004-10-09 10:00 PDT + +and then after the addition one would get + +2004-11-23 10:00:00 PST + +At least that's my understanding of what we want and what we can get (plus +that we also need to support HH:MM tz values since those also exist in the +world, check this emails header for example). + +It's possible that you discuss something else, but that has been lost on +me so far. + +-- +/Dennis Björklund + + +---------------------------(end of broadcast)--------------------------- +TIP 3: 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 + +From pgsql-hackers-owner+M60341=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 15:15:18 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PJFHf18332 + for ; Mon, 25 Oct 2004 15:15:17 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id D2DE53A41BD + for ; Mon, 25 Oct 2004 20:15:13 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 16799-02 for ; + Mon, 25 Oct 2004 19:15:12 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 928BF3A41A6 + for ; Mon, 25 Oct 2004 20:15:13 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 2AE4F3A41C4 + for ; Mon, 25 Oct 2004 20:13:02 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 15871-02 + for ; + Mon, 25 Oct 2004 19:12:59 +0000 (GMT) +Received: from zigo.dhs.org (as2-4-3.an.g.bonet.se [194.236.34.191]) + by svr1.postgresql.org (Postfix) with ESMTP id 6881F3A41BC + for ; Mon, 25 Oct 2004 20:12:59 +0100 (BST) +Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1]) + by zigo.dhs.org (Postfix) with ESMTP + id 09CC48467; Mon, 25 Oct 2004 21:12:59 +0200 (CEST) +Date: Mon, 25 Oct 2004 21:12:59 +0200 (CEST) +From: Dennis Bjorklund +To: Josh Berkus +cc: pgsql-hackers@postgresql.org, Tom Lane +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <200410251154.33532.josh@agliodbs.com> +Message-ID: +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=ISO-8859-1 +Content-Transfer-Encoding: 8BIT +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +On Mon, 25 Oct 2004, Josh Berkus wrote: + +> Hour/Minute/Second/ms +> Day/Week +> Month/Year + +And just when I pressed "send" on the previous mail I got the problem +:-) + +-- +/Dennis Björklund + + +---------------------------(end of broadcast)--------------------------- +TIP 2: you can get off all lists at once with the unregister command + (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) + +From pgsql-hackers-owner+M60342=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 15:20:32 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PJKVf19055 + for ; Mon, 25 Oct 2004 15:20:32 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 0D6103A418E + for ; Mon, 25 Oct 2004 20:20:28 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 17860-02 for ; + Mon, 25 Oct 2004 19:20:26 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id AA4353A41B2 + for ; Mon, 25 Oct 2004 20:20:27 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 131A23A41BB + for ; Mon, 25 Oct 2004 20:19:02 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 16799-06 + for ; + Mon, 25 Oct 2004 19:18:51 +0000 (GMT) +Received: from zigo.dhs.org (as2-4-3.an.g.bonet.se [194.236.34.191]) + by svr1.postgresql.org (Postfix) with ESMTP id BBD663A3B06 + for ; Mon, 25 Oct 2004 20:18:52 +0100 (BST) +Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1]) + by zigo.dhs.org (Postfix) with ESMTP + id 36B248467; Mon, 25 Oct 2004 21:18:52 +0200 (CEST) +Date: Mon, 25 Oct 2004 21:18:52 +0200 (CEST) +From: Dennis Bjorklund +To: Josh Berkus +cc: pgsql-hackers@postgresql.org, Tom Lane +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <200410251154.33532.josh@agliodbs.com> +Message-ID: +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=ISO-8859-1 +Content-Transfer-Encoding: 8BIT +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +On Mon, 25 Oct 2004, Josh Berkus wrote: + +> Hour/Minute/Second/ms +> Day/Week +> Month/Year + +This is embarrasing. I'm still a bit confused :-) + +The standard treat days as a separate entry, it does not assume that a day +is 24 hours. It restricts the hour field to the interval 0-23 so one can +never have something like 25 hours. So it does not need to worry about how +many days that translate to. + +And why do we need weeks also? + +Well, this is the last mail I send before I've been thinking about this +for a while more :-) + +-- +/Dennis Björklund + + +---------------------------(end of broadcast)--------------------------- +TIP 6: Have you searched our list archives? + + http://archives.postgresql.org + +From pgsql-hackers-owner+M60344=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 15:36:30 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PJaTf21902 + for ; Mon, 25 Oct 2004 15:36:29 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id E2F703A3CF2 + for ; Mon, 25 Oct 2004 20:36:25 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 23154-01 for ; + Mon, 25 Oct 2004 19:36:24 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 7935E3A3AC2 + for ; Mon, 25 Oct 2004 20:36:25 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 13AB03A41BC + for ; Mon, 25 Oct 2004 20:35:09 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 21223-08 + for ; + Mon, 25 Oct 2004 19:35:05 +0000 (GMT) +Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) + by svr1.postgresql.org (Postfix) with ESMTP id EA1E73A41B3 + for ; Mon, 25 Oct 2004 20:35:06 +0100 (BST) +Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) + by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9PJZ429022817; + Mon, 25 Oct 2004 15:35:04 -0400 (EDT) +To: Dennis Bjorklund +cc: Josh Berkus , pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: +References: +Comments: In-reply-to Dennis Bjorklund + message dated "Mon, 25 Oct 2004 21:18:52 +0200" +Date: Mon, 25 Oct 2004 15:35:04 -0400 +Message-ID: <22816.1098732904@sss.pgh.pa.us> +From: Tom Lane +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Dennis Bjorklund writes: +> The standard treat days as a separate entry, it does not assume that a day +> is 24 hours. + +SQL92 says + + 4.5.2 Intervals + + There are two classes of intervals. One class, called year-month + intervals, has an express or implied datetime precision that in- + cludes no fields other than YEAR and MONTH, though not both are + required. The other class, called day-time intervals, has an ex- + press or implied interval precision that can include any fields + other than YEAR or MONTH. + +AFAICS the reason for this rule is that they expect all Y/M intervals to +be comparable (which they are) and they also expect all D/H/M/S intervals +to be comparable, which you can only do by assuming that 1 D == 24 H. + +It seems to me though that we can store days separately and do interval +comparisons with the assumption 1 D == 24 H, and be perfectly +SQL-compatible as far as that goes, and still make good use of the +separate day info when adding to a timestamptz that has a DST-aware +timezone. In a non-DST-aware timezone the addition will act the same as +if we weren't distinguishing days from h/m/s. Therefore, an application +using only the spec-defined features (ie, only fixed-numeric-offset +timezones) will see no deviation from the spec behavior. + + regards, tom lane + +---------------------------(end of broadcast)--------------------------- +TIP 4: Don't 'kill -9' the postmaster + +From pgsql-hackers-owner+M60343=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 15:31:38 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PJVZf21237 + for ; Mon, 25 Oct 2004 15:31:36 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 139FF3A41C0 + for ; Mon, 25 Oct 2004 20:31:32 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 21073-05 for ; + Mon, 25 Oct 2004 19:31:30 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 9E1723A41B8 + for ; Mon, 25 Oct 2004 20:31:31 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id C7A9E3A41A0 + for ; Mon, 25 Oct 2004 20:30:15 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 19517-04 + for ; + Mon, 25 Oct 2004 19:30:05 +0000 (GMT) +Received: from wolff.to (wolff.to [66.93.249.74]) + by svr1.postgresql.org (Postfix) with SMTP id 9A0483A4182 + for ; Mon, 25 Oct 2004 20:30:05 +0100 (BST) +Received: (qmail 26726 invoked by uid 500); 25 Oct 2004 19:40:57 -0000 +Date: Mon, 25 Oct 2004 14:40:57 -0500 +From: Bruno Wolff III +To: Dennis Bjorklund +cc: Josh Berkus , pgsql-hackers@postgresql.org, + Tom Lane +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +Message-ID: <20041025194057.GA26356@wolff.to> +Mail-Followup-To: Dennis Bjorklund , + Josh Berkus , pgsql-hackers@postgresql.org, + Tom Lane +References: <200410251154.33532.josh@agliodbs.com> +MIME-Version: 1.0 +Content-Type: text/plain; charset=us-ascii +Content-Disposition: inline +In-Reply-To: +User-Agent: Mutt/1.5.6i +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +On Mon, Oct 25, 2004 at 21:18:52 +0200, + Dennis Bjorklund wrote: +> On Mon, 25 Oct 2004, Josh Berkus wrote: +> +> > Hour/Minute/Second/ms +> > Day/Week +> > Month/Year +> +> This is embarrasing. I'm still a bit confused :-) +> +> The standard treat days as a separate entry, it does not assume that a day +> is 24 hours. It restricts the hour field to the interval 0-23 so one can +> never have something like 25 hours. So it does not need to worry about how +> many days that translate to. +> +> And why do we need weeks also? + +For convenience. Just like years are a group of months, weeks are a group +of days. + +---------------------------(end of broadcast)--------------------------- +TIP 4: Don't 'kill -9' the postmaster + +From pgsql-hackers-owner+M60346=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 16:10:09 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PKA8f26656 + for ; Mon, 25 Oct 2004 16:10:08 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 07A343A4201 + for ; Mon, 25 Oct 2004 21:10:05 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 62669-07 for ; + Mon, 25 Oct 2004 20:10:03 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id ACA1C3A4200 + for ; Mon, 25 Oct 2004 21:10:04 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 251663A41FE + for ; Mon, 25 Oct 2004 21:08:18 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 62513-04 + for ; + Mon, 25 Oct 2004 20:08:15 +0000 (GMT) +Received: from zigo.dhs.org (as2-4-3.an.g.bonet.se [194.236.34.191]) + by svr1.postgresql.org (Postfix) with ESMTP id 3C94D3A3AC2 + for ; Mon, 25 Oct 2004 21:08:14 +0100 (BST) +Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1]) + by zigo.dhs.org (Postfix) with ESMTP + id 025568467; Mon, 25 Oct 2004 22:08:14 +0200 (CEST) +Date: Mon, 25 Oct 2004 22:08:14 +0200 (CEST) +From: Dennis Bjorklund +To: Tom Lane +cc: Josh Berkus , +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <22816.1098732904@sss.pgh.pa.us> +Message-ID: +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=ISO-8859-1 +Content-Transfer-Encoding: 8BIT +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +On Mon, 25 Oct 2004, Tom Lane wrote: + +> There are two classes of intervals. One class, called year-month +> intervals, has an express or implied datetime precision that in- +> cludes no fields other than YEAR and MONTH, though not both are +> required. The other class, called day-time intervals, has an ex- +> press or implied interval precision that can include any fields +> other than YEAR or MONTH. +> +> AFAICS the reason for this rule is that they expect all Y/M intervals to +> be comparable (which they are) and they also expect all D/H/M/S intervals +> to be comparable, which you can only do by assuming that 1 D == 24 H. + +I said I was not going to send any more mails, but here we go again :-) + +The standard restrict the hour field to the interval 0-23, so there can +never be any compare between for example '1 day 1 hour' and '25 hours'. +This means that one can not add two intervals together to get a bigger +one but that it would still work to do timestamp+interval+interval. + +> It seems to me though that we can store days separately and do interval +> comparisons with the assumption 1 D == 24 H, and be perfectly +> SQL-compatible as far as that goes, and still make good use of the +> separate day info when adding to a timestamptz that has a DST-aware +> timezone. In a non-DST-aware timezone the addition will act the same as +> if we weren't distinguishing days from h/m/s. Therefore, an application +> using only the spec-defined features (ie, only fixed-numeric-offset +> timezones) will see no deviation from the spec behavior. + +I agree with this. + +-- +/Dennis Björklund + + +---------------------------(end of broadcast)--------------------------- +TIP 7: don't forget to increase your free space map settings + +From pgsql-hackers-owner+M60347=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 16:20:45 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PKKif28309 + for ; Mon, 25 Oct 2004 16:20:44 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 65B453A4215 + for ; Mon, 25 Oct 2004 21:20:40 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 66092-07 for ; + Mon, 25 Oct 2004 20:20:38 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 1DCD53A4210 + for ; Mon, 25 Oct 2004 21:20:40 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 803933A3AC2 + for ; Mon, 25 Oct 2004 21:18:00 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 64513-06 + for ; + Mon, 25 Oct 2004 20:17:49 +0000 (GMT) +Received: from davinci.ethosmedia.com (server226.ethosmedia.com [209.128.84.226]) + by svr1.postgresql.org (Postfix) with ESMTP id 726633A3CF2 + for ; Mon, 25 Oct 2004 21:17:51 +0100 (BST) +Received: from [64.81.245.111] (account josh@agliodbs.com HELO temoku.sf.agliodbs.com) + by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) + with ESMTP id 6554308; Mon, 25 Oct 2004 13:19:17 -0700 +From: Josh Berkus +Reply-To: josh@agliodbs.com +Organization: Aglio Database Solutions +To: pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +Date: Mon, 25 Oct 2004 13:20:01 -0700 +User-Agent: KMail/1.6.2 +cc: Dennis Bjorklund , Tom Lane +References: +In-Reply-To: +MIME-Version: 1.0 +Content-Disposition: inline +Content-Type: text/plain; + charset="iso-8859-1" +Content-Transfer-Encoding: 7bit +Message-ID: <200410251320.01311.josh@agliodbs.com> +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Dennis, + +> An implementation like the one Tom (and I) want would start with +> +> 2004-10-09 10:00 PDT +> +> and then after the addition one would get +> +> 2004-11-23 10:00:00 PST + +Sounds like we're on the same page then. + +> The standard restrict the hour field to the interval 0-23, so there can +> never be any compare between for example '1 day 1 hour' and '25 hours'. +> This means that one can not add two intervals together to get a bigger +> one but that it would still work to do timestamp+interval+interval. + +Hour field of the timestamp, or hour field of interval? There a world of +difference. + +As long as we're willing to live with the understanding that +1day 1 hour may +produce a slightly different result than + 25 hours, I don't see the problem. +Currently I can add +900 hours if I like, postgreSQL will support it. + +-- +--Josh + +Josh Berkus +Aglio Database Solutions +San Francisco + +---------------------------(end of broadcast)--------------------------- +TIP 3: 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 + +From pgsql-hackers-owner+M60348=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 16:24:27 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PKOQf28960 + for ; Mon, 25 Oct 2004 16:24:26 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 2AD843A419B + for ; Mon, 25 Oct 2004 21:24:22 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 68942-02 for ; + Mon, 25 Oct 2004 20:24:20 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id E08EE3A4182 + for ; Mon, 25 Oct 2004 21:24:21 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id C8C413A419B + for ; Mon, 25 Oct 2004 21:22:31 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 68322-02 + for ; + Mon, 25 Oct 2004 20:22:28 +0000 (GMT) +Received: from zigo.dhs.org (as2-4-3.an.g.bonet.se [194.236.34.191]) + by svr1.postgresql.org (Postfix) with ESMTP id BB2F53A41EC + for ; Mon, 25 Oct 2004 21:22:29 +0100 (BST) +Received: from zigo.zigo.dhs.org (zigo.zigo.dhs.org [192.168.0.1]) + by zigo.dhs.org (Postfix) with ESMTP + id 2B7368467; Mon, 25 Oct 2004 22:22:29 +0200 (CEST) +Date: Mon, 25 Oct 2004 22:22:29 +0200 (CEST) +From: Dennis Bjorklund +To: Josh Berkus +cc: pgsql-hackers@postgresql.org, Tom Lane +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <200410251320.01311.josh@agliodbs.com> +Message-ID: +MIME-Version: 1.0 +Content-Type: TEXT/PLAIN; charset=ISO-8859-1 +Content-Transfer-Encoding: 8BIT +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +On Mon, 25 Oct 2004, Josh Berkus wrote: + +> > The standard restrict the hour field to the interval 0-23, so there can +> > never be any compare between for example '1 day 1 hour' and '25 hours'. +> > This means that one can not add two intervals together to get a bigger +> > one but that it would still work to do timestamp+interval+interval. +> +> Hour field of the timestamp, or hour field of interval? There a world of +> difference. + +Hour field of an interval can be 0-23 according to the spec (doesn't say +that we need that restriction, but we do need to understand what the spec +say). + +-- +/Dennis Björklund + + +---------------------------(end of broadcast)--------------------------- +TIP 3: 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 + +From pgsql-hackers-owner+M60349=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 16:34:48 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9PKYlf00828 + for ; Mon, 25 Oct 2004 16:34:47 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id D7AD63A3BF2 + for ; Mon, 25 Oct 2004 21:34:43 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 72374-02 for ; + Mon, 25 Oct 2004 20:34:41 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 99E183A3B88 + for ; Mon, 25 Oct 2004 21:34:43 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 534763A3C84 + for ; Mon, 25 Oct 2004 21:32:42 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 71412-04 + for ; + Mon, 25 Oct 2004 20:32:36 +0000 (GMT) +Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) + by svr1.postgresql.org (Postfix) with ESMTP id 798CA3A3B88 + for ; Mon, 25 Oct 2004 21:32:38 +0100 (BST) +Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) + by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9PKWbDK023330; + Mon, 25 Oct 2004 16:32:37 -0400 (EDT) +To: josh@agliodbs.com +cc: pgsql-hackers@postgresql.org, Dennis Bjorklund +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <200410251320.01311.josh@agliodbs.com> +References: <200410251320.01311.josh@agliodbs.com> +Comments: In-reply-to Josh Berkus + message dated "Mon, 25 Oct 2004 13:20:01 -0700" +Date: Mon, 25 Oct 2004 16:32:37 -0400 +Message-ID: <23329.1098736357@sss.pgh.pa.us> +From: Tom Lane +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Josh Berkus writes: +> As long as we're willing to live with the understanding that +1day 1 hour may +> produce a slightly different result than + 25 hours, I don't see the problem. + +Right, which is exactly why we can't accept the spec's restriction that +the hour field be limited to 0-23. People may legitimately want to add +48 hours to a timestamp, and *not* have that mean the same as adding +"2 days". Besides, we would have a backwards-compatibility problem if +we tried to forbid it, since as you note we've always accepted such input. + + regards, tom lane + +---------------------------(end of broadcast)--------------------------- +TIP 5: Have you checked our extensive FAQ? + + http://www.postgresql.org/docs/faqs/FAQ.html + +From pgsql-hackers-owner+M60371=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 21:26:49 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9Q1Qmf13334 + for ; Mon, 25 Oct 2004 21:26:49 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id A8E2F3A42A1 + for ; Tue, 26 Oct 2004 02:26:43 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 52441-04 for ; + Tue, 26 Oct 2004 01:26:39 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 5F7503A42A0 + for ; Tue, 26 Oct 2004 02:26:43 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 02D5D3A427F + for ; Tue, 26 Oct 2004 02:25:27 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 52284-03 + for ; + Tue, 26 Oct 2004 01:25:22 +0000 (GMT) +Received: from houston.familyhealth.com.au (houston.au.fhnetwork.com [203.22.197.21]) + by svr1.postgresql.org (Postfix) with ESMTP id 5C0723A42A0 + for ; Tue, 26 Oct 2004 02:25:25 +0100 (BST) +Received: from houston.familyhealth.com.au (localhost [127.0.0.1]) + by houston.familyhealth.com.au (Postfix) with ESMTP id 1BDCB2506C; + Tue, 26 Oct 2004 09:25:25 +0800 (WST) +Received: from [192.168.0.40] (work-40.internal [192.168.0.40]) + by houston.familyhealth.com.au (Postfix) with ESMTP id 14E452506B; + Tue, 26 Oct 2004 09:25:25 +0800 (WST) +Message-ID: <417DA785.7010208@familyhealth.com.au> +Date: Tue, 26 Oct 2004 09:25:25 +0800 +From: Christopher Kings-Lynne +User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) +X-Accept-Language: en-us, en +MIME-Version: 1.0 +To: Tom Lane +cc: josh@agliodbs.com, pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +References: <20041022184636.62D39EAEE02@svr1.postgresql.org> <200410250942.38212.josh@agliodbs.com> <21834.1098726893@sss.pgh.pa.us> <200410251108.52164.josh@agliodbs.com> <22093.1098728380@sss.pgh.pa.us> +In-Reply-To: <22093.1098728380@sss.pgh.pa.us> +Content-Type: text/plain; charset=ISO-8859-1; format=flowed +Content-Transfer-Encoding: 7bit +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +>>>regression=# set timezone to 'US/Arizona'; +>>>SET +>>>regression=# select now(); +>>>now +>>>------------------------------- +>>>2004-10-25 10:52:49.441559-07 +> +> +>>Wow! When did that get fixed? How do I keep track of this stuff if you +>>guys keep fixing it? ;-) + +That's worked for ages. What doesn't work is this: + +usatest=# select current_timestamp at time zone 'US/Arizona'; +ERROR: time zone "us/arizona" not recognized + +Chris + +---------------------------(end of broadcast)--------------------------- +TIP 4: Don't 'kill -9' the postmaster + +From pgsql-hackers-owner+M60372=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 21:41:27 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9Q1fPf15148 + for ; Mon, 25 Oct 2004 21:41:26 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id A941F3A4278 + for ; Tue, 26 Oct 2004 02:41:20 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 54196-09 for ; + Tue, 26 Oct 2004 01:41:16 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id 50EEA3A4255 + for ; Tue, 26 Oct 2004 02:41:20 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 34AC13A4288 + for ; Tue, 26 Oct 2004 02:39:55 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 54779-07 + for ; + Tue, 26 Oct 2004 01:39:42 +0000 (GMT) +Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) + by svr1.postgresql.org (Postfix) with ESMTP id 3E9493A1D91 + for ; Tue, 26 Oct 2004 02:39:45 +0100 (BST) +Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) + by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9Q1ditA003223; + Mon, 25 Oct 2004 21:39:46 -0400 (EDT) +To: Dennis Bjorklund +cc: Robert Treat , pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: +References: +Comments: In-reply-to Dennis Bjorklund + message dated "Sat, 23 Oct 2004 08:09:05 +0200" +Date: Mon, 25 Oct 2004 21:39:43 -0400 +Message-ID: <3222.1098754783@sss.pgh.pa.us> +From: Tom Lane +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Dennis Bjorklund writes: +> So if I understand you correctly you are planning to extend the current +> timestamp type to work with both named time zones and HH:MM ones? I didn't +> think you wanted the last one since your plan was to store a UTC+OID where +> the OID pointed to a named time zone. And I guess that you don't plan to +> add 00:00, 00:01, 00:02, ... as named zones with an OID. + +I missed getting back to you on this, but I think we can do both. Some +random points: + +* Once we expand timestamptz to bigger than 8 bytes, there's essentially +zero cost to making it 12 bytes, and for that matter we could go to 16 +without much penalty, because of alignment considerations. So there's +plenty of space. + +* What we need is to be able to represent either a fixed offset from UTC +or a reference of some kind to a zic database entry. The most +bit-splurging way of doing the former is a signed offset in seconds from +Greenwich, which would take 17 bits. It'd be good enough to represent +the offset in minutes, which needs only 11 bits. + +* I suggested OIDs for referencing zic entries, but we don't have to do +that; any old mapping table will do. 16 bits would surely be plenty to +assign a unique label to every present and future zic entry. + +* My inclination therefore is to extend timestamptz with two 16-bit +fields, one being the offset from UTC (in minutes) and one being the +zic identifier. If the identifier is zero then it's a straight numeric +offset from UTC and the offset field is all you need (this is the SQL +spec compatible case). If the identifier is not zero then it gives you +an index to look up the timezone rules. However, there is no need for +the offset field to go to waste; we should store the offset anyway, +since that might save a trip to the zic database in some cases. + +* It's not clear to me yet whether the stored offset in the second case +should be the zone's standard UTC offset (thus always the same for a +given zone ID) or the current-time offset for the timestamp (thus +different if the timestamp is in daylight-savings or standard time). + +* If we store the current-time offset then it almost doesn't matter +whether the timestamp itself is stored as a UTC or local time value; +you can trivially translate either to the other by adding or subtracting +the offset (*60). But I'm inclined to store UTC for consistency with +past practice, and because it will make comparisons a bit faster: you +can compare the timestamps without adjusting first. Generally I think +comparisons ought to be the best-optimized operations in a Postgres +datatype, because index operations will do a ton of 'em. (We definitely +do NOT want to have to visit the zic database in order to compare two +timestamptz values.) + + regards, tom lane + +---------------------------(end of broadcast)--------------------------- +TIP 4: Don't 'kill -9' the postmaster + +From pgsql-hackers-owner+M60373=pgman=candle.pha.pa.us@postgresql.org Mon Oct 25 21:53:52 2004 +Return-path: +Received: from svr1.postgresql.org (svr1.postgresql.org [200.46.204.71]) + by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id i9Q1rpf17038 + for ; Mon, 25 Oct 2004 21:53:51 -0400 (EDT) +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 1B6153A42AC + for ; Tue, 26 Oct 2004 02:53:46 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 58642-09 for ; + Tue, 26 Oct 2004 01:53:42 +0000 (GMT) +Received: from postgresql.org (svr1.postgresql.org [200.46.204.71]) + by svr1.postgresql.org (Postfix) with ESMTP id CE6DE3A42A7 + for ; Tue, 26 Oct 2004 02:53:45 +0100 (BST) +X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org +Received: from localhost (unknown [200.46.204.144]) + by svr1.postgresql.org (Postfix) with ESMTP id 768EE3A429E + for ; Tue, 26 Oct 2004 02:52:13 +0100 (BST) +Received: from svr1.postgresql.org ([200.46.204.71]) + by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) + with ESMTP id 58205-07 + for ; + Tue, 26 Oct 2004 01:52:07 +0000 (GMT) +Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) + by svr1.postgresql.org (Postfix) with ESMTP id 397C43A4294 + for ; Tue, 26 Oct 2004 02:52:11 +0100 (BST) +Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) + by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9Q1q07B003307; + Mon, 25 Oct 2004 21:52:00 -0400 (EDT) +To: Christopher Kings-Lynne +cc: josh@agliodbs.com, pgsql-hackers@postgresql.org +Subject: Re: [HACKERS] timestamp with time zone a la sql99 +In-Reply-To: <417DA785.7010208@familyhealth.com.au> +References: <20041022184636.62D39EAEE02@svr1.postgresql.org> <200410250942.38212.josh@agliodbs.com> <21834.1098726893@sss.pgh.pa.us> <200410251108.52164.josh@agliodbs.com> <22093.1098728380@sss.pgh.pa.us> <417DA785.7010208@familyhealth.com.au> +Comments: In-reply-to Christopher Kings-Lynne + message dated "Tue, 26 Oct 2004 09:25:25 +0800" +Date: Mon, 25 Oct 2004 21:52:00 -0400 +Message-ID: <3306.1098755520@sss.pgh.pa.us> +From: Tom Lane +X-Virus-Scanned: by amavisd-new at hub.org +X-Mailing-List: pgsql-hackers +Precedence: bulk +Sender: pgsql-hackers-owner@postgresql.org +X-Virus-Scanned: by amavisd-new at hub.org +Status: OR + +Christopher Kings-Lynne writes: +> That's worked for ages. What doesn't work is this: + +> usatest=# select current_timestamp at time zone 'US/Arizona'; +> ERROR: time zone "us/arizona" not recognized + +Right, and similarly you can do + +regression=# select '2004-10-25 21:32:33.430222 MST'::timestamptz; + timestamptz +------------------------------- + 2004-10-26 00:32:33.430222-04 +(1 row) + +but not + +regression=# select '2004-10-25 21:32:33.430222 US/Arizona'::timestamptz; +ERROR: invalid input syntax for type timestamp with time zone: "2004-10-25 21:32:33.430222 US/Arizona" + +I would like to see both of these cases working in 8.1; and furthermore +I'd like to see the timezone specs coming back as entered, not as bare +numeric offsets. (This will need to be adjustable via a DateStyle +option, of course, but I want the information to be in there whether it +is displayed or not.) + + regards, tom lane + +---------------------------(end of broadcast)--------------------------- +TIP 7: don't forget to increase your free space map settings + -- 2.11.0