pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres Date Type Value
4+ messages / 3 participants
[nested] [flat]
* Postgres Date Type Value
@ 2022-12-17 23:55 Michael Arnold <myk321@gmail.com>
2022-12-18 00:20 ` Re: Postgres Date Type Value Tom Lane <tgl@sss.pgh.pa.us>
2022-12-18 00:24 ` Re: Postgres Date Type Value David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Michael Arnold @ 2022-12-17 23:55 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres Date Type Value
2022-12-17 23:55 Postgres Date Type Value Michael Arnold <myk321@gmail.com>
@ 2022-12-18 00:20 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2022-12-18 00:20 UTC (permalink / raw)
To: Michael Arnold <myk321@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres Date Type Value
2022-12-17 23:55 Postgres Date Type Value Michael Arnold <myk321@gmail.com>
@ 2022-12-18 00:24 ` David G. Johnston <david.g.johnston@gmail.com>
2022-12-18 01:15 ` Re: Postgres Date Type Value Michael Arnold <myk321@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2022-12-18 00:24 UTC (permalink / raw)
To: Michael Arnold <myk321@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres Date Type Value
2022-12-17 23:55 Postgres Date Type Value Michael Arnold <myk321@gmail.com>
2022-12-18 00:24 ` Re: Postgres Date Type Value David G. Johnston <david.g.johnston@gmail.com>
@ 2022-12-18 01:15 ` Michael Arnold <myk321@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Arnold @ 2022-12-18 01:15 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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.
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-12-18 01:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-17 23:55 Postgres Date Type Value Michael Arnold <myk321@gmail.com>
2022-12-18 00:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-12-18 00:24 ` David G. Johnston <david.g.johnston@gmail.com>
2022-12-18 01:15 ` Michael Arnold <myk321@gmail.com>
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