Re: Date with time zone

On Saturday 28 November 2009 3:43:02 am Eduardo Piombino wrote:
> Hello list, this is my first msg here. I hope this is the correct place for
> this subject, I couldn't find any more specific list for this.
>
> This thought had been bugging me for some time now and I thought it was
> time to share it with you pg gurus.
>
> Why in god's sake is there not a "date with time zone" data type?
> I mean, in the same manner that every country does not have the same time
> (due to the time zone they are in), they also don't have to be in the same
> day (for the same reason). Maybe it's January 10th in one place, and
> January 11st a couple of time zones ahead.
>
> So, in the same way that a simple "time" data type is not enough for
> precise time specification on multi time zone setups, a simple "date" data
> type is also not enough for a precise date specification in those setups.
>
> Of course you can always set another column, specifying that that "date"
> actually corresponds to a specific timezone, but in the same manner that u
> dont need an extra column for time values (cause u have the "time with time
> zone"), you shouldn't be needing to create another one to host the time
> zone for the date.
>
> I don't know, am I crazy?
> Thanks a lot.
>
> Eduardo.

The best explanation I can offer comes from the manual.

http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html

" PostgreSQL endeavors to be compatible with the SQL standard definitions for
typical usage. However, the SQL standard has an odd mix of date and time types
and capabilities. Two obvious problems are:

*

Although the date type cannot have an associated time zone, the time type
can. Time zones in the real world have little meaning unless associated with a
date as well as a time, since the offset can vary through the year with
daylight-saving time boundaries.
*

The default time zone is specified as a constant numeric offset from UTC.
It is therefore impossible to adapt to daylight-saving time when doing
date/time arithmetic across DST boundaries.

To address these difficulties, we recommend using date/time types that contain
both date and time when using time zones. We do not recommend using the type
time with time zone (though it is supported by PostgreSQL for legacy
applications and for compliance with the SQL standard). PostgreSQL assumes your
local time zone for any type containing only date or time. "

--
Adrian Klaver
aklaver(at)comcast(dot)net