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


Thu, 21 Oct 2004 13:22:40 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
timestamp with time zone a la sql99
Dennis Bjorklund <db@zigo.dhs.org> 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

Thu, 21 Oct 2004 10:29:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: timestamp with time zone a la sql99 
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


Thu, 21 Oct 2004 17:01:52 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Re: timestamp with time zone a la sql99 
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

Fri, 22 Oct 2004 08:37:33 -0400
Robert Treat <xzilla@users.sourceforge.net>
Re: timestamp with time zone a la sql99
Robert Treat <xzilla@users.sourceforge.net> 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

Fri, 22 Oct 2004 10:13:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: timestamp with time zone a la sql99 
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


Fri, 22 Oct 2004 16:28:12 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Re: timestamp with time zone a la sql99 
Dennis Bjorklund <db@zigo.dhs.org> 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

Fri, 22 Oct 2004 10:54:19 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: timestamp with time zone a la sql99 
On Fri, Oct 22, 2004 at 16:28:12 +0200,
  Dennis Bjorklund <db@zigo.dhs.org> 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.

Fri, 22 Oct 2004 09:56:44 -0500
Bruno Wolff III <bruno@wolff.to>
Re: timestamp with time zone a la sql99
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


Fri, 22 Oct 2004 17:34:19 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Re: timestamp with time zone a la sql99 
Dennis Bjorklund <db@zigo.dhs.org> 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

Fri, 22 Oct 2004 17:01:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: timestamp with time zone a la sql99 
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


Fri, 22 Oct 2004 23:18:07 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Re: timestamp with time zone a la sql99 
Dennis Bjorklund <db@zigo.dhs.org> 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

Fri, 22 Oct 2004 17:38:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: timestamp with time zone a la sql99 
>> 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

Fri, 22 Oct 2004 17:48:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: timestamp with time zone a la sql99 
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


Sat, 23 Oct 2004 08:09:05 +0200 (CEST)
Dennis Bjorklund <db@zigo.dhs.org>
Re: timestamp with time zone a la sql99 
Dennis Bjorklund <db@zigo.dhs.org> 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

Mon, 25 Oct 2004 21:39:43 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: timestamp with time zone a la sql99 

Added to TODO:

	* Once we expand timestamptz to bigger than 8 bytes, there's essentially


---------------------------------------------------------------------------

Tom Lane wrote:
> Dennis Bjorklund <db@zigo.dhs.org> 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
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 26 Oct 2004 15:41:08 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: timestamp with time zone a la sql99

This thread has been added as a link on the TODO list under TODO.detail.

---------------------------------------------------------------------------

Dennis Bjorklund 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. 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)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sat, 4 Jun 2005 20:20:26 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: timestamp with time zone a la sql99