Right now I'm storing a number of records in SQL Server with a DATETIME column that stores the current timestamp using GETUTCDATE(). This ensures that we're always storing the exact date without having to worry about questions like "well is this 2:00 my time or 2:00 your time?" Using UTC ensures that we know exactly when it happened regardless of timezone.
However, I have a query that essentially groups these records by date. A simplified version of this query looks something like this:
SELECT [created], SUM([amount]) AS [amount]
FROM (
SELECT [amount], LEFT(CONVERT(VARCHAR, [created], 120), 10) AS [created]
FROM (
SELECT [amount], DATEADD(HOUR, -5, [created]) AS [created]
FROM [sales]
WHERE [organization] = 1
) AS s
) AS s
GROUP BY [created]
ORDER BY [created] ASC
Obviously this query is far from ideal--the whole reason I'm here is to ask how to improve it. First of all, it does (for the most part) accomplish the goal of what I'm looking for here--it has things grouped by dates and the other values aggregated accordingly. But what it doesn't accomplish is handling Daylight Savings Time correctly.
I live in Madison, WI and we're on Central Time time, so between March and November we're UTC-5, otherwise we're UTC-6. That's why you see the -5 in the code there as a quick hack to get it working.
The problem is that if I run this query, and there are records that fall on both sides of the daylight savings time changeover, it could potentially group things incorrectly. So for instance, if the table looks something like this:
+----+--------+---------------------+
| id | amount | created |
+----+--------+---------------------+
| 1 | 100.00 | 2010-04-02 06:00:00 |
| 2 | 50.00 | 2010-04-02 04:30:00 |
| 3 | 75.00 | 2010-04-02 03:00:00 |
| 4 | 150.00 | 2010-03-02 07:00:00 |
| 5 | 25.00 | 2010-03-02 05:30:00 |
| 6 | 50.00 | 2010-03-02 04:00:00 |
+----+--------+---------------------+
My query will return this:
+------------+--------+
| created | amount |
+------------+--------+
| 2010-03-01 | 50.00 |
| 2010-03-02 | 175.00 |
| 2010-04-01 | 125.00 |
| 2010-04-02 | 100.00 |
+------------+--------+
However, ideally it SHOULD return this:
+------------+--------+
| created | amount |
+------------+--------+
| 2010-03-01 | 75.00 |
| 2010-03-02 | 150.00 |
| 2010-04-01 | 125.00 |
| 2010-04-02 | 100.00 |
+------------+--------+
The trouble is that if I just subtract a fixed -5, then April is correct but March is not, but if I instead subtract a fixed -6 then March is correct but April is not. What I really need to do is convert to the appropriate time zone in a way that is aware of Daylight Savings Time and can adjust accordingly. Can I do this with SQL query? How do I write this query?