0

Want to retrieve a date type from a postgres table using liqpq 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;

But obviously given the supported date range it's not a normal int. Something like this:

int32_t haha = be32toh(*((uint32_t *) PQgetvalue(res, 0, 17)));

Gives haha=1466004328 for 2022-10-25.

Which is clearly not a day count and since its not a ratio of 86,400 is also not seconds since an epoch. Number is also too small to be microseconds.

How do I interpret the 4 bytes of postgresql 'date' data?

Added Later: This question contains an error - PQgetvalue() references column 17 (a text value) instead of column 18 (a date value) - with that corrected haha=8332

myk
  • 708
  • 2
  • 8
  • 20
  • 4713 BC is the basis for [Julian day](https://en.wikipedia.org/wiki/Julian_day) numbers, so that's clearly got something to do with it. – Steve Summit Dec 17 '22 at 00:51
  • Ah interesting...2459878 days from 1 Jan 4713BC to 2022-10-25, gives gives 585.966... ticks per day. Clearly we're still missing something. – myk Dec 17 '22 at 05:24

1 Answers1

0

Date is an integer day count from POSTGRES_EPOCH_JDATE (2000-01-01).

myk
  • 708
  • 2
  • 8
  • 20