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