pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedWhat is the binary format for timestamps?
6+ messages / 4 participants
[nested] [flat]
* What is the binary format for timestamps?
@ 2005-04-07 21:03 Marvin Bellamy <marvin.bellamy@innovision.com>
0 siblings, 2 replies; 6+ messages in thread
From: Marvin Bellamy @ 2005-04-07 21:03 UTC (permalink / raw)
To: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: What is the binary format for timestamps?
@ 2005-04-07 23:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marvin Bellamy <marvin.bellamy@innovision.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2005-04-07 23:52 UTC (permalink / raw)
To: Marvin Bellamy <marvin.bellamy@innovision.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: What is the binary format for timestamps?
@ 2005-04-08 01:02 ljb <ljb220@mindspring.com>
parent: Marvin Bellamy <marvin.bellamy@innovision.com>
1 sibling, 1 reply; 6+ messages in thread
From: ljb @ 2005-04-08 01:02 UTC (permalink / raw)
To: pgsql-interfaces
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).
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: What is the binary format for timestamps?
@ 2005-04-08 14:07 Bruno Wolff III <bruno@wolff.to>
parent: ljb <ljb220@mindspring.com>
0 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2005-04-08 14:07 UTC (permalink / raw)
To: ljb <ljb220@mindspring.com>; +Cc: pgsql-interfaces
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: What is the binary format for timestamps?
@ 2005-04-08 14:32 Marvin Bellamy <marvin.bellamy@innovision.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Marvin Bellamy @ 2005-04-08 14:32 UTC (permalink / raw)
To: ; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: What is the binary format for timestamps?
@ 2005-04-08 14:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marvin Bellamy <marvin.bellamy@innovision.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2005-04-08 14:46 UTC (permalink / raw)
To: Marvin Bellamy <marvin.bellamy@innovision.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-04-08 14:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-07 21:03 What is the binary format for timestamps? Marvin Bellamy <marvin.bellamy@innovision.com>
2005-04-07 23:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-08 14:32 ` Marvin Bellamy <marvin.bellamy@innovision.com>
2005-04-08 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-08 01:02 ` ljb <ljb220@mindspring.com>
2005-04-08 14:07 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox