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).