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

From: Petr Fedorov <petr(dot)fedorov(at)phystech(dot)edu>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
Date: 2019-11-30 09:28:18
Message-ID: 42b73d2d-da12-ba9f-570a-420e0cce19d9@phystech.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Pavel Stehule 2019-11-30 09:55:26 Re: Strange query planner behavior
Previous Message EffiSYS / Martin Querleu 2019-11-30 08:31:08 Strange query planner behavior

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2019-11-30 11:28:55 Re: pgbench -i progress output on terminal
Previous Message Fabien COELHO 2019-11-30 07:27:51 Re: pgbench -i progress output on terminal