Query to Convert Utc to Cst
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...
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
2 Answers
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))
--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);
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
