Query to convert UTC to CST

Question

I'm using SQL Server to count rows in a 24 hour period. I've accomplished that, however I can't figure out how to convert the UTC time to CST that the database stores.

Then...

How to breakdown the 24 hours into 24 x 1 hour blocks with a SUM or Count of each hours rows?

Convert UTC TIME to CST?

Query?

-- THEN

Select COUNT (*) AS Total
From readmodels.Database
Where
    Timestamp >= '2018-01-18' AND 
    Timestamp <= '2018-01-19'

-- Then Breakdown the count into 24 - 1 hour blocks

Hi and welcome to SO. This needs more detail for people to have a chance at helping you. Here is a great place to start http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ Also, I would suggest not using Timestamp as a column name. It is a reserved word because it is actually a datatype, but it has nothing to do with date or time. — Sean Lange, Jan 22 '18 at 20:50
I don't recommend performing any time of time-zone operations inside SQL as it is largely a view-level concern (assuming all of your data is stored as UTC internally). Is there any way to change the database to use UTC instead of CST? How does the design handle daylight savings? — Dai, Jan 22 '18 at 22:12
Do you want to convert all dates using current time zone/daylight offset only? Then take a look at [SWITCHOFFSET](https://learn.microsoft.com/en-us/sql/t-sql/functions/switchoffset-transact-sql). If you need to take into account historical data and historical daylight saving changes then https://www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-in-sql-server--part-1/ — Alex, Jan 22 '18 at 22:57
[This answer](https://stackoverflow.com/q/24797/538763) provides some DST aware approaches. — crokusek, Jan 23 '18 at 01:24

Robert · Answer 1 · 2018-01-23T01:10:49.700

If you only need to convert from UTC to CST. You can simply use DATEADD(hour, -6, Timestamp) in your query.

e.g.

Select COUNT(*) as count, DATEPART(year, DATEADD(hour, -6, Timestamp)) as year, DATEPART(month, DATEADD(hour, -6, Timestamp)) as month, DATEPART(day, DATEADD(hour, -6, Timestamp)) as day, DATEPART(hour, DATEADD(hour, -6, Timestamp)) as hour
From readmodels.Database
Where
DATEADD(hour, -6, Timestamp) >= '2018-01-18' AND 
DATEADD(hour, -6, Timestamp) <= '2018-01-19'
Group by DATEPART(year, DATEADD(hour, -6, Timestamp)), DATEPART(month, DATEADD(hour, -6, Timestamp)), DATEPART(day, DATEADD(hour, -6, Timestamp)), DATEPART(hour, DATEADD(hour, -6, Timestamp))

score 0 · Answer 2 · answered Jan 23 '18 at 01:24

--this is what I ended up using

SELECT  dateadd(hour, datediff(hour, 0, TimeStamp), 0) as TimeStampHour_CST, Count(*) As Total_Per_Hour
FROM    readmodels.database
WHERE   Timestamp >= '2018-01-17' AND 
        Timestamp <= '2018-01-18'
GROUP BY dateadd(hour, datediff(hour, 0, TimeStamp), 0)
ORDER BY dateadd(hour, datediff(hour, 0, TimeStamp), 0);

Query to convert UTC to CST

2 Answers2