Menu
I'm trying to store in timestamp with timezone field my value. It is in milliseconds from 1970.
select TO_CHAR(TO_TIMESTAMP(1401432881230), 'DD/MM/YYYY HH24:MI:SS.MS')
Expected 30/5/2014 11:29:42 10:54:41.230, but get
22/08/46379 23:27:02.000
723
Postgres DATE data type Postgres uses the DATE data type for storing different dates in YYYY-MM-DD format. It uses 4 bytes for storing a date value in a column. You can design a Postgres table with a DATE column and use the keyword DEFAULT CURRENT_DATE to use the current system date as the default value in this column.
To cast a timestamp value to the timestamptz data type directly as it is always an excellent approach, as shown in the below command: SELECT timezone('America/New_York','2016-06-01 00:00'::timestamptz);
PostgreSQL stores the timestamptz in UTC value. When you insert a value into a timestamptz column, PostgreSQL converts the timestamptz value into a UTC value and stores the UTC value in the table.
Unix timestamps measures time with seconds, and not milliseconds (almost everywhere, in PostgreSQL too).
Therefore you need to call
SELECT TO_TIMESTAMP(1401432881230 / 1000);
If you want to preserve milliseconds, call with double precision:
SELECT TO_TIMESTAMP(1401432881230::double precision / 1000);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
