Query for events by local time

I have a database (SQL Server 2005, but I think my question is more general) with GMT timestamped events. For a number of reasons, I need to be able to query and aggregate data based on a user's local time. For example, how many events occurred between 5pm and 7pm local time? DST is really throwing me for a loop on this one.

I've thought about trying to maintain a table of all timezone/dst rules. Then I could join my events table to that, limiting results to the timezone/dst info for the user. So the query for my example would look something like:

select count(e.ID)
from events e
  join dst d on e.timeGMT between d.startGMT and d.endGMT
where d.region=@userRegion
  and dbo.getTime(dateadd(ss, d.offsetSec, e.timeGMT)) between '17:00' and '19:00'

This dst table seems like it would more than likely become a maintenance nightmare. So, anybody have a better option?

UPDATE Well, there seems to be some confusion on my question, so I'll provide some sample data...

First, note that DST ends in the US at 02:00 local time on Sunday, Nov. 6th.

Given the following table of events

create table events(ID int, timeGMT datetime)

insert into events(ID, timeGMT)
select  1, '2011-11-04 20:00' union --16:00 EDT
select  2, '2011-11-04 20:15' union --16:15 EDT
select  3, '2011-11-04 20:30' union --16:30 EDT
select  4, '2011-11-04 20:45' union --16:45 EDT
select  5, '2011-11-04 21:00' union --17:00 EDT
select  6, '2011-11-04 21:15' union --17:15 EDT
select  7, '2011-11-04 21:30' union --17:30 EDT
select  8, '2011-11-04 21:45' union --17:45 EDT
select  9, '2011-11-04 22:00' union --18:00 EDT
select 10, '2011-11-04 22:15' union --18:15 EDT
select 11, '2011-11-04 22:30' union --18:30 EDT
select 12, '2011-11-04 22:45' union --18:45 EDT
select 13, '2011-11-04 23:00' union --19:00 EDT
select 14, '2011-11-06 20:00' union --15:00 EST
select 15, '2011-11-06 20:15' union --15:15 EST
select 16, '2011-11-06 20:30' union --15:30 EST
select 17, '2011-11-06 20:45' union --15:45 EST
select 18, '2011-11-06 21:00' union --16:00 EST
select 19, '2011-11-06 21:15' union --16:15 EST
select 20, '2011-11-06 21:30' union --16:30 EST
select 21, '2011-11-06 21:45' union --16:45 EST
select 22, '2011-11-06 22:00' union --17:00 EST
select 23, '2011-11-06 22:15' union --17:15 EST
select 24, '2011-11-06 22:30' union --17:30 EST
select 25, '2011-11-06 22:45' union --17:45 EST
select 26, '2011-11-06 23:00'       --18:00 EST

I'm looking for a good way of getting the following results. Assuming the local start time of 17:00, the local end time of 18:00, and the local timezone being US-Easter is all provided.

 ID | timeGMT
----|------------------
  5 | 2011-11-04 21:00
  6 | 2011-11-04 21:15
  7 | 2011-11-04 21:30
  8 | 2011-11-04 21:45
  9 | 2011-11-04 22:00
 22 | 2011-11-06 22:00
 23 | 2011-11-06 22:15
 24 | 2011-11-06 22:30
 25 | 2011-11-06 22:45
 26 | 2011-11-06 23:00

I also want this to work for any real set of DST rules and all timezones. Including the fact that the real dataset spans several years, and thus several DST shifts.

UPDATE开发者_高级运维 2 I've basically implemented the solution that I originally outlined, but I've also created some code to drastically reduce the required maintenance operations.

1. I parse the tz database (aka. zoneinfo, IANA Time Zone, or Olson database, available here), outputting a list of all GMT offset shifts, for all zones for the years I have to worry about.
2. Insert the list into a temp table.
3. Use the temp table to build time ranges for each zone for each GMT offset it has.

---

Well, just a small idea that you may want to consider:

Instead of converting the data in your table(s) during a query, you can reverse the logic and instead convert the user's local time range parameters to GMT and then do everything in GMT. If you want results back that are in local time, you can convert again at that point.

Whether this is an effective solution or not will depend on the nature of your queries.

---

In such situations, If we are calling this procedure from the front we normally pass in the current GMT time, in case of .net we use System.DateTime.UtcNow and then compare the data, using this approach we don't have to do the datetime conversion in the sql server.

Edited:

    declare @table table(eventName Varchar(50), [time] time)
    insert into @table(eventName, [time]) Values ('event 1', '03:30')
    insert into @table(eventName, [time]) Values ('event 1', '04:00')
    insert into @table(eventName, [time]) Values ('event 2', '04:20')
    insert into @table(eventName, [time]) Values ('event 3', '05:20')
    insert into @table(eventName, [time]) Values ('event 3', '07:20')

    select * from @table

    -- assuming ur local time is +1 GMT
    declare 
        @timeFromLocal time = '05:00',
        @timeToLocal time = '07:00',
        @timeFromGMT time,
        @timeToGMT time,
        @Offset int = -1

    /*I prefer doing the following conversion from the front end where the time reflects the users time zone*/
    set @timeFromGMT = DATEADD(Hour, @Offset, @timeFromLocal)
    set @timeToGMT = DATEADD(Hour, @Offset, @timeToLocal)

    select * from @table Where [time] between @timeFromGMT and @timeToGMT