Skip site navigation (1) Skip section navigation (2)

Re: What is the binary format for timestamps?

From: ljb <ljb220(at)mindspring(dot)com>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: What is the binary format for timestamps?
Date: 2005-04-08 01:02:07
Message-ID: d34l6e$284h$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-interfaces
marvin(dot)bellamy(at)innovision(dot)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).

In response to

Responses

pgsql-interfaces by date

Next:From: Greg Sabino MullaneDate: 2005-04-08 01:43:32
Subject: DBD::Pg version 1.41 released
Previous:From: Tom LaneDate: 2005-04-07 23:52:32
Subject: Re: What is the binary format for timestamps?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group