Обсуждение: Binary timestamp with without timezone
Hi,
I work on binary support for JDBC. I saw disadventage of TIMESTAMPS WITH /
WITHOUT TZ. Currently (in text mode) driver always sends date time string with
appended time offset, as UNSPECIFIED so backend can choose to use offset or
not. In binary mode I can only send 8 bytes timestamp without appended
timezone. 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 (so currently I send
timestamps as text).
I think about following patch (giving backward compatibility) on timestamp
(tz). Idea is as follows if we have additional two bytes it's TZ offset and
use this to convert received time to UTC. I wrote it in e-mail editor (sorry
:) no C IDE last time),
Datum
timestamptz_recv(PG_FUNCTION_ARGS)
{ StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
#ifdef NOT_USED Oid typelem = PG_GETARG_OID(1);
#endif int32 typmod = PG_GETARG_INT32(2); TimestampTz timestamp; int
tz; struct pg_tm tt, *tm = &tt; fsec_t fsec; char *tzn;
int16 tzOffset; //Zone offset with precision to minutes 12*60=720
#ifdef HAVE_INT64_TIMESTAMP timestamp = (TimestampTz) pq_getmsgint64(buf);
#else timestamp = (TimestampTz) pq_getmsgfloat8(buf);
#endif
+ if (buf->len == 10) { //We assume two last bytes is timezone offset
+ tzOffset = pg_copymsgbytes(buf, &tzOffset,2 /*sizeof(int16)*/);
+#ifdef HAVE_INT64_TIMESTAMP
+ timestamp -= ((int16) tzOffset) * 60 /* sek */ * USECS_PER_SEC;
+#else
+ timestamp -= (float8) (tzOffset * 60 /* sek */); //Good casting...?
+#endif
+ }
/* rangecheck: see if timestamptz_out would like it */ if (TIMESTAMP_NOT_FINITE(timestamp))
/*ok */ ; else if (timestamp2tm(timestamp, &tz, tm, &fsec, &tzn, NULL) != 0) ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), errmsg("timestamp
outof range")));
AdjustTimestampForTypmod(×tamp, typmod);
PG_RETURN_TIMESTAMPTZ(timestamp);
}
Will be this enaugh to allow to send TIMESTAMPS WITH(OUT) TZ as UNSPECIFIED?
Simillar should go for (ugly) time with timezone.
//Ofc, excelent behaviour will be if I could send values always with TZ and
//leave _proper_ casting to backend.
Kind regards,
Radosław Smogura
http://www.softperience.eu
Radosław Smogura <rsmogura@softperience.eu> writes:
> I work on binary support for JDBC. I saw disadventage of TIMESTAMPS WITH /
> WITHOUT TZ. Currently (in text mode) driver always sends date time string with
> appended time offset, as UNSPECIFIED so backend can choose to use offset or
> not. In binary mode I can only send 8 bytes timestamp without appended
> timezone. 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 think about following patch (giving backward compatibility) on timestamp
> (tz). Idea is as follows if we have additional two bytes it's TZ offset and
> use this to convert received time to UTC. I wrote it in e-mail editor (sorry
> :) no C IDE last time),
This is not a terribly good idea, and even if it was, how will you use
it from a client that doesn't know which data type is really in use?
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> Thursday 16 December 2010 18:59:56 > Radosław Smogura <rsmogura@softperience.eu> writes: > > I work on binary support for JDBC. I saw disadventage of TIMESTAMPS WITH > > / WITHOUT TZ. Currently (in text mode) driver always sends date time > > string with appended time offset, as UNSPECIFIED so backend can choose > > to use offset or not. In binary mode I can only send 8 bytes timestamp > > without appended timezone. 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. > > I think about following patch (giving backward compatibility) on > > timestamp (tz). Idea is as follows if we have additional two bytes it's > > TZ offset and use this to convert received time to UTC. I wrote it in > > e-mail editor (sorry > > > > :) no C IDE last time), > > This is not a terribly good idea, and even if it was, how will you use > it from a client that doesn't know which data type is really in use? Binary protocol disallow to send timezone offset, as text mode allow (lack of information). I would like to send this in same behavior as text mode does: send local time with two bytes of client tz as OID unspecified. One more instead of pq_copymsgbtes better will be tzOffset = pq_getint(buf, sizeof(int16)); Kind regards, Radosław Smogura http://www.softperience.eu
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. 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.
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.
regards, tom lane
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
On Mon, Dec 20, 2010 at 6:29 AM, Radosław Smogura <rsmogura@softperience.eu> wrote: > > 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 should be a > timestamp. I don't know if it should be with or without tz. That's your problem right there, full stop. If you don't know if your time is with or without tz, how can you possibly expect the server to know? Either send without tz, or grab the time zone from the local environment and convert to binary timezone tz. Your issue has absolutely nothing to do with which protocol you are using. merlin