I am trying to subtract one date from another but having issues:

SELECT MIN(date) AS first_day, 
MAX(date) AS last_date, 
((MAX(date)) - (MIN(date))) AS totaL_days
FROM dates;

Could someone please clarify the number format of the number it is returning below?

 ------------ 
| total_days |
 ------------ 
|   29001900 |

I have tried using DATEDIFF but this rounds the days to the nearest whole number and I need to carry out further calculations with the data. The rounding means my solutions are a little off. In the version of DB I am using DATEDIFF() only takes two parameters so always has to be days as far as I'm aware, I get an error if I try to use hours.

CodePudding user response：

SELECT DATEDIFF
(
    SELECT MAX(date) FROM dates,
    SELECT MIN(date) FROM dates
)
AS totaL_days;

should do the trick.

CodePudding user response：

I'm assuming your RDBMS is a MySql.

Then that number you got would be the seconds between those 2 datetimes.

Because if you subtract 2 DATE types you would get the days between them.

There's more than DATEDIFF to work with.

Test data

create table mytest (
 id int auto_increment primary key, 
 date_col date not null, 
 datetime_col datetime not null
);

insert into mytest(date_col, datetime_col) values
 ('2021-06-16', '2021-06-16 14:15:30') 
,('2021-07-16', '2021-07-16 19:06:15')

Test using dates

select 
  min(date_col) as min_date 
, max(date_col) as max_date
, max(date_col) - min(date_col) as subtracted
, datediff(max(date_col), min(date_col)) as days
from mytest

min_date   | max_date   | subtracted | days
:--------- | :--------- | ---------: | ---:
2021-06-16 | 2021-07-16 |        100 |   30

Test using datetimes

select 
  min(datetime_col) as min_date 
, max(datetime_col) as max_date
, max(datetime_col) - min(datetime_col) as seconds
, datediff(max(datetime_col), min(datetime_col)) as days
from mytest

min_date            | max_date            |   seconds | days
:------------------ | :------------------ | --------: | ---:
2021-06-16 14:15:30 | 2021-07-16 19:06:15 | 100049085 |   30

Using sec_to_time and extract

select seconds
, sec_to_time(seconds) as tm
, extract(day from sec_to_time(seconds)) as days
from (
   select 
   max(datetime_col) - min(datetime_col) as seconds 
   from mytest
) q

  seconds | tm        | days
--------: | :-------- | ---:
100049085 | 838:59:59 |   30

db<>fiddle here