Re: Date/Time atributes and binary cursors

Gonçalo Marrafa wrote:
> I'm trying to read a timestamp without timezone attribute, using binary
> cursors. I can read the data but i don't know what to do with it. I would
> like to convert it to a time_t or tm struct. How can i do this?
>
> I'm using postgresql 7.4 on Debian sid.
>
> Here's a snippet of the code i'm using:
>
> ...
> #include <postgresql/pgtypes_timestamp.h>
> ...
> timestamp *ts; ts =3D (timestamp *) PQgetvalue(res, i, f_num);
>
>
> I would like to use date attributes too.

Disclaimer: relying on binary cursor data format is probably a bad idea.

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).

Note that binary cursor results are in network data order (big-endian)
starting with PostgreSQL-7.4 (versus native server order pre-7.4). This
means they need to be byte-swapped if your client runs on an Intel-type
little-endian system.