One of the more interesting jobs I’ve had over the years was for a company that created emergency room software. It was pretty cool software and I learned a lot, both about writing queries in SQL Server and about how a software company can be run. One of the more interesting things in the various reports we created was the concept of shift calculations. In other words, what happened during a given shift. Say for example breaking down what happened in the various shifts.

• 9am – 5pm
• 5pm – 1am
• 1am – 9am

Or in pseudo code.

• Time >= 9am AND Time < 5pm
• Time >= 5pm OR Time < 1am
• Time >= 1am AND Time < 9am

Now, obviously we don’t care about the date here. Or at least we are going to need to check the date and time separately since most queries are going to have a date range as well. In the end you’ll get a query that looks something like this:

SELECT
	SUM(CASE WHEN MyTime >= '09:00:00' AND MyTime < '17:00:00' THEN 1 ELSE 0 END) AS Shift1_Count
	,SUM(CASE WHEN MyTime >= '17:00:00' OR MyTime < '01:00:00' THEN 1 ELSE 0 END) AS Shift2_Count
	,SUM(CASE WHEN MyTime >= '01:00:00' AND MyTime < '09:00:00' THEN 1 ELSE 0 END) AS Shift3_Count
FROM DateTable
WHERE MyDate >= '1/1/2022' AND MyDate < '2/1/2022';

The important thing I want you to notice here is that I don’t have a single datetime column, instead I have a column of data type date (MyDate) and another column of data type time (MyTime). Why? Because otherwise I would have to convert the datetime column into time every time I wanted to do this type of calculation. Just the act of doing the conversion is going to slow me down. Not to mention that by breaking out the time into it’s own column I can index it separately. That could end up being even more important if I need to group by shift, rather than just do a count.

You should consider yourself lucky that we have the time datatype for this type of thing now. We had to do all kinds of weird things to make this work. I’ve stored the time in a datetime column that were all the same date (say 1/1/9999) or stored them as integers. Let me tell you, that was a pain.

Another important thing to keep in mind when you are working with time this closely is daylight savings time. These reports had to be accurate, not just accurate most of the time. So in order to avoid the problem of time moving back and forth during daylight savings we stored not just the datetime something happened, but the UTC datetime. There is in fact a function in SQL called GetUTCDate() which made that a lot easier. If you aren’t aware of what UTC is, it stands for Universal Time Coordinated also known as the Coordinated Universal Time or Greenwich Mean Time (GMT) and it’s the time that all other time zones are based on. It also isn’t affected by daylight savings. This did add some complexity because we had to adjust the input datetime values to match UTC but it was better than the alternative. Some of this has been made easier with the datatype datetimeoffset but that only helps so much when you are storing the time separately. (There’s no timeoffset.) Come to think of it a persisted calculated column would probably work pretty well here.

To summarize, a shift calculation is a calculation based strictly on a time range. It’s best to store your time separately for this so it can be indexed. Depending on the rules you are working with, if you want your calculations to be accurate you’re probably going to have to use UTC datetimes at least some of the time.