Re: Timezone Inconsistancies - Mailing list pgsql-hackers

From Thomas Lockhart
Subject Re: Timezone Inconsistancies
Date
Msg-id 3C7B06D1.A1F152B5@fourpalms.org
Whole thread Raw
In response to Timezone Inconsistancies  ("Arguile" <arguile@lucentstudios.com>)
Responses Re: Timezone Inconsistancies  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
...
> I understand why it's not implicitly casting, but if tz can be ignored in
> one it should exhibit the same behavoir for all.

Yes, the explicit cast is missing; thanks for pointing that out. You can
define one as follows:

thomas=# create function "time"(timestamp)
thomas-# returns time as 
thomas-# 'select cast(cast($1 as timestamp without time zone) as time);'
thomas-# language 'sql';

thomas=# select cast(now() as time);      now       
-----------------19:43:48.736863

> 2) Another inconsistancy is timestamp default to having a timezone, while
> time does not, and dates not even having a date with timezone option:
> ::time      != ::timetz
> ::timestamp == ::timestamptz
> ::date      ??

The SQL99 standard says that timestamp should default to having no time
zone. But historically PgSQL had a "zoneful timestamp", and only in the
latest version do we have both. So the default was kept as-is for this
release to help with upgrades from previous versions. The default will
likely change for 7.3.

SQL9x calls for timestamp with and without timezone, time with and
without time zone, and date with no time zone. istm that time with time
zone is useless, but we offer it to provide compatibility with
standards. date with time zone would likely be pretty useless also. We
try to conform to the standard in most cases, with some exceptions due
to standards brain damage. I usually recommend using timestamp with time
zone for most things.

> Dates are just as effected by time zones as time is. The only way to cast to
> timestamp without tz is CAST( ... without time zone) as far as I can tell;
> as ::timestamp(p) doesn't work nor the w/ w/o tz in that style of casting.

Right. The double-colon syntax is an ancient PgSQL-ism. Use cast()
instead to get the greatest variety of syntax in coersions.

> Among other fun new timestamp changes ;)

Enjoy ;)
                   - Thomas


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: More time zones
Next
From: Tom Lane
Date:
Subject: Re: Timezone Inconsistancies