Re: Timestamp without Timezone and differing client / server tzs

Oliver Jowett wrote:
> Ken Johanson wrote:
>
>>
>> The servers and their schema (use-of ts w/o tz are not under my
>> control; adding with-timezone would likely break apps that transmit
>> date values with offset already normalized)
>
> This is the root of your problems, you're trying to use WITHOUT TIMEZONE
> to represent an instant in time which only makes sense when you're using
> the same timezone absolutely everywhere - and you're not. (Basically,
> you've got an implicit extra bit of data floating around - the server
> timezone - that's not correctly tied to your column data)

That's fine to me. UTC (in my case) is a well defined and agreed upon
TZ. IT works perfectly without having any conveyance of TZ since
everyone agrees to use UTC (or any other stable TZ spec).

>
> But if you can't change the schema, you can certainly work around it in
> your application:
>

I think that depends on how many if any layer of middleware are opaque
vs extensible. If I'm ostensibly only allow to pass in a number-wrapped
object java.util.Date or Timestamp then I could do as you suggest but by
offsetting the REAL / internal numeric value. Cringe. But I may have to
resort to this.

>> Just to pass-in a timestamp, and which ever database it is sent to, be
>> stored with its real atomic (integer) value (ie string conversion
>> normalized to what db uses).
>>
>> Simplified:
>>
>> Timestamp NOW = new Timestamp(System.currentTimeMillis());
>> for (int i=0; i<SERVERS.length; i++)
>> {
>> Connection con = ....//SERVERS[i] etc; each server exists in different
>> timezone, and datetime/timestamps cols do not store zone.
>> PreparedStatement ps = con.prepareStatement("INSERT INTO tbl
>> (lastModified) VALUES (?)");
>> ps.setTimestamp(1, NOW));
>> ps.executeUpdate();
>> }
>
> Ok, so essentially you want to pass a timestamp-with-timezone value to
> the server, then store it as timestamp-without-timezone, using the
> server's timezone to do the cast, right?
>
> What is biting you here is the server-side inference of the datatype of
> your parameter. To support both WITH TIMEZONE and WITHOUT TIMEZONE via
> setTimestamp(), the driver has to pass the parameter with an unknown
> type and let the server infer the actual type.
>

Well made point. But I'm fine with the driver implicitly treating ANY TS
object as convertible on a configured-as-such connection, even if its
destined for a TS w/ TZ. In this case because I know there are only
TZless storage.

> For example if a client sets a timestamp of "05:00 +1000" (either via an
> explicit Calendar, or because their default timezone is +1000) to insert
> into a WITHOUT TIMEZONE column, the only sensible result is to insert
> that as "05:00" regardless of the server's timezone setting. The client
> sees the value as 05:00, so the only sensible thing the driver can do is
> to insert it as 05:00.
>
> The driver takes advantage of the fact that literals interpreted as
> WITHOUT TIMEZONE completely ignore the timezone specification in this
> case (and when inserting into a WITHOUT TIMEZONE column, the type of an
> unknown-type parameter is inferred to be WITHOUT TIMEZONE).

Maybe that part/all the answer: if overriding TZ on connection then dont
pass redundant TZ information in protocol- just pass tz-less value
normalized/offset to the server/implicit TZ. I'm pretty sure another
driver does this too.

>
> If the driver passed that literal as a WITH TIMEZONE type, it'd first
> get converted to a seconds-since-epoch value (respecting the timezone
> specification) and then converted to WITHOUT TIMEZONE using the server's
> timezone. If the server's timezone is not +1000, you get something other
> than "05:00" inserted, which isn't what the client asked for.
>
> So the driver deliberately doesn't do that, and in fact jumps through
> some hoops to make sure it doesn't happen.
>
> However, "interpret as WITH TIMEZONE then cast" is exactly the behaviour
> you want in this case. You can get that behaviour via some explicit
> casting, something like this:
>
> CAST((CAST ? AS TIMESTAMP WITH TIMEZONE) AS TIMESTAMP WITHOUT TIMEZONE)
>
> (modulo syntax errors; not sure if the outermost CAST is needed, off the
> top of my head)
>
> -O
>

Honestly that beyond my comprehension, though part of me thinks it may
be over complicating things, if the driver instead just adds the offset
and doesn't pass zone in the string? In UTC's case anyway.

Thank you for some good ideas!

Ken