Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgres Date Type Value

Postgres Date Type Value

From
Michael Arnold
Date:

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?

Re: Postgres Date Type Value

From
Tom Lane
Date:
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

Re: Postgres Date Type Value

From
"David G. Johnston"
Date:
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.

Re: Postgres Date Type Value

From
Michael Arnold
Date:
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:
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.