CODEWITHSUNDEEP
sqlsql-servergroup-bysql-order-byutc
Carol
Carol

Reputation: 105

Sorting/Grouping SQL by day-of-week and hour-of-day

I have a SQL Server 2005 table that contains records with a UTC/GMT date field. I need to have the records GROUPed BY and ORDERed by the "day of the week" and "hour of the day". Giving 7*24=168 groups. The output would look like:

Sun 12am
Sun  1am
Sun  2am
.... etc
Mon 12am
Mon  1am
.... etc
.... etc
Sat 10pm
Sat 11pm

Everything works fine until I try to convert things to my local time zone. Doing the conversion inside SQL:

SELECT MIN(Key),MIN(SavedOn), 
FROM MyTable 
GROUP BY (DatePart(WEEKDAY, SavedOn)*24.0) + DatePart(HOUR, SavedOn) - (5.0/24.0)
ORDER BY (DatePart(WEEKDAY, SavedOn)*24.0) + DatePart(HOUR, SavedOn) - (5.0/24.0)

Or doing the conversion in my own code AFTER sorting/getting the records in UTC:

SELECT MIN(Key),MIN(SavedOn), 
FROM MyTable 
GROUP BY (DatePart(WEEKDAY, SavedOn)*24.0) + DatePart(HOUR, SavedOn)  
ORDER BY (DatePart(WEEKDAY, SavedOn)*24.0) + DatePart(HOUR, SavedOn) 

(my own math here)

Either way... 5 records will appear "out of order". The end of the week records (Sat PM) appear at the beginning of the week... not the end... where they belong.

Any idea on what I'm doing wrong?

Upvotes: 8

Views: 19072

Answers (2)

MJB
MJB

Reputation: 7686

Here is what I would do. I am not sure I totally understand what you are trying though:

SELECT DatePart(WEEKDAY, SavedOn), DatePart(HOUR, SavedOn) , min(key)
FROM MyTable 
GROUP BY DatePart(WEEKDAY, SavedOn), DatePart(HOUR, SavedOn)
ORDER BY 1, 2

If your approach to converting is just to add 5 hours, then do it every time you see SavedOn , like this:

SELECT DatePart(WEEKDAY, SavedOn+5.0/24.0), DatePart(HOUR, SavedOn+5.0/24.0) , min(key)
FROM MyTable 
GROUP BY DatePart(WEEKDAY, SavedOn+5.0/24.0, DatePart(HOUR, SavedOn+5.0/24.0)
ORDER BY 1, 2

Upvotes: 1

Andomar
Andomar

Reputation: 238086

Floating point math is imprecise. Multiplying by 24.0 invites border errors.

Instead of adding the numbers

GROUP BY (DatePart(WEEKDAY, SavedOn)*24.0) + DatePart(HOUR, SavedOn)

sort on two fields instead:

GROUP BY DatePart(WEEKDAY, SavedOn), DatePart(HOUR, SavedOn)

Upvotes: 5

Related Questions