Postgresql: How to find hours between 2 dates and 2 times?
Adding a time to a date yields a timestamp and subtracting one timestamp from another returns an interval.
So all you need to do is:
(enddate + endtime) - (startdate + starttime) as diff
An interval is nice in the context of SQL, but usually harder to handle in a programming language. You can easily convert an interval to seconds using extract(epoch from interval)
If you want to convert that to hours use extract and divide by 3600
extract(epoch from (enddate + endtime) - (startdate + starttime))/3600 as diff_hours
Counting business hours between timestamps
Imagine that you have a table of work minutes. (Or build one. This one isn't tested, so it might contain timezone and fencepost errors.)
create table work_minutes (
work_minute timestamp primary key
);
insert into work_minutes
select work_minute
from
(select generate_series(timestamp '2013-01-01 00:00:00', timestamp '2013-12-31 11:59:00', '1 minute') as work_minute) t
where extract(isodow from work_minute) < 6
and cast(work_minute as time) between time '09:00' and time '17:30'
Now your query can count minutes, and that's just dead simple.
select count(*)/60.0 as elapsed_hrs
from work_minutes
where work_minute between '2013-01-23 10:47:52' and '2013-02-25 11:18:36'
elapsed_hours
--
196.4
You can decide what to do with fractional hours.
There can be a substantial difference between calculating by minutes and calculating by hours, depending on how you treat the start time and such. Calculations based on hours might not count a lot of minutes in an hour that extends beyond the stop time. Whether it matters is application-dependent.
You can generate a virtual table like this on the fly with generate_series(), but a base table like this only needs about 4 million rows to cover 30 years, and this kind of calculation on it is really fast.
Later . . .
I see that Erwin Brandstetter covered the use of generate_series() for modern PostgreSQL; it won't work in version 8.3, because 8.3 doesn't support common table expressons or generate_series(timestamp, timestamp). Here's a version of Erwin's query that avoids those problems. This isn't a completely faithful translation; the calculations differ by an hour. That's probably a fencepost error on my part, but I don't have time to dig into the details right now.
select count(*) from
(select timestamp '2013-01-23 10:47:52-05' + (n || ' hours')::interval
from generate_series( 0
, (extract(days from timestamp '2013-02-25 11:18:36-05'
- timestamp '2013-01-23 10:47:52-05')::integer * 24) ) n
where extract(isodow from (timestamp '2013-01-23 10:47:52-05' + (n || ' hours')::interval)) < 6
and (timestamp '2013-01-23 10:47:52-05' + (n || ' hours')::interval)::time >= '09:00'::time
and (timestamp '2013-01-23 10:47:52-05' + (n || ' hours')::interval)::time < '17:30'::time
) t
A table-based solution has the advantage of easily handling management whimsy. "Hey! Our dog had seven puppies! Half day today!" It also scales well, and it works on virtually every platform without modification.
If you use generate_series(), wrap it in a view. That way, arbitrary changes to the rules can be managed in one place. And if the rules become too complicated to maintain easily within the view, you can replace the view with a table having the same name, and all the application code, SQL, and stored procedures and functions will just work.
How to calculate difference between two days in hours in postgreSQL and return as days?
You can use epoch arithmetic:
select ceiling(extract(epoch from date2) - extract(epoch from date1)) / (24 * 60 * 60)
from t;
Note that this particular formulation counts anything longer than 1 day as 2 days. I think that is the intention of your question. However, if you really do have a 1 hour buffer, the logic could be tweaked to handle that.
Calculate business hours between two dates
Baran's answer fixed and modified for SQL 2005
SQL 2008 and above:
-- =============================================
-- Author: Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime]
(
@StartDate DATETIME,
@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Temp BIGINT
SET @Temp=0
DECLARE @FirstDay DATE
SET @FirstDay = CONVERT(DATE, @StartDate, 112)
DECLARE @LastDay DATE
SET @LastDay = CONVERT(DATE, @FinishDate, 112)
DECLARE @StartTime TIME
SET @StartTime = CONVERT(TIME, @StartDate)
DECLARE @FinishTime TIME
SET @FinishTime = CONVERT(TIME, @FinishDate)
DECLARE @WorkStart TIME
SET @WorkStart = '09:00'
DECLARE @WorkFinish TIME
SET @WorkFinish = '17:00'
DECLARE @DailyWorkTime BIGINT
SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)
IF (@StartTime<@WorkStart)
BEGIN
SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
SET @FinishTime=@WorkFinish
END
IF (@FinishTime<@WorkStart)
BEGIN
SET @FinishTime=@WorkStart
END
IF (@StartTime>@WorkFinish)
BEGIN
SET @StartTime = @WorkFinish
END
DECLARE @CurrentDate DATE
SET @CurrentDate = @FirstDay
DECLARE @LastDate DATE
SET @LastDate = @LastDay
WHILE(@CurrentDate<=@LastDate)
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + @DailyWorkTime
END
--IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
END
END
SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
END
-- Return the result of the function
IF @Temp<0
BEGIN
SET @Temp=0
END
RETURN @Temp
END
SQL 2005 and below:
-- =============================================
-- Author: Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime]
(
@StartDate DATETIME,
@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Temp BIGINT
SET @Temp=0
DECLARE @FirstDay DATETIME
SET @FirstDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate))
DECLARE @LastDay DATETIME
SET @LastDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @FinishDate))
DECLARE @StartTime DATETIME
SET @StartTime = @StartDate - DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)
DECLARE @FinishTime DATETIME
SET @FinishTime = @FinishDate - DATEADD(dd, DATEDIFF(dd, 0, @FinishDate), 0)
DECLARE @WorkStart DATETIME
SET @WorkStart = CONVERT(DATETIME, '09:00', 8)
DECLARE @WorkFinish DATETIME
SET @WorkFinish = CONVERT(DATETIME, '17:00', 8)
DECLARE @DailyWorkTime BIGINT
SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)
IF (@StartTime<@WorkStart)
BEGIN
SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
SET @FinishTime=@WorkFinish
END
IF (@FinishTime<@WorkStart)
BEGIN
SET @FinishTime=@WorkStart
END
IF (@StartTime>@WorkFinish)
BEGIN
SET @StartTime = @WorkFinish
END
DECLARE @CurrentDate DATETIME
SET @CurrentDate = @FirstDay
DECLARE @LastDate DATETIME
SET @LastDate = @LastDay
WHILE(@CurrentDate<=@LastDate)
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + @DailyWorkTime
END
--IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
END
END
SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
END
-- Return the result of the function
IF @Temp<0
BEGIN
SET @Temp=0
END
RETURN @Temp
END
Postgres - calculate total working hours based on IN and OUT entry
The tricky part is to expand one row that covers two (calendar) days to two rows and allocating the hours of the "next" day correctly.
The first part is to get a pivot table that combines IN/OUT pairs into a single row.
A simple (yet not very efficient) approach is:
select ain.u_id,
ain.swipe_time as time_in,
(select min(aout.swipe_time)
from attendance aout
where aout.u_id = ain.u_id
and aout.status = 'OUT'
and aout.swipe_time > ain.swipe_time) as time_out
from attendance ain
where ain.status = 'IN'
The next step is to break up the rows with more than one day into two rows.
This assumes that you never have an IN/OUT pair that covers more than two days!
with inout as (
select ain.u_id,
ain.swipe_time as time_in,
(select min(aout.swipe_time)
from attendance aout
where aout.u_id = ain.u_id
and aout.status = 'OUT'
and aout.swipe_time > ain.swipe_time) as time_out
from attendance ain
where ain.status = 'IN'
), expanded as (
select u_id,
time_in::date as "date",
time_in,
time_out
from inout
where time_in::date = time_out::date
union all
select i.u_id,
x.time_in::date as date,
x.time_in,
x.time_out
from inout i
cross join lateral (
select i.u_id,
i.time_in,
i.time_in::date + 1 as time_out
union all
select i.u_id,
i.time_out::date,
i.time_out
) x
where i.time_out::date > i.time_in::date
)
select *
from expanded;
The above returns the following for your sample data:
u_id | date | time_in | time_out
-----+------------+---------------------+--------------------
1 | 2020-08-20 | 2020-08-20 16:00:00 | 2020-08-20 20:00:00
1 | 2020-08-20 | 2020-08-20 21:00:00 | 2020-08-21 00:00:00
1 | 2020-08-21 | 2020-08-21 00:00:00 | 2020-08-21 01:00:00
1 | 2020-08-21 | 2020-08-21 16:00:00 | 2020-08-21 19:00:00
How does this work?
So we first select all those rows that start and end on the same day with this part:
select u_id,
time_in::date as "date",
time_in,
time_out
from inout
where time_in::date = time_out::date
The second part of the union splits up the rows that span two days by using a cross join that generates one row with the original start time and midnight, and another from midnight to the original end time:
select i.u_id,
x.time_in::date as date,
x.time_in,
x.time_out
from inout i
cross join lateral (
-- this generates a row for the first of the two days
select i.u_id,
i.time_in,
i.time_in::date + 1 as time_out
union all
-- this generates the row for the next day
select i.u_id,
i.time_out::date,
i.time_out
) x
where i.time_out::date > i.time_in::date
At the end the new "expanded" rows are then aggregated by grouping them by user and date and left joined to the users table to get the username as well.
with inout as (
select ain.u_id,
ain.swipe_time as time_in,
(select min(aout.swipe_time)
from attendance aout
where aout.u_id = ain.u_id
and aout.status = 'OUT'
and aout.swipe_time > ain.swipe_time) as time_out
from attendance ain
where ain.status = 'IN'
), expanded as (
select u_id,
time_in::date as "date",
time_in,
time_out
from inout
where time_in::date = time_out::date
union all
select i.u_id,
x.time_in::date as date,
x.time_in,
x.time_out
from inout i
cross join lateral (
select i.u_id,
i.time_in,
i.time_in::date + 1 as time_out
union all
select i.u_id,
i.time_out::date,
i.time_out
) x
where i.time_out::date > i.time_in::date
)
select u.id,
u.u_name,
e."date",
min(e.time_in) as time_in,
max(e.time_out) as time_out,
sum(e.time_out - e.time_in) as duration
from users u
left join expanded e on u.id = e.u_id
group by u.id, u.u_name, e."date"
order by u.id, e."date";
Which then results in:
u_id | date | time_in | time_out | duration
-----+------------+---------------------+---------------------+----------------------------------------------
1 | 2020-08-20 | 2020-08-20 16:00:00 | 2020-08-21 00:00:00 | 0 years 0 mons 0 days 7 hours 0 mins 0.0 secs
1 | 2020-08-21 | 2020-08-21 00:00:00 | 2020-08-21 19:00:00 | 0 years 0 mons 0 days 4 hours 0 mins 0.0 secs
The "duration" column is an interval which you can format to your liking.
Online example
Calculate time difference (only working hours) in minutes between two dates
If you want to do it pure SQL here's one approach
CREATE TABLE working_hours (start DATETIME, end DATETIME);
Now populate the working hours table with countable periods, ~250 rows per year.
If you have an event(@event_start, @event_end) that will start off hours and end off hours then simple query
SELECT SUM(end-start) as duration
FROM working_hours
WHERE start >= @event_start AND end <= @event_end
will suffice.
If on the other hand the event starts and/or ends during working hours the query is more complicated
SELECT SUM(duration)
FROM
(
SELECT SUM(end-start) as duration
FROM working_hours
WHERE start >= @event_start AND end <= @event_end
UNION ALL
SELECT end-@event_start
FROM working_hours
WHERE @event_start between start AND end
UNION ALL
SELECT @event_end - start
FROM working_hours
WHERE @event_end between start AND end
) AS u
Notes:
- the above is untested query, depending on your RDBMS you might need date/time functions for aggregating and subtracting datetime (and depending on the functions used the above query can work with any time precision).
- the query can be rewritten to not use the UNION ALL.
- the working_hours table can be used for other things in the system and allows maximum flexibility
EDIT:
In MSSQL you can use DATEDIFF(mi, start, end) to get the number of minutes for each subtraction above.
Related Topics
How to Select Id with Max Date Group by Category in Postgresql
Rewriting MySQL Select to Reduce Time and Writing Tmp to Disk
How to Insert Multiple Records and Get the Identity Value
Postgres Column "X" Does Not Exist
How to Use Boolean Type in Select Statement
Casting Null Type When Updating Multiple Rows
How to Pass Parameters to a View in SQL
Generate All Combinations in SQL
Get the Records of Last Month in SQL Server
Way to Try Multiple Selects Till a Result Is Available
Solution For: Store Update, Insert, or Delete Statement Affected an Unexpected Number of Rows (0)
How to Design a Database for User Defined Fields
Call a Stored Procedure with Another in Oracle
How to See Active SQL Server Connections