PostgreSQL Timestamp Difference in Milliseconds-CodePudding

I am struggling getting the difference of two Timestamps in milliseconds.

My current approach was

SELECT * FROM EXTRACT(EPOCH FROM(NOW()::TIMESTAMP - '2022-10-23 16:34:53.227'))

which was described on a bad website as returning the difference in seconds but in fact returning the difference defined as an interval.

It so happens, that i am unable to multiply a factor of 1000 to get the value as milliseconds. I've then tried to cast the result as numeric or decimal, bigint, int8 and int but none these want let me use any calculation nor comparison.

Can someone tell me, what i am misunderstanding here?

CodePudding user response：

Take the epoch form both timestamp and subtract it

select
  extract('epoch' from NOW())  - extract('epoch' from '2022-10-23 16:34:53.227'::timestamp)

?column?
18464.130495

SELECT 1

fiddle

CodePudding user response：

You could try this:

SELECT EXTRACT(EPOCH FROM(NOW()::TIMESTAMP - '2022-10-23 16:34:53.227'))*1000 as DESIRED_COLUMN_NAME

You could also simplify to:

SELECT EXTRACT(EPOCH FROM(NOW() - '2022-10-23 16:34:53.227'))*1000 as DESIRED_COLUMN_NAME

You can check it with this too:

SELECT EXTRACT(EPOCH FROM(CAST('2022-10-23 17:45:12.000' AS TIMESTAMP)- '2022-10-23 17:45:00.000'))*1000 AS milliseconds

It outputs 12000

CodePudding user response：


SELECT 
  * 
FROM 
    EXTRACT(EPOCH FROM(NOW() - (NOW() - '0:0:0.256134'::interval)) * 1000) ;
  extract   
------------
 256.134000