CODEWITHSUNDEEP
databasepostgresqltime
XmikeX
XmikeX

Reputation: 381

How to store timestamp with milliseconds in PostgreSQL?

I need to store timestamps in the format yyyy-mm-dd hh:mm:ss.SSS (SSS standing for milliseconds) in a PostgreSQL database. Surely it would work as a text/or varchar, but I need to "work" with the timestamps (e.g. filter all instances prior a certain timestamp - considering milliseconds). How would I do that in PostgreSQL?

Upvotes: 37

Views: 61371

Answers (1)

Alex Mantaut
Alex Mantaut

Reputation: 3885

I know this is an old question, but I had a similar problem.

You can use the timestamp(n) field, with n equals the number of digits of precision to store on the date (timestamp(3) would give you millisecond precision)

Example

CREATE TABLE date_test (datetime timestamp(3) with time zone);

insert into date_test values(to_timestamp(1525745241.879));

select EXTRACT(epoch FROM datetime) from date_test;

Note: in current versions of postgres timestamp is stored as an integer, so it does not incur in floating point rounding issues

Upvotes: 55

Related Questions