Postgres Pro
Downloads
Home   >   mailing lists

Thread: Binary Format of Timestamp

Binary Format of Timestamp

From
Joe Joe
Date:
Hi all,

I have a timestamp in a database that is beeing
returned in pgAdmin as "2005-06-21 11:21:47", exactly
as entered. When i retrieve the field via PQgetvalue i
get 8 bytes worth of data that i can not interpret.
ColumnType is TIMESTAMPOID, and the field content is
binary, size is 8 bytes, also all as expected.
I expect the timestamp format to be a float8 type
containing the seconds elapsed sind the 1.1.2000. This
should roughly be something around 172668107, give and
take a few timezone adjustments. What i get back from
PQgetvalue is the following array of bytes:

0: 0x00
1: 0x00
2: 0x9d
3: 0x0a
4: 0x6d
5: 0x2e
6: 0x88
7: 0xc0

Handling these bytes like a double (i.e adjusting them
for network byteorder), returns a value of
8.530937980114e-310#DEN.
But no matter how i order these bytes, i cannot make
sense of them.

Platform is Win32, Postgres was installed from binary
distribution. Does anyone have a hint ?

Many thanks in advance,

Joe








____________________________________________________
Yahoo! Sports
Rekindle the Rivalries. Sign up for Fantasy Football
http://football.fantasysports.yahoo.com

Re: Binary Format of Timestamp

From
Tom Lane
Date:
Joe Joe <fondwey@yahoo.com> writes:
> I expect the timestamp format to be a float8 type
> containing the seconds elapsed sind the 1.1.2000. This
> should roughly be something around 172668107, give and
> take a few timezone adjustments. What i get back from
> PQgetvalue is the following array of bytes:

> 0: 0x00
> 1: 0x00
> 2: 0x9d
> 3: 0x0a
> 4: 0x6d
> 5: 0x2e
> 6: 0x88
> 7: 0xc0

Looks to me like you are talking to a server that's using the
integer_datetimes build option, in which the timestamp format
is int8 not double, and the unit is microseconds.

regression=# select x'9d0a6d2e88c0'::int8;
      int8
-----------------
 172668107000000
(1 row)

            regards, tom lane

Re: Binary Format of Timestamp

From
Michael Fuhr
Date:
On Tue, Jun 21, 2005 at 06:28:05AM -0700, Joe Joe wrote:
>
> I have a timestamp in a database that is beeing
> returned in pgAdmin as "2005-06-21 11:21:47", exactly
> as entered. When i retrieve the field via PQgetvalue i
> get 8 bytes worth of data that i can not interpret.
> ColumnType is TIMESTAMPOID, and the field content is
> binary, size is 8 bytes, also all as expected.
> I expect the timestamp format to be a float8 type
> containing the seconds elapsed sind the 1.1.2000. This
> should roughly be something around 172668107, give and
> take a few timezone adjustments. What i get back from
> PQgetvalue is the following array of bytes:
>
> 0: 0x00
> 1: 0x00
> 2: 0x9d
> 3: 0x0a
> 4: 0x6d
> 5: 0x2e
> 6: 0x88
> 7: 0xc0
>
> Handling these bytes like a double (i.e adjusting them
> for network byteorder), returns a value of
> 8.530937980114e-310#DEN.
> But no matter how i order these bytes, i cannot make
> sense of them.

You're assuming that the value is a double, but what's your
integer_datetimes setting?  I notice that the above bytes unpacked
as a 64-bit integer give exactly the number you guessed, multiplied
by one million:

% perl -le 'print unpack("q", pack("C*", 0xc0, 0x88, 0x2e, 0x6d, 0x0a, 0x9d, 0x00, 0x00))'
172668107000000

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/