Re: Binary timestamp with without timezone

On Thu, 16 Dec 2010 14:24:27 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Radosław Smogura <rsmogura@softperience.eu> writes:
>> Tom Lane <tgl@sss.pgh.pa.us> Thursday 16 December 2010 18:59:56
>>> Radosław Smogura <rsmogura@softperience.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
shouldbe 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
asUNSPECIFIED. 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.
Inboth cases we can find examples when this behaviour will fail, butIn proposed solution I added (I hope in safe way)
supportfor 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,
willgive much more client friendly casting to timestamp and timestamptz-s. I mean it should be casted to timestamptz,
aswell 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
UTCbased.
 
Regards,Radek