SQL difference between two datetime columns

Question

I have a dataset with 2 columns of datetime datatype as shown here:

I want to take the difference between the two dates and I try it with this code:

Select 
    *, 
    original_due_date - due_date as difference
from 
    Table

However I'm not sure if the same would suffice as this is a datetime and not just date.

Any inputs would be much appreciated.

Desired output

Postgres doesn't have a `datetime` data type. Do you mean `timestamp`? And if so, what results do you want? Just in days? As an interval? In some other. unit? — Gordon Linoff, Aug 20 '21 at 11:36
Sample data is better presented as [formatted text](https://meta.stackoverflow.com/a/251362). See [here](https://meta.stackexchange.com/questions/81852) for some tips on how to create nice looking tables. — , Aug 20 '21 at 11:36
The result of subtracting a `timestamp` from a `timestamp` is an `interval` — , Aug 20 '21 at 11:37
Hi @GordonLinoff, basically I want to take the difference between them and arrive the result in days. (eg 21-Apr-2021 - 22-Apr-2021 = 1 day) So I need the difference column to be number of days. Hope I made sense! — Sid, Aug 20 '21 at 11:39

Gordon Linoff · Answer 1 · 2021-08-20T12:24:47.170

0

The question was originally tagged Postgres, so this answers the original question.

Presumably, you are storing the values as timestamps. If you just want the results in days, then convert to dates and take the difference:

Select t.*,
       (t.original_due_date::date - t.due_date::date) AS difference
from Table t;

If you want fractional days, then a pretty simple method is to extract the "epoch", which is measured in seconds, and use arithmetic:

Select t.*,
       ( extract(epoch from t.original_due_date - 
         extract(epoch from t.due_date
       ) / (24.0 * 60 * 60) AS decimal_days
from Table t;

edited Aug 20 '21 at 12:24

answered Aug 20 '21 at 11:40

Gordon Linoff

1,242,037
58
646
786

my apologies, apparently hiveSQL does not allow use of ":". I had overlooked to mention the same. – Sid Aug 20 '21 at 11:42

score 0 · Answer 2 · answered Aug 20 '21 at 13:49

transform timestamps to seconds (unix_timestamp), calculate difference and divide by (60*60*24) to get days

select  (unix_timestamp(original_due_date, 'MM-dd-yyyy HH:mm')-unix_timestamp(due_date, 'MM-dd-yyyy HH:mm'))/(60*60*24) as difference_days
 from (select '07-01-2021 00:00' as due_date,  '02-10-2020 00:00' as original_due_date) t

Result:

-507

SQL difference between two datetime columns

2 Answers2