Time Calcuations - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Time Calcuations

Author

Topic

dabram
Starting Member

14 Posts

Posted - 2010-07-29 : 13:45:21

Hello,

I am new to SQL and was wondering what the easiest way to calculate the time punched in for a date. I am not sure if there is DateTime function that already exists that would take a date and time and calculate it to midnight or if I will have to create my own function?

Start Time: 7/1/2010 21:00
End Time: 7/2/2010 02:00
The time worked for 7/1 would be 3 hours

Start Time: 7/2/2010 21:00
End Time: 7/3/2010 03:00
The time worked for 7/2 would be 5 hours

Best Regards,
Dave

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-07-29 : 15:57:08

Hi -

Not sure if I completely follow your example. Are you only trying to calculate the hours up to midnight?

The sql DATEDIFF() function sounds like it would work for you, if you are trying to calculate the number of hours between two datetime values.

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-07-29 : 16:04:05

Maybe something like:

declare @starttime datetime = '2010-07-01 21:00:00'
declare @endtime datetime = '2010-07-02 02:00:00'
declare @midnight datetime = dateadd(d, 1, datediff(d, 0, @starttime))
select @starttime, @midnight 

select  DATEDIFF( HOUR,@starttime, @midnight )

dabram
Starting Member

14 Posts

Posted - 2010-07-29 : 16:29:24

Yes, just the times for a specific date. With the example, 7/1/2010 would calculate from 7/1/2010 21:00 to midnight.
7/2/2010 I need to calculate from 7/2/2010 00:00 to 7/2/2010 02:00 and 7/2/2010 21:00 to 7/2/2010 23:59 + 1 minute for a total of 5 hours.

This is what I got so far: If I put in 7/2/2010 21:00 for start_time and 7/3/2010 02:00 for end_time, it will calculate 7/2/2010 as having 3 hours.

ALTER function [dbo].[Udf_CalcHrDiff]
(
@start_time DATETIME,
@end_time DATETIME
)
RETURNS FLOAT
AS
BEGIN
DECLARE
@minutes FLOAT,
@output FLOAT,
@start_time_date DATETIME,
@end_time_date DATETIME,
@hours int,
@new_time DATETIME

SET @hours = 24
SET @start_time_date = DATEDIFF(dd, 0, @start_time)
SET @end_time_date = DATEDIFF(dd, 0, @end_time)
SET @new_time = DATEADD(HOUR, @hours, @start_time_date)

IF @end_time_date = @start_time_date
BEGIN
SELECT @output = 0

IF @start_time is null or @end_time is null
RETURN @output

SELECT @minutes = DATEDIFF(MINUTE, @start_time, @end_time)
SELECT @output = (@minutes/60.0)
RETURN @output
END
ELSE
BEGIN
SELECT @output = 0

IF @start_time is null or @end_time is null
RETURN @output

SELECT @minutes = DATEDIFF(MINUTE, @start_time, @new_time)
SELECT @output = (@minutes/60.0)
RETURN @output
END
RETURN @output
END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-29 : 16:56:48

I know there is a better way. But, I slapped together a brute force method that is not very efficent:

-- Setup some sample data
DECLARE @T TABLE (StartTime DATETIME, EndTime DATETIME)
INSERT @T
SELECT '7/1/2010 21:00', '7/2/2010 02:00'
UNION ALL SELECT '7/2/2010 21:00', '7/3/2010 03:00'
UNION ALL SELECT '7/4/2010 00:00', '7/4/2010 03:00'


-- Run query
SELECT 
	CalendarDay,
	SUM(WorkHours) AS WorkHours
FROM
(
	-- Times that start before midnight, but over lap
	-- So calc the time up until midnight
	SELECT 
		DATEADD(DAY, DATEDIFF(DAY, 0, StartTime), 0) AS CalendarDay,
		DATEDIFF(HOUR, StartTime, DATEADD(DAY, DATEDIFF(DAY, 0, StartTime) + 1, 0)) AS WorkHours
	FROM
		@T
	WHERE
		DATEADD(DAY, DATEDIFF(DAY, 0, StartTime), 0) <> DATEADD(DAY, DATEDIFF(DAY, 0, EndTime), 0)

	UNION ALL 

	-- Times that start before midnight, but over lap
	-- So calc the time after midnight
	SELECT 
		DATEADD(DAY, DATEDIFF(DAY, 0, EndTime), 0) AS CalendarDay,
		DATEDIFF(HOUR, DATEADD(DAY, DATEDIFF(DAY, 0, EndTime), 0), EndTime) AS WorkHours
	FROM
		@T
	WHERE
		DATEADD(DAY, DATEDIFF(DAY, 0, StartTime), 0) <> DATEADD(DAY, DATEDIFF(DAY, 0, EndTime), 0)

	UNION ALL 

	-- No midnight overlap
	SELECT 
		DATEADD(DAY, DATEDIFF(DAY, 0, StartTime), 0) AS CalendarDay,
		DATEDIFF(HOUR, StartTime, EndTime) AS WorkHours
	FROM
		@T
	WHERE
		DATEADD(DAY, DATEDIFF(DAY, 0, StartTime), 0) = DATEADD(DAY, DATEDIFF(DAY, 0, EndTime), 0)
) AS T
GROUP BY
	CalendarDay

