CODEWITHSUNDEEP
sqlpostgresql
Alex Mantaut
Alex Mantaut

Reputation: 3875

How to get Epoch with Microsecond accuracy from a timestamp column in Postgres?

In Postgres I am trying to create a timestamp field with microsecond accuracy.

If I query for the timestamp in printable format, it displays the date with the exact microseconds, but if I query for the epoch, it only displays 5 digits...

Example:

CREATE TABLE date_test (datetime timestamp(6) WITH TIME ZONE);

INSERT INTO date_test VALUES(TO_TIMESTAMP(1525745241.879112));

SELECT datetime FROM date_test; //returns 2018-05-08 12:07:21.879112+10
SELECT EXTRACT(epoch FROM datetime) from date_test; //returns 1525745241.87911

What is the reason for the last digit to be lost? Is the query inherently converting to floating point and losing accuracy? Is there any easy way to store dates with microsecond precision in Postgres?

Upvotes: 3

Views: 2853

Answers (2)

Mate
Mate

Reputation: 5274

Maybe:

select  replace(extract (epoch from timestamp 'now')::text, '.', '');

select replace(extract (epoch from timestamp 'now')::text, '.', '')::bigint;

Or using maths...

select EXTRACT(epoch FROM now()) * 1000000

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51406

It's ugly, but on the top of a head:

t=# select extract(epoch from TO_TIMESTAMP(1525745241.879112) - '1000000000 seconds'::interval)::numeric(20,6) + 1000000000;
     ?column?
-------------------
 1525745241.879112
(1 row)

https://www.postgresql.org/docs/current/static/functions-datetime.html extract returns double precision which is 15 decimal digits precision, so microseconds just do not fit...

Upvotes: 5

Related Questions