CODEWITHSUNDEEP
postgresql
user3614760
user3614760

Reputation: 9

Calculate time difference between two timestamp with additional conditions in postgres

I would like to get the total number of hours,minute, and seconds difference between two timestamp fields based on starting and ending time in postgres

Table supermarket has fields opening_time and closing_time

Table orders have the fields order_id, arrived_date, and picked_date

I want to calculate the total time spend to pick an order. (The orders can be picked only the supermarket is open but the orders will get placed and queued anytime in the day)

Condition: The total picking time should be calculated considering the opening and closing time of the store.

Example

Consider opening_time is 09:00:00 and closing_time is 22:00:00

Case 1: if an order arrives on 2020-09-08 10:00:00 and is picked at 2020-09-08 12:00:00, then the total picking time should be 02 hours

Case 2: if an order arrives on 2020-09-08 06:00:00 and is picked at 2020-09-08 12:00:00, then the total picking time should be 03 hours, not 06 hours considering the opening time

Case 3: if an order arrives on 2020-09-08 23:00:00 and is picked next day at 2020-09-09 10:00:00, then the total picking time should be 01 hour considering closing and opening time

Upvotes: 0

Views: 758

Answers (2)

Schwern
Schwern

Reputation: 164809

Timestamps can simply be subtracted resulting in an interval.

test=# select '2020-09-08 12:00:00'::timestamp - '2020-09-08 10:00:00'::timestamp;
 ?column? 
----------
 02:00:00
(1 row)

That interval can then be formatted with to_char.

test=# select to_char('2020-09-08 12:00:00'::timestamp - '2020-09-08 10:00:00'::timestamp, 'HH24 hours MI "minutes"');
       to_char       
---------------------
 02 hours 00 minutes
(1 row)

To get the correct interval considering opening and closing time, you need a little bit of logic to figure out the actual picking start time.

select
case
when arrived_date::time < opening_time
  -- It arrived before you're open. Start when you open that day.
  arrived_date::date + opening_time
when arrived_date::time > closing_time
  -- It arrived after you closed. Start when you open tomorrow.
  arrived_date::date + '1 day'::interval + opening_time
else
  -- It arrived while you're open. Start when it arrives.
  arrived_date
end as picking_start

The trick is to cast arrived_date to time, truncating the date part, to compare with the opening and closing times. Similarly, we can cast arrived_date to date and use just the date part, then add the opening time. This assumes that arrived_date is a timestamp and that opening_time and closing_time are time columns.

This could be condensed into a function for easy use.

Upvotes: 0

GMB
GMB

Reputation: 222462

It is a bit more complicated that it would seem to, especially if the ranges may expand over more than 24 hours. The safest solution approach might be a brute-force approach that generates all hours in the range, then filtering and aggregation:

select s.*, x.*
from supermarket s
cross join lateral (
    select count(*) no_hours
    from generate_series(s.opening_time, s.closing_time, '1 hour') x(x_time)
    where x_time::time >= '09:00:00'::time and x_time::time < '22:00:00'::time
) x

This assumes that the opening and closing dates are truncated to the hour, as shown in your examples. If you want to handle minutes, then:

select s.*, x.*
from supermarket s
cross join lateral (
    select count(*) no_minutes
    from generate_series(s.opening_time, s.closing_time, '1 minute') x(x_time)
    where x_time::time >= '09:00:00'::time and x_time::time < '22:00:00'::time
) x

Upvotes: 1

Related Questions