Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> The timestamp and the timezone in which that timestamp was entered
> are two separate pieces of data and *ought* to be in two separate
> fields.
So, if you're grabbing a timestamp and the time zone for it, how do
you ensure you've done that atomically if you're at the boundary of
a DST change? The difficulty of grabbing both such that they are
guaranteed to correspond suggests to me that they really form a
single logical value.
> For one thing, the question of "what timezone was this entered in"
> is an application-specific question, since you have three
> different potential timezones:
>
> * the actual client timezone
> * the actual server timezone
> * the application timezone if the application has configurable
> timezones
>
> In a builtin data type, which of those three would you pick?
Well clearly the only one *PostgreSQL* would "pick" is one assigned
within the database server; otherwise, for a data type like this the
value coming over the wire should specify it.
If I want the client side value (in Java) it's easy enough to get
such a value. "new GregorianCalendar()" is described thusly:
| Constructs a default GregorianCalendar using the current time in
| the default time zone with the default locale.
How does Java assign those defaults? Why should PostgreSQL care?
It's got the means to do so for itself. The point is, people can
easily establish such a value on the client side; why not on the
server side?
> Only the application knows [whether it should pick the value or
> let the database pick it].
When are things otherwise? Obviously the application will assign it
or choose to let the server assign it (if that's the right thing).
> Additionally, if you have your timestamp-with-original-timezone
> data type, then you're going to need to recode every single
> timestamp-handling function and operator to handle the new type.
Why? I think you'd want to add some *new* casts and operators for
the new data type; I don't see why any existing ones would need to
be modified.
-Kevin