dabram
Starting Member

14 Posts

Posted - 2010-07-30 : 07:45:34

Lamprey,

Thanks a bunch, I appreicate your input. I tested it out and it works just fine for what I need it for.

Best regards,
Dave

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-30 : 08:30:17

Is the EndTime always 1 day after StartTime ?

Will you have situation where StartTime & EndTime is 2 days apart ?

KH
[spoiler]Time is always against us[/spoiler]

parody
Posting Yak Master

111 Posts

Posted - 2010-07-30 : 09:28:10

What output are you requiring - i.e. your example crossing midnight means you have two outputs - one for each day. Which day do you need? and what if it crossed several days?

dabram
Starting Member

14 Posts

Posted - 2010-07-30 : 09:40:11

khtan - It will always be 1 day
parody - The solution Lamprey gave is what I needed. It will give the results for several days. originally thinking one day, but then realized it will need to span several days.

parody
Posting Yak Master

111 Posts

Posted - 2010-07-30 : 09:43:59

and the output should be accurate to the minute and in decimal hours?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-30 : 10:07:17

if it is always 1 day diff, you can also try this


-- Setup some sample data
DECLARE @sample TABLE (StartTime DATETIME, EndTime DATETIME)
INSERT @sample	SELECT '7/1/2010 21:00', '7/2/2010 02:00'	
UNION ALL 	SELECT '7/2/2010 21:00', '7/3/2010 03:00'	
UNION ALL 	SELECT '7/4/2010 20:00', '7/4/2010 03:00'

select	[Date], hrs	= sum(datediff(hour, BeginTime, EndTime))
from
(
	select	[Date]		= dateadd(day, datediff(day, 0, StartTime), 0), 
		BeginTime 	= StartTime,
		EndTime 	= dateadd(day, datediff(day, 0, StartTime), 1)
	from	@sample s
	
	union all
	
	select	[Date]		= dateadd(day, datediff(day, 0, EndTime), 0),
		BeginTime	= dateadd(day, datediff(day, 0, EndTime), 0),
		EndTime		= EndTime
	from	@sample s
) d
group by [Date]

KH
[spoiler]Time is always against us[/spoiler]

parody
Posting Yak Master

111 Posts

Posted - 2010-07-30 : 10:16:39

using lampreys source:

WITH CTE AS
(SELECT
convert(date,StartTime) AS WorkDate
,datediff(MINUTE,StartTime,convert(date,dateadd(dd,1,StartTime))) AS WorkHours
FROM @T
UNION ALL
SELECT
convert(date,EndTime)
,datediff(MINUTE,convert(date,dateadd(dd,1,StartTime)),EndTime) AS Gap2
FROM @T)

SELECT
WorkDate
,SUM(WorkHours)/60.0 AS WorkHours
FROM CTE
GROUP BY WorkDate

dabram
Starting Member

14 Posts

Posted - 2010-07-30 : 10:41:34

parody - WOW, what a way of cutting down on code. It's very fast too!

Thanks everyone for all your help, I have learned alot this last couple of days on date/time calculation in SQL!

dabram
Starting Member

14 Posts

Posted - 2010-07-30 : 16:02:01

Is there a way I can add another column to display the running total for WorkHours? I know I have to do something like this, but I just don't know how to fit it in.

SELECT a.WorkDate,
a.WorkHours,
SUM(b.WorkHours)
FROM CTE a
(
SELECT
WorkDate, SUM(WorkHours)/60.0 AS WorkHours
FROM CTE
GROUP BY WorkDate
)
CROSS JOIN @TB b
WHERE (b.WorkDate <= a.WorkDate) AS TotalHours
GROUP BY a.WorkDate, a.WorkHours
ORDER BY a.WorkDate

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-07-30 : 16:50:34

This solution shows how to sum hours over spans greater than 1 day and includes TotalHours as a column as well as HoursToDate.

It's broken down into cascading cte's for readability. Performance should be largely unaffected.



-------------------------------------------------------------------------
-- Setup some sample data
-------------------------------------------------------------------------
DECLARE @T TABLE (StartTime DATETIME, EndTime DATETIME)

