From: Petr Fedorov <petr.fedorov@phystech.edu>
To: pgsql-bugs@postgresql.org
Subject: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
Date: Sat, 30 Nov 2019 12:28:18 +0300
Message-ID: <42b73d2d-da12-ba9f-570a-420e0cce19d9@phystech.edu> (raw)
Hello,
Steps to reproduce:
select extract(epoch from '2001-09-09 01:46:39.999999'::timestamp)
returns 999999999.999999 as expected
while
select extract(epoch from '2001-09-09 01:46:40.000021'::timestamp)
returns 1000000000.00002 - 1 microsecond is truncated.
Obviously, it is due to the fact that extract epoch returns double
precision which in turn has 15 decimal digits precision.
While there is a pretty simple workaround in C, that returns
microseconds since Unix epoch:
Datum
to_microseconds(PG_FUNCTION_ARGS) {
Timestamp arg = PG_GETARG_TIMESTAMP(0)+946684800000000;
PG_RETURN_INT64(arg);
}
I was not able to find the other way of doing that (i.e. without C
function).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: petr.fedorov@phystech.edu
Subject: Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
In-Reply-To: <42b73d2d-da12-ba9f-570a-420e0cce19d9@phystech.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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