Question 89531 in PostgreSQL Question posted in
The official documentation can be found here.

Calculate time difference bewteen 2 datetime excluding weekend in days hours minutes seconds format – Postgresql

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 'vendorcarbonautoload.php';
use CarbonCarbonImmutable;
use CarbonCarbonInterval;


$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

Tags:

3

Answers


  1. 0

    Really interesting question which completely distracted me from work! This question is similar and could be useful to you:Get all dates between two dates in SQL Server .
    I think the recommendation of building a calendar table would help you a lot!

    To directly answer your question I think it you need to change from max to min, as I have, here:

    FROM (
SELECT
EXTRACT(EPOCH FROM ('2023-01-10 15:12:24'::timestamp - MIN(date))::interval) AS diff
  FROM date_range) t;

    However even with this you return 8 days when I think the answer should be 7….

    I’m not overly familiar with the recursive so I went for something like the below. The case statement can definitely be optimised. {note I’m snowflake dialect}

    SET start_date = '2023-01-02 10:34:36';
SET end_date = '2023-01-10 15:12:24';
WITH DateRange(DateData) AS
    (
    SELECT $start_date::DATe as Date
    UNION ALL
    SELECT DATEADD(DAYS ,1,DateData)::DATE
    FROM DateRange
    WHERE DateData < $end_date
    )
,date_array AS (
    SELECT datedata
            , DAYOFWEEK(datedata) AS day_index
            , dayname(datedata) AS day_name
            , $start_date
            , $end_date
    FROM daterange
)

SELECT
       $start_date
       , substring($start_date ,12,8)::TIME as start_time
       , $end_date
       , substring($end_date ,12,8)::TIME as end_time
       , HOUR(end_time) - HOUR(start_time) as hours
       , MINUTE(end_time) - MINUTE(start_time) as minutes
       , SECOND(end_time) - SECOND(start_time) as seconds
       , COUNT(DISTINCT CASE WHEN day_index NOT IN (6,0) THEN  datedata END ) as  days
       , CONCAT (
           CASE WHEN hours < 0 THEN days -1 ELSE days END , ' days ' ,

           CASE
               WHEN minutes < 0 AND hours < 0 THEN 24 + hours  -1
               WHEN minutes > 0 AND hours < 0 THEN 24 + hours
               WHEN minutes < 0 AND hours > 0 THEN   hours -1
                   ELSE hours END
           , ' hours ' ,
           CASE
               WHEN seconds < 0 AND minutes < 0 THEN 60 + minutes  -1
               WHEN seconds > 0 AND minutes < 0 THEN 60 + minutes
               WHEN seconds < 0 AND minutes > 0 THEN   minutes -1
                   ELSE minutes END

           , ' minutes ' ,
           CASE WHEN seconds <0 THEN 60 + seconds ELSE seconds END , ' seconds') as output
FROM date_array
GROUP BY 1,2,3

    Output :

    | output |

    | 7 days 4 hours 37 minutes 48 seconds |

    Login or Signup to reply.
  2. 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;
$$;
    Login or Signup to reply.
  3. 0

    Since your difference time is calculated by extract end date to the MAX(date) – which its value is same date as end date or the last working date before end date, so difference days value could not be calculated correctly.

    You could achieve your desired result by using below query.

    1. Generating date series from start date to end date by using generate_series function, then get only working days from this series.
    2. For each working date in date series, calculate working time per each date (data type of working time will be Postgres’s interval).
    3. Then calculating total working time of all working dates in date series.
    SET intervalstyle = 'postgres_verbose'; -- format display style for interval

WITH date_range AS 
      (SELECT '2023-01-02 10:34:36'::timestamp AS start_date
            , '2023-01-10 15:12:24'::timestamp AS end_date),
     date_series AS
      (SELECT  d as date
            ,  (CASE
                  WHEN start_date::date = d THEN interval '1 day' + (d - start_date)::interval
                  WHEN end_date::date = d THEN  (end_date - d)::interval
                  ELSE interval '1 day'
               END) AS working_time
       FROM date_range 
            CROSS JOIN generate_series(start_date::date, end_date::timestamp, interval '1 day') d
       WHERE EXTRACT (ISODOW FROM  d) BETWEEN 1 AND 5)
       
SELECT SUM(working_time) AS working_time
FROM date_series;

    See demo here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search