Binary timestamp with without timezone

From: Radosław Smogura <rsmogura(at)softperience(dot)eu>
To: PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Binary timestamp with without timezone
Date: 2010-12-16 17:55:45
Message-ID: 201012161855.45575.rsmogura@softperience.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(&timestamp, 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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2010-12-16 17:58:32 Re: directory archive format for pg_dump
Previous Message Heikki Linnakangas 2010-12-16 17:48:06 Re: directory archive format for pg_dump