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

Поиск
Список
Период
Сортировка
От Petr Fedorov
Тема Since '2001-09-09 01:46:40'::timestamp microseconds are lost whenextracting epoch
Дата
Msg-id 42b73d2d-da12-ba9f-570a-420e0cce19d9@phystech.edu
обсуждение исходный текст
Ответы Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost whenextracting epoch  (Thomas Munro <thomas.munro@gmail.com>)
Список pgsql-bugs
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).












В списке pgsql-bugs по дате отправления:

Предыдущее
От: EffiSYS / Martin Querleu
Дата:
Сообщение: Strange query planner behavior
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: Strange query planner behavior