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