Thread: What is the binary format for timestamps?

What is the binary format for timestamps?

From
Marvin Bellamy
Date:
I'm trying to read/write timestamps as binary data, but I'm getting 
garbage.  It looks like there are 8 bytes of timestamp data (if I read 
the headers correctly), which I had assumed was the time in millis from 
the PostgreSQL epoch, but my values are way off.  I wrote what I thought 
was valid data to my  timestamp columns, but my queries in the psql 
console show (epoch?) for each one.  When I read the binary content 
back, I'd expect to get 0s, but I get some non-zero values.

-- 
Marvin Keith Bellamy
Software Engineer
Innovision Corporation
913.438.3200



Re: What is the binary format for timestamps?

From
Tom Lane
Date:
Marvin Bellamy <marvin.bellamy@innovision.com> writes:
> I'm trying to read/write timestamps as binary data, but I'm getting 
> garbage.  It looks like there are 8 bytes of timestamp data (if I read 
> the headers correctly), which I had assumed was the time in millis from 
> the PostgreSQL epoch, but my values are way off.

Are you using integer datetimes?  It should be either a float8 measured
in seconds, or an int8 measured in microseconds since the epoch.
        regards, tom lane


Re: What is the binary format for timestamps?

From
ljb
Date:
marvin.bellamy@innovision.com wrote:
> I'm trying to read/write timestamps as binary data, but I'm getting 
> garbage.  It looks like there are 8 bytes of timestamp data (if I read 
> the headers correctly), which I had assumed was the time in millis from 
> the PostgreSQL epoch, but my values are way off.  I wrote what I thought 
> was valid data to my  timestamp columns, but my queries in the psql 
> console show (epoch?) for each one.  When I read the binary content 
> back, I'd expect to get 0s, but I get some non-zero values.

Don't use binary formats. But if you do:

A date is returned as a 4-byte big-endian integer representing the number
of days since POSTGRES_EPOCH_DATE.
A timestamp is returned as an 8-byte big-endian double precision number of
seconds since POSTGRES_EPOCH_DATE.
A time is returned as an 8-byte big-endian double precision number of
seconds since midnight.
POSTGRES_EPOCH_DATE is January 1, 2000 (2000-01-01).


Re: What is the binary format for timestamps?

From
Bruno Wolff III
Date:
On Fri, Apr 08, 2005 at 01:02:07 +0000, ljb <ljb220@mindspring.com> wrote:
> 
> A timestamp is returned as an 8-byte big-endian double precision number of
> seconds since POSTGRES_EPOCH_DATE.
> A time is returned as an 8-byte big-endian double precision number of
> seconds since midnight.
> POSTGRES_EPOCH_DATE is January 1, 2000 (2000-01-01).

I believe building with --enable-integer-datetimes will change this.


Re: What is the binary format for timestamps?

From
Marvin Bellamy
Date:
Tom Lane wrote:

>Marvin Bellamy <marvin.bellamy@innovision.com> writes:
>  
>
>>I'm trying to read/write timestamps as binary data, but I'm getting 
>>garbage.  It looks like there are 8 bytes of timestamp data (if I read 
>>the headers correctly), which I had assumed was the time in millis from 
>>the PostgreSQL epoch, but my values are way off.
>>    
>>
>
>Are you using integer datetimes?  It should be either a float8 measured
>in seconds, or an int8 measured in microseconds since the epoch.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>  
>
I've been treating the timestamp as an integer, not a double.  What's a 
good way to deal with double/float byte ordering when reading/writing 
data from Windows applications?

-- 
Marvin Keith Bellamy
Software Engineer
Innovision Corporation
913.438.3200



Re: What is the binary format for timestamps?

From
Tom Lane
Date:
Marvin Bellamy <marvin.bellamy@innovision.com> writes:
> I've been treating the timestamp as an integer, not a double.  What's a 
> good way to deal with double/float byte ordering when reading/writing 
> data from Windows applications?

Pretend it's an integer for long enough to byte-swap it the same way as
you'd do for an integer of the same size.  (IIRC this is exactly how the
backend does it, too.)
        regards, tom lane