按当地时间查询事件

发布于 2024-12-06 17:33:43 字数 2687 浏览 0 评论 0原文

我有一个带有 GMT 时间戳事件的数据库（SQL Server 2005，但我认为我的问题更一般）。由于多种原因，我需要能够根据用户的当地时间查询和聚合数据。例如，当地时间下午 5 点到 7 点之间发生了多少事件？ DST 确实让我对这个问题感到困惑。

我考虑过尝试维护所有时区/夏令时规则的表格。然后我可以将我的事件表加入其中，将结果限制为用户的时区/夏令时信息。因此，我的示例的查询将类似于：

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 表看起来很可能会成为维护噩梦。那么，有人有更好的选择吗？

更新嗯，我的问题似乎有些混乱，所以我将提供一些示例数据...

首先，请注意，DST 在美国当地时间 11 月 6 日星期日 02:00 结束。

鉴于下表的事件，

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

我正在寻找一种获得以下结果的好方法。假设本地开始时间为 17:00，本地结束时间为 18:00，本地时区为 US-Easter。

 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

我还希望它适用于任何真正的 DST 规则集和所有时区。包括实际数据集跨越数年的事实，因此存在数次夏令时转变。

更新2 我基本上已经实现了最初概述的解决方案，但我还创建了一些代码来大大减少所需的维护操作。

我解析 tz 数据库（又名 zoneinfo、IANA 时区或 Olson 数据库，可在此处获取），输出我必须担心的年份中所有区域的所有 GMT 偏移量列表。
将列表插入临时表中。
使用临时表为每个区域的每个 GMT 偏移量构建时间范围。

原文

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.

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.
Insert the list into a temp table.
Use the temp table to build time ranges for each zone for each GMT offset it has.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

偏闹i 2024-12-13 17:33:43

好吧，这只是您可能需要考虑的一个小想法：

您可以反转逻辑，将用户的本地时间范围参数转换为 GMT，然后以 GMT 执行所有操作，而不是在查询期间转换表中的数据。如果您想要返回当地时间的结果，您可以在此时再次转换。

这是否是一个有效的解决方案将取决于您的查询的性质。

回复收藏 0 原文

猫弦 2024-12-13 17:33:43

在这种情况下，如果我们从前面调用这个过程，我们通常会传入当前的 GMT 时间，如果是 .net，我们使用 System.DateTime.UtcNow 然后比较数据，使用这种方法我们不必这样做sql server 中的日期时间转换。

编辑：

    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

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

回复收藏 0 原文

~没有更多了~