Trevor Talbot wrote:
>, what I meant at least (not sure if others meant it), is
> storing the value in the timezone it was entered, along with what zone
> that was. That makes the value stable with respect to the zone it
> belongs to, instead of being stable with respect to UTC. When DST
> rules change, the value is in effect "reinterpreted" as if it were
> input using the new rules. To me that's also what the name of the
> type suggests it does.
I would argue that this isn't necessarily more helpful than what we have.
Many of us work in an in an international environment, and DST rules (and,
I'm sure you all remember the Venezuela case, time zones) change, and
at different instances in time.
To reiterate what the SQL standard says, a WITH TIMEZONE element should
have information on the original time zone it was stored as, but only in
the form of an offset from UTC in hours and minutes. SQL has no
notion of time zone labels, so if we decide to store these, we wouldn't
be any closer to SQL compliancy. An interesting observation is that,
as far as I can tell, the original time zone is only applied when casting
the element to a string. Apart from that, it's not used.
I would suggest that the WITH TIMEZONE elements are converted to UTC when
inserted into the database. Since all operations on it are based on
its UTC form, it's most efficient ( I believe) if the data is stored that
way. To be compliant, an offset (hours and minutes) to the time zone
that was used when storing the time should be stored as well.
--Magne