Postgres Date Type Value
Want to retrieve a date type from a postgres table using libpq
PQexecParams() in binary mode (please humor me).
https://www.postgresql.org/docs/14/datatype-datetime.html says that a date
is 4 bytes (4713 BC to 5874897 AD). src/include/utils/date.h defines:
typedef int32 DateADT;
Something like this:
int32_t date_val = be32toh(*((uint32_t *) PQgetvalue(res, 0, 17)));
Gives date_val=1,466,004,328 for 2022-10-25.
Since 4713BC is the start of the Julian calendar thought it might be a
simple day count but the value is too large (2,459,878 days from 1 Jan
4713BC to 2022-10-25). So clearly not a day count. My simple maths gives
me 585.966... ticks per day.
How do I interpret the 4 bytes of postgresql 'date' value?
Michael Arnold <myk321@gmail.com> writes:
Something like this:
int32_t date_val = be32toh(*((uint32_t *) PQgetvalue(res, 0, 17)));
Gives date_val=1,466,004,328 for 2022-10-25.
The origin is 2000-01-01 (I'm pretty sure that's documented somewhere),
so the correct integer value for that date is 8333 by my math.
Looking at the bit-pattern for 1,466,004,328: 0x57617368, it seems
totally unrelated, more like ASCII text ("Wash") than anything else.
You sure you're reading the right column of the result?
regards, tom lane
On Sat, Dec 17, 2022 at 4:55 PM Michael Arnold <myk321@gmail.com> wrote:
How do I interpret the 4 bytes of postgresql 'date' value?
See date2j and j2date in datetime.c
timestamp.h has the relevant constant Tom refers to (POSTGRES_EPOCH_JDATE)
David J.
My bad - was referencing the wrong column with PQgetvalue(). With that
corrected, get 8332 for 2022-10-25. So date is an integer day count
from POSTGRES_EPOCH_JDATE
(2000-01-01).
Thanks for the help!
---------------
On Sun, Dec 18, 2022 at 8:24 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:
Show quoted text
On Sat, Dec 17, 2022 at 4:55 PM Michael Arnold <myk321@gmail.com> wrote:
How do I interpret the 4 bytes of postgresql 'date' value?
See date2j and j2date in datetime.c
timestamp.h has the relevant constant Tom refers to (POSTGRES_EPOCH_JDATE)
David J.