Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch

Petr Fedorov <[email protected]>
Newsgroups gmane.comp.db.postgresql.bugs
Message-ID <[email protected]>
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).
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.