Reputation: 7270
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.
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
.
Upvotes: 7
Views: 8367
Reputation:
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
Upvotes: 23