Re: Binary timestamp with without timezone

On Thu, 16 Dec 2010 14:24:27 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> =?utf-8?q?Rados=C5=82aw_Smogura?= <rsmogura(at)softperience(dot)eu> writes:
>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> Thursday 16 December 2010 18:59:56
>>> =?utf-8?q?Rados=C5=82aw_Smogura?= <rsmogura(at)softperience(dot)eu>
>>> writes:
>>>> ... This timestamp must be properly encoded
>>>> depending if target is WITH TZ or not, but JDBC (and other
>>>> clients,
>>>> probably too) doesn't have any knowledge about target type when
>>>> statement is executed
>
>>> Seems like you need to fix *that*.
>
>> I don't say it's bad way to send timestamps in text mode. It's good
>> solution,
>> because timestamp without tz will silently ignore tz offset,
>> timestamp with tz
>> will use offset in calculations if it is there, if no it will use
>> server TZ.
>
> No, what I'm saying is that it's complete folly to be sending binary
> data for a value you don't know the exact type of.
I know something about value I want to send, but only this it should be
a timestamp. I don't know if it should be with or without tz.

> There are too many
> ways for that to fail, and too few ways for the backend to validate
> what
> you sent. Adding more possible ways to interpret a binary blob makes
> that problem worse, not better.

Official JDBC driver release use this technique to send timezone
timestamps, but for text mode; any timestamp is send as UNSPECIFIED. So
text mode driver can fail in this way too.

> What you need to fix is the inadequate type bookkeeping in JDBC. If
> you
> don't know the exact type of the value you're going to send, send it
> in
> text mode, where you have some reasonable hope of a mismatch being
> detected.

I know that this procedure isn't good as well as in text mode and in
binary mode, but gives any chance to do it better. In both cases we can
find examples when this behaviour will fail, but
In proposed solution I added (I hope in safe way) support for timezone
information, that is missing in comparison to binary protocol, which can
be useful.

Maybe better idea is to create new timestamptz type, that will fully
support TIME offsets, as well and most important, will give much more
client friendly casting to timestamp and timestamptz-s. I mean it should
be casted to timestamptz, as well to timestamp, but in last situation,
per field base ('2010-01-01 +1:00)::timestamp -> '2010-01-01'. It could
be better, because missing tz offset in current implementation can cause
problems with historical DST offset (many posts found).

Binary protocol will not have this disadvantage when reading, because
Java supports historical DST, and timestamptz is UTC based.

Regards,
Radek