Thread: Binary encoding of TIMESTAMP WITH TIME ZONE

Binary encoding of TIMESTAMP WITH TIME ZONE

From

Joe Abbate

Date:

05 June 2020, 03:24:50

I'm dealing with an issue where a query uses 'today'::date to select one 
of a number of rows depending on the day modulo the number of rows. The 
intent is that different information will be shown starting after 
midnight local time.  The query runs as expected in psql and using psycopg2.

However, when using the same query using the Rust adapter the transition 
to a new row started showing up after midgnight GMT.  I opened an issue 
on Github (https://github.com/sfackler/rust-postgres/issues/608 ) and 
the maintainer claimed the Rust adapter *had* to initialize timezone to 
UTC in order to properly convert "to and from time datatypes".  I 
pointed out that the timezone offset is available in psql and psycopg2, 
but then he replied the binary encoding of timestamptz does *not* 
include the timezone offset.

He pointed me to the function timestamptz_send() which per the comments 
"converts timestamptz to binary format".  I found that the TimestampTz 
used in the function is a typedef for an int64, but since I'm not 
familiar with the code, I can't tell if timezone offset is embedded in 
there or not.

I'm hoping someone reading this can confirm (or deny) the above (or do I 
need to ask the -hackers list?).

Regards,

Joe

Re: Binary encoding of TIMESTAMP WITH TIME ZONE

From

Tom Lane

Date:

05 June 2020, 03:32:51

Joe Abbate <jma@freedomcircle.com> writes:
> However, when using the same query using the Rust adapter the transition 
> to a new row started showing up after midgnight GMT.  I opened an issue 
> on Github (https://github.com/sfackler/rust-postgres/issues/608 ) and 
> the maintainer claimed the Rust adapter *had* to initialize timezone to 
> UTC in order to properly convert "to and from time datatypes".  I 
> pointed out that the timezone offset is available in psql and psycopg2, 
> but then he replied the binary encoding of timestamptz does *not* 
> include the timezone offset.

Indeed it does not, just as the on-disk format for it does not.  The
representation is effectively always in UTC.  If you have some other
timezone setting selected, timestamptz_out rotates to that zone for
display purposes ... but the binary format doesn't.

            regards, tom lane

Re: Binary encoding of TIMESTAMP WITH TIME ZONE

From

"Peter J. Holzer"

Date:

05 June 2020, 18:30:57

On 2020-06-04 20:32:51 -0400, Tom Lane wrote:
> Joe Abbate <jma@freedomcircle.com> writes:
> > However, when using the same query using the Rust adapter the transition
> > to a new row started showing up after midgnight GMT.  I opened an issue
> > on Github (https://github.com/sfackler/rust-postgres/issues/608 ) and
> > the maintainer claimed the Rust adapter *had* to initialize timezone to
> > UTC in order to properly convert "to and from time datatypes".  I
> > pointed out that the timezone offset is available in psql and psycopg2,
> > but then he replied the binary encoding of timestamptz does *not*
> > include the timezone offset.
>
> Indeed it does not, just as the on-disk format for it does not.  The
> representation is effectively always in UTC.  If you have some other
> timezone setting selected, timestamptz_out rotates to that zone for
> display purposes ... but the binary format doesn't.

However, the explanation still sounds off. I'm not familiar with Rust,
but I wouild expect the Rust time type to be based on Unix time_t or
some variant of it (maybe milliseconds as in Java, or nanoseconds or a
different epoch). That also doesn't include a timezone, so conversion
should be straightforward and not require any timezone to be involved.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment

signature.asc