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 out of 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
=?utf-8?q?Rados=C5=82aw_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
=?utf-8?q?Rados=C5=82aw_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 executedSeems 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
=?utf-8?q?Rados=C5=82aw_Smogura?= <rsmogura@softperience.eu> writes:
Tom Lane <tgl@sss.pgh.pa.us> Thursday 16 December 2010 18:59:56
=?utf-8?q?Rados=C5=82aw_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:
=?utf-8?q?Rados=C5=82aw_Smogura?= <rsmogura@softperience.eu> writes:
Tom Lane <tgl@sss.pgh.pa.us> Thursday 16 December 2010 18:59:56
=?utf-8?q?Rados=C5=82aw_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 executedSeems 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
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:
=?utf-8?q?Rados=C5=82aw_Smogura?= <rsmogura@softperience.eu> writes:
Tom Lane <tgl@sss.pgh.pa.us> Thursday 16 December 2010 18:59:56
=?utf-8?q?Rados=C5=82aw_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 executedSeems 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