
Imho the behavior of timestamp code is somewhat awkward 
for dates that do not fit into a time_t (>2038 or < 1901).

Times in the time_t range are displayed in local time including DST.
Times outside that range are displayed in UTC. I would have expected
UTC plus local offset not taking DST into account. 

When setting datestyle to ISO I get a timezone offset, even when SQL99
says timezone is only available if the column was defined as 
"timestamp with timezone". Dealing with the stupid mktime issue
I think the standard intended to not involve any issues with 
timezones or DST for the timestamp datatype. 

The timestamp implementation is not SQL99 conformant.

Andreas

Mon, 22 Jan 2001 12:37:16 +0100
Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.at>
timestamp (mis)behaviors
> Imho the behavior of timestamp code is somewhat awkward
> for dates that do not fit into a time_t (>2038 or < 1901).
> Times in the time_t range are displayed in local time including DST.
> Times outside that range are displayed in UTC. I would have expected
> UTC plus local offset not taking DST into account.

For times before 1901, any assumption about time zone behavior being
similar to the years around 2000 are likely to be completely wrong .
Time zones are a 1800's phenomemon (in the US, they came about when the
railroads were built across the continent) but the conventions did not
settle down until the 1900's. Not sure what the conventions are for
Austria, but I'd be interested in hearing about your regional history
for this.

In the US, the current conventions seem to be from post-WWII, and there
are a few years in the 1970's where DST was declared to be year-round.
So which years should we use as a model for appropriate 1800's behavior?
imho there is no reasonable assumption one can make about this without
having specific info.

Not sure why we should assume that the time zone behavior of 2050 is the
same as the years near 2000, especially since some years even recently
have variations in time zones.

> When setting datestyle to ISO I get a timezone offset, even when SQL99
> says timezone is only available if the column was defined as
> "timestamp with timezone". Dealing with the stupid mktime issue
> I think the standard intended to not involve any issues with
> timezones or DST for the timestamp datatype.
> The timestamp implementation is not SQL99 conformant.

Right. Eventually, we can separate out "timestamp" and "timestamp with
time zone", but for now they are the same. Not sure *when* we can make
them unique, since there is a backward-compatibility issue, but it would
be nice to do this before the 8.0 release.

                     - Thomas

Mon, 22 Jan 2001 15:43:23 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: timestamp (mis)behaviors