how to get time difference in seconds/milliseconds in postgre

Sorry..if it is for time difference, pls check the below article

http://www.experts-exchange.com/Programming/Languages/Scripting/Perl/Q_21101974.html

Since I have to use in my sql statements, that solution should be using simple SQL (including CASE and built-in functions)

See below sql in sql server, I want relevant query in postgre using its built-in functions

If there is no simple way, I have a workaround where I will extract hour/minute/seconds/ms and convert them to integer using simple logic like below

to convert 1 day in seconds

24*60*60
and similarly to convert it to milliseconds
24*60*60*1000

but the code becomes clumsy anyway thats the way if there is no go

select datediff(s,d1,d2) from (
SELECT '2009-04-01 23:59:59.950' D1,'2009-03-31 11:29:07.450' D2 -- 131452 seconds time diff
UNION ALL
SELECT '2009-06-29 16:12:38.880' D1,'2009-06-29 14:39:20.340' D2 -- 5598 seconds time diff
)x
--
select datediff(ms,d1,d2) from (
SELECT '2009-04-01 23:59:59.950' D1,'2009-03-31 11:29:07.450' D2 -- 131452500 millisecs time diff
UNION ALL
SELECT '2009-06-29 16:12:38.880' D1,'2009-06-29 14:39:20.340' D2 -- 5598540 millisecds time diff
)x

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

Ok, I got half-solultion, but it gives only seconds


My problem is, in some of the places I need compare time difference in milliseconds

select d1,d2,interval (age(d1,d2)),
extract(epoch from interval (age(d1,d2)))
from (
SELECT '2009-04-01 23:59:59.950000' D1,'2009-03-31 11:29:07.450000' D2
UNION ALL
SELECT '2009-06-29 16:12:38.880000' D1,'2009-06-29 14:39:20.340000' D2
UNION ALL
SELECT '2009-06-29 16:12:38.10' D1,'2009-06-28 16:11:37.00' D2
)x
--
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

testdb=> select d1,d2,age(d1,d2),
extract(epoch from age(d1,d2)) * 1000 as milliseconds
from (
SELECT '2009-04-01 23:59:59.950000'::timestamp as D1,'2009-03-31 11:29:07.450000'::timestamp as D2
UNION ALL
SELECT '2009-06-29 16:12:38.880000'::timestamp as D1,'2009-06-29 14:39:20.340000'::timestamp as D2
UNION ALL
SELECT '2009-06-29 16:12:38.10'::timestamp as D1,'2009-06-28 16:11:37.00'::timestamp as D2
) as x;
           d1           |           d2           |        age        | milliseconds
------------------------+------------------------+-------------------+--------------
 2009-04-01 23:59:59.95 | 2009-03-31 11:29:07.45 | 1 day 12:30:52.50 |    131452500
 2009-06-29 16:12:38.88 | 2009-06-29 14:39:20.34 | 01:33:18.54       |      5598540
 2009-06-29 16:12:38.10 | 2009-06-28 16:11:37    | 1 day 00:01:01.10 |     86461100
(3 rows)

earthman2:

Its simply converting seconds to milleseconds but my requirement is to get the difference in milleseconds

to get more clarity see below two timestamps which has 10 milleseconds timestamps difference

SELECT '2009-06-29 16:12:38.10' as D1,'2009-06-29 16:12:38.00' as D2

when we extract it it gives 0 seconds difference and multiplying it with 1000 gives 0 milliseconds which is wrong

SELECT round(1000 * extract( epoch from '2009-06-29 16:12:38.10'::timestamp -'2009-06-29
16:12:38.00'::timestamp )) as milliseconds;

Doest it give difference in millisecs in postgre? What is the result it? Why I am asking is because I am running in Netezza which is  actually uses postgre db engine. Age() is giving showing 1 millisec difference in Interval but extract function using above method is still showing 0 in netezza