Thread: What is the binary format for timestamps?
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
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
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).
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.
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
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