-1

Hi i have same query in oracle 

select ( TRUNC(b.pub_ts, 'MI') - DATE '1970-01-01')* 24 * 60 * 60, 
             TRUNC(b.pub_ts, 'MI') - DATE '1970-01-01'
                from abctable b  where b.blurb_id=344143

the output of query in oracle is this

enter image description here

and i have converted query in postgres and i want same result how can i get that , i have created the query for postgres but it is giveing interval not decimal output like oracle

 select  ( date_trunc('minute', b.pub_ts) - DATE '1970-01-01')* 24 * 60 * 60,
                              date_trunc('minute', b.pub_ts) - DATE '1970-01-01'
  FROM abc b WHERE b.blurb_id=344143;

enter image description here

I have tried many solution . can you help me out.

Himanshu sharma
  • 7,487
  • 4
  • 42
  • 75

1 Answers1

3

I think are is right, I think it is a duplicate of How do I convert an interval into a number of hours with postgres?

If you check the accepted answer, the only thing different in the example below is, I added a division of 24.

SELECT EXTRACT(epoch FROM INTERVAL '1499990400 days 1206720:00:00')/24/60/60,
       EXTRACT(epoch FROM INTERVAL '17361 days 13:58:00')/24/60/60;

returns

1500040680 and 17361.581944444442

which are exactly the values you want.

Your query should look along the lines of this:

SELECT
  extract( epoch FROM date_trunc('minute', b.pub_ts) - DATE '1970-01-01'),
  extract( epoch FROM date_trunc('minute', b.pub_ts) - DATE '1970-01-01')/86400
FROM abc b WHERE b.blurb_id=344143;
9DA
  • 146
  • 6