I want to calculate the time difference between two datetime. Saturday and sunday need to be excluded from the calculation.
For exemple difference between 2023-01-10 15:12:24 and 2023-01-01 10:34:36 is 6 days 4 hours 37 minutes 48 seconds according to PHP carbon.
<?php
require 'vendor\carbon\autoload.php';
use Carbon\CarbonImmutable;
use Carbon\CarbonInterval;
$created = CarbonImmutable::parse("2023-01-02 10:34:36");
$firstResponse = CarbonImmutable::parse("2023-01-10 15:12:24");
$diffInSeconds = 0;
$step = $created;
while ($step < $firstResponse) {
if ($step->isWeekend()) {
$step = $step->next('Monday');
continue;
}
$nextStep = min($firstResponse, $step->addDay()->startOfDay());
$diffInSeconds += $step->diffInSeconds($nextStep);
$step = $nextStep;
}
echo CarbonInterval::seconds($diffInSeconds)->cascade()->forHumans(); //6 days 4 hours 37 minutes 48 seconds
The goal is to calculate this value using SQL.
I've come to this following query :
WITH RECURSIVE date_range AS (
SELECT '2023-01-02 10:34:36'::timestamp AS date
UNION ALL
SELECT CASE
WHEN EXTRACT(ISODOW FROM date) IN (6, 7) THEN date + INTERVAL '1 day'*(8-EXTRACT(ISODOW FROM date))
ELSE date + INTERVAL '1 DAY'
END
FROM date_range
WHERE date + INTERVAL '1 DAY' < '2023-01-10 15:12:24'::timestamp
)
SELECT
CONCAT(
FLOOR(diff / 86400), ' days ',
FLOOR((diff % 86400) / 3600), ' hours ',
FLOOR((diff % 3600) / 60), ' minutes ',
FLOOR(diff % 60), ' seconds'
) AS duration
FROM (
SELECT
EXTRACT(EPOCH FROM ('2023-01-10 15:12:24'::timestamp - MAX(date))::interval) AS diff
FROM date_range
) t;
Output :
----------------------------------------
| duration |
----------------------------------------
| 0 days 4 hours 37 minutes 48 seconds |
----------------------------------------
I don't understand why days has value equal to 0.
How can I fix the days value ?
Fiddle : https://www.db-fiddle.com/f/3V6QVdE1PPETKS6yN33zdE/0
The reason you always get 0 days is because you selecting MAX(date) which turns out to be 2023-01-10 10:34:36 (the first value that satisfies your exit condition) which is 0 days from 2023-01-10 15:12:24. Perhaps you should select MIN(date). I am not even sure that is valid for all timestamps not if the start and/or end dates specified fall on a weekend?
But why are you messing around with epoch from an interval then the "complicated" date/time calculations. Your process centers around 2 hard-coded timestamps. The subtraction of 2 timestamps gives an interval then you can directly extract each field. Your query reduces to: (see demo)
with parms (start_date, end_date) as
( select '2023-01-02 10:34:36'::timestamp --- as parameter $1
, '2023-01-10 15:12:24'::timestamp --- as parameter $2
)
, weekend_days (wkend) as
( select sum(case when extract(isodow from d) in (6, 7) then 1 else 0 end)
from parms
cross join generate_series(start_date, end_date, interval '1 day') dn(d)
)
select concat( extract( day from diff) , ' days '
, extract( hours from diff) , ' hours '
, extract( minutes from diff) , ' minuets '
, extract( seconds from diff)::int , ' seconds '
)
from (
select (end_date-start_date)- ( wkend * interval '1 day') diff
from parms
join weekend_days on true
) sq;
You can even wrap the query in a SQL function and completely hide it away.
create or replace function diff_without_weekend( start_date_in timestamp
, end_date_in timestamp)
returns text
language sql
as $$
with weekend_days (wkend) as
( select sum(case when extract(isodow from d) in (6, 7) then 1 else 0 end)
from generate_series(start_date_in, end_date_in, interval '1 day') dn(d)
)
select CONCAT( extract( day from diff) , ' days '
, extract( hours from diff) , ' hours '
, extract( minutes from diff) , ' minuets '
, extract( seconds from diff)::int , ' seconds ')
from ( select (end_date_in -start_date_in )- ( wkend * interval '1 day') diff
from weekend_days
) sq;
$$;