Date intervals in Postgresql not showing correct results

Question

When running this query:

SELECT  start_date,
        end_date,
        extract(epoch from end_date::timestamp - start_date::timestamp)/(24*60*60) as total,
        extract(epoch from end_date::timestamp - GETDATE()::timestamp)/(24*60*60) as left

FROM    app_data.content_cards

I get a table like this:

+---------------------+---------------------+-------+-------+
| start_date          | end_date            | total | left  |
+---------------------+---------------------+-------+-------+
| 2017-03-30 22:00:00 | 2017-04-07 22:00:00 | 8     | -9.89 |
+---------------------+---------------------+-------+-------+
| 2017-04-09 22:00:00 | 2017-04-11 22:00:00 | 2     | -5.89 |
+---------------------+---------------------+-------+-------+
| 2017-04-03 22:00:00 | 2017-04-11 22:00:00 | 8     | -5.89 |
+---------------------+---------------------+-------+-------+
| 2017-03-30 22:00:00 | 2017-04-18 22:00:00 | 19    | 1.11  |
+---------------------+---------------------+-------+-------+
| 2017-04-09 22:00:00 | 2017-04-15 22:00:00 | 6     | -1.89 |
+---------------------+---------------------+-------+-------+
| 2017-04-02 22:00:00 | 2017-05-20 22:00:00 | 78    | 63.11 |
+---------------------+---------------------+-------+-------+
| 2017-04-23 22:00:00 | 2017-04-29 22:00:00 | 6     | 12.11 |
+---------------------+---------------------+-------+-------+
| 2017-04-02 22:00:00 | 2017-05-20 22:00:00 | 78    | 63.11 |
+---------------------+---------------------+-------+-------+

But if I use this query:

SELECT  start_date,
        end_date,
        end_date - start_date as total,
        end_date - GETDATE() as days

FROM    app_data.content_cards

I get a table like this:

+--------------------+-------------------+-------+-----------------+
| start_date         | end_date          | total | left            |
+--------------------+-------------------+-------+-----------------+
|2017-03-30 22:00:00 |2017-04-07 22:00:00|8 days |-9 days -21:21:47|
+--------------------+-------------------+-------+-----------------+
|2017-04-09 22:00:00 |2017-04-11 22:00:00|2 days |-5 days -21:21:47|
+--------------------+-------------------+-------+-----------------+
| 2017-04-03 22:00:00|2017-04-11 22:00:00|8 days |-5 days -21:21:47|
+--------------------+-------------------+-------+-----------------+
| 2017-03-30 22:00:00|2017-04-18 22:00:00|19 days|1 day 02:38:13   |
+--------------------+-------------------+-------+-----------------+
| 2017-04-09 22:00:00|2017-04-15 22:00:00|6 days |-1 days -21:21:47|
+--------------------+-------------------+-------+-----------------+
| 2017-04-02 22:00:00|2017-05-20 22:00:00|48 days|33 days 02:38:13 |
+--------------------+-------------------+-------+-----------------+
| 2017-04-23 22:00:00|2017-04-29 22:00:00|6 days |12 days 02:38:13 |
+--------------------+-------------------+-------+-----------------+
| 2017-04-02 22:00:00|2017-05-20 22:00:00|48 days|33 days 02:38:13 |
+--------------------+-------------------+-------+-----------------+

Note the different results in rows 1 & 3 starting from the bottom. The correct should be the one the last query returns.

What I need is to have the results in days and with two decimals (like in the first table), but with the data being correct (like the second one). Also, I need the data to be without 'days' in the result, just need the value with two decimals.

How can I achieve this?

I am using: version PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1232

Answer 1

This query should work for you.

select  start_date,
        end_date,
        (date_part(epoch,end_date)::float- date_part(epoch,start_date)::float)/(24*60*60) as total,
        (date_part(epoch,end_date)::float- date_part(epoch,sysdate)::float)/(24*60*60) as total left        
from    app_data.content_cards

OR
You can use your extract function too.

SELECT  start_date, 
        end_date, 
        (extract(epoch from end_date)::float - extract(epoch from start_date)::float)/(24*60*60) as total,
        (extract(epoch from end_date)::float - extract(epoch from sysdate)::float)/(24*60*60) as left
FROM    app_data.content_cards

If I’ve made a bad assumption please comment and I’ll refocus my answer.