Right now I am storing multiple records in SQL Server with a DATETIME column that saves the current timestamp using GETUTCDATE() . This ensures that we always keep the exact date without worrying about issues such as “ok, is this 2 hours of my time or 2 hours of your time?” Using UTC ensures that we know exactly when this happened, regardless of time zone.
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 request is far from ideal - the whole reason I am here is to ask how to improve it. First of all, it (for the most part) fulfills the goal of what I am looking for here - these are things grouped by dates and other values aggregated accordingly. But what he does not correctly manage daylight saving time.
I live in Madison, Wisconsin, and we are in central time, so between March and November we are UTC-5, otherwise we are UTC-6. That's why you see -5 in the code there as a quick hack to make it work.
The problem is that if I run this query and there are records that fall on both sides of the daylight saving time, this can lead to incorrect grouping. So, for example, 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 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 problem is that if I simply subtract the fixed -5 , then April will be correct, but March will not, but if instead subtract the fixed -6 , then March will be correct, but April will not. What I really need to do is convert to the appropriate time zone in a way that knows about summer time savings and can adjust accordingly. Can I do this with an SQL query? How to write this request?