PostgreSQL 9.5: Convert intervals INTO milliseconds

Question

I have the following two dates which is in the form of varchar(50).

Date1: 2016-11-24 12:38:29.123

Date2: 2016-04-22 11:44:55

First date that is Date1 contains milliseconds and Date2 contains till seconds. So i did casting to get up to seconds from both dates.

Using:

SELECT ('2016-11-24 12:38:29.123'::timestamp(0) - '2016-04-22 11:44:55'::timestamp(0));

Output:

216 days 00:53:34

Note: Now i want to convert 216 days 00:53:34 into milliseconds.

My try:

SELECT EXTRACT(MILLISECONDS FROM(CAST('2016-11-24 12:38:29.123' AS timestamp(0)) - CAST('2016-04-22 11:44:55' AS timestamp(0))));

Output:

34000

Problem: 34000 is not the exact milliseconds of 216 days 00:53:34.

Answer 1

You are looking for epoch which returns the number of seconds stored in the interval.

SELECT extract(epoch from timestamp '2016-11-24 12:38:29.123' - timestamp '2016-04-22 11:44:55');

returns: 18665614