INSERT @T
VALUES
 ('7/1/2010 21:00', '7/2/2010 02:00')
,('7/2/2010 21:00', '7/3/2010 03:00')
,('7/4/2010 00:00', '7/4/2010 03:00')
,('7/4/2010 16:45', '7/7/2010 03:00')
,('7/7/2010 12:15', '7/7/2010 13:00');


-------------------------------------------------------------------------
-- Return results
-------------------------------------------------------------------------
WITH		cteNumbers
AS
(
SELECT		spt.number
FROM		master..spt_values AS spt
WHERE		spt.[type] = 'P' 
)
,		cte1
AS
(
SELECT		CONVERT(DATE,DATEADD(d,n.number,d.StartTime)) AS [Date],
		CONVERT(DATE,DATEADD(d,n.number+1,d.StartTime)) AS [NextDate],
		*			
FROM		@T AS d
JOIN		cteNumbers AS n
ON		n.number <= DATEDIFF(d,d.StartTime,d.EndTime)
)
-- 		Following cte could be removed if simple functions were 
-- 		available like dbo.udfGreaterThan and udf.LessorThan
,		cte2
AS
(
SELECT		c.Date,
		CASE WHEN c.Date > c.StartTime THEN c.Date ELSE c.StartTime END AS DateStartTime,
		CASE WHEN c.NextDate < c.EndTime THEN c.NextDate ELSE c.EndTime END AS DateEndTime
FROM		cte1 AS	c
)
,		cte3
AS
(
SELECT		c.Date,
		SUM(DATEDIFF(N,c.DateStartTime, c.DateEndTime))/60.0 AS [Hours]
FROM		cte2 AS	c
GROUP BY	c.Date
)
SELECT		c.Date,
		c.Hours,
		(SELECT SUM(Z.Hours) FROM cte3 AS z WHERE z.Date <= c.Date) AS HoursToDate,
		SUM(c.Hours) OVER(PARTITION BY NULL) AS TotalHours
FROM		cte3 AS c
ORDER BY	c.Date

/*
Date		Hours		HoursToDate	TotalHours
2010-07-01	3.000000	3.000000	73.000000
2010-07-02	5.000000	8.000000	73.000000
2010-07-03	3.000000	11.000000	73.000000
2010-07-04	10.250000	21.250000	73.000000
2010-07-05	24.000000	45.250000	73.000000
2010-07-06	24.000000	69.250000	73.000000
2010-07-07	3.750000	73.000000	73.000000
--*/

Celko
Starting Member

23 Posts

Posted - 2010-07-30 : 18:37:35

Quick tip:

The immediate reaction from hard-core SQL peope when they see a date like "7/1/2010" or "10-JUL-07" is that the poster is Hillbilly who speaks in local dialect.

Learn the ISO-8601 temporal formats. SQL Server now has the ANSI/ISO DATE (yyyy-mm-dd) and TIME (hh:mm:ss.ssss..) datatypes whch are the only formats allowed in Standard SQL and MANY other ISO standards.

The DATEDIFF() and other temporal math functions are still dialect, so you are stuck for a few more release until the old "Sybase code museum" is deprecated. Go to Books-on-line, learn them and write the ANSI version in the margins for later.

Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL

dabram
Starting Member

14 Posts

Posted - 2010-07-31 : 09:45:57

Thanks Lazereth, your code is very helpful.

As for Celko, well.... I honor your tip and your knowledge on SQL programming, but are not very tactful when communicating it.

I am not a Hillbilly; not that you were referring to me alone, but I do honor their cultural lifestyles, along with all other cultures.

A simple quick tip with it's explanation would have been sufficient.

Best regards,

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-07-31 : 14:56:50

dabram -
You can always count on Celko to deliver a rant filled with 95% derision and maybe 5% advice. There is a small group of people that have a difficult time helping people without mocking them, calling them stupid or making inane and inaccurate cultural references. However, that seems to be the way that Mr. Celko likes to work (I have seen/read countless examples online). I am sure that psychologists could explain the reasons. The best tactic with this type of response is to simly ignore the attempts to ridicule and see if there is anything worthwhile that can progress your sql skills. If so, then use it and be thankful. Otherwise, keep going.

Just my dos centavos
- will

parody
Posting Yak Master

111 Posts

Posted - 2010-08-02 : 09:11:34

from my example above add a subquery to the select as below to add running total. Could also be done by cross applying a function that retunrs the total.

SELECT
WorkDate
,SUM(WorkHours)/60.0 AS WorkHours
,(SELECT SUM(WorkHours) FROM CTE a WHERE a.WorkDate <= b.WorkDate) / 60 AS RunningTotal
FROM CTE b
GROUP BY WorkDate
ORDER BY WorkDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources