[HACKERS] Binary timestamp with without timezone

[Radosław Smogura]

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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers