How to store timestamp with milliseconds in PostgreSQL?

Question

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?

Answer 1

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