21

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

Clyde
  • 991
  • 2
  • 9
  • 17
  • 1
    `select TO_CHAR(TO_TIMESTAMP(1401432881222 / 1000), 'DD/MM/YYYY HH24:MI:SS') || '.' || (1401432881222%1000)` is good-style query? Returns `30/05/2014 06:54:41.222` – Clyde May 30 '14 at 08:32
  • `to_timestamp` already considers milliseconds, that's why it accepts floating point unix timestamps. If you want to store a real timestamp, just store what it returns. If you want to format it, use `MS` to display milliseconds. – pozs May 30 '14 at 08:37
  • But in my example (select TO_CHAR(TO_TIMESTAMP(1401432881230), 'DD/MM/YYYY HH24:MI:SS.MS')) I'm doing as you said. – Clyde May 30 '14 at 09:09

3 Answers3

56

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);
pozs
  • 34,608
  • 5
  • 57
  • 63
  • 6
    You can also write `TO_TIMESTAMP(1401432881230 / 1000.0)` instead of the explicit cast, if you want. – mlc Aug 02 '19 at 19:28
  • Will using double prevision introduce rounding errors? – KamilCuk Dec 05 '22 at 18:49
  • 1
    @KamilCuk it can, yes; but there's no `to_timestamp(numeric)` variant [currently](https://www.postgresql.org/docs/current/functions-datetime.html), the parameter will anyway be converted to `double precision` whatever one use. – pozs Dec 16 '22 at 20:11
1

This is how I convert ms to timestamp and keep ms instead seconds.The accepted answer will drop ms.

WITH ts AS (SELECT 1401432881230 AS ts)
SELECT to_timestamp(ts / 1000) + ((ts % 1000 ) || ' milliseconds') :: INTERVAL
FROM ts;

-- FOR ALTER COLUMN
ALTER TABLE  my_info
  ALTER COLUMN tstmp TYPE TIMESTAMP USING to_timestamp(tstmp / 1000) + ((tstmp % 1000) || ' milliseconds') :: INTERVAL;
wener
  • 7,191
  • 6
  • 54
  • 78
-2

Okay, I understood. My INSERT should looks like:

INSERT INTO events (timestamp) VALUES (to_timestamp(TO_CHAR(TO_TIMESTAMP(1401432881222 / 1000), 'YYYY-MM-DD HH24:MI:SS') || '.' || (1401432881222%1000), 'YYYY-MM-DD HH24:MI:SS.MS'))

I'm converting bigint-timestamp with milliseconds to text with required format ('YYYY-MM-DD HH24:MI:SS.MS') and pass it to to_timestamp function.

Clyde
  • 991
  • 2
  • 9
  • 17