如何按日期分组，计时时区和DST？ [英] How to group by date, accounting for timezones and DST?

问题描述

我有一堆事件存储在我的数据库中。每个事件都有开始时间，以UTC为单位存储为 DATETIME 。每个用户都可以选择他/她自己的时区。

我想显示一个日历，只显示每月的每一天的事件数。我不能 GROUP BY DATE（start_dt），因为用户的一天不会在00:00:00 UTC开始。此外，用户的时区可能在本月中旬有一个DST开关。

我唯一的选择是获取给定月份的每个单个事件，然后组和用我的服务器端语言来计数？

解决方案

你可以尝试

  SELECT ... FROM ... GROUP BY DATE（CONVERT_TZ（start_dt，'UTC'，'America / Vancouver'））
  / pre> 
 
 
请注意，您需要将时区数据加载到MySQL 在此之前将工作。
 
I have a bunch of events stored in my database. Each event has a start time, stored as DATETIME in UTC. Each user can choose his/her own timezone.

I want to display a calendar which just shows the number of events on each day of the month. I can't GROUP BY DATE(start_dt) because the user's day does not [necessarily] start at 00:00:00 UTC. Moreover, the user's timezone may have a DST switch in the middle of the month.

Is my only option then to fetch every single event for the given month and then group and count them with my server-side language?
解决方案
You can try 
SELECT ... FROM ... GROUP BY DATE(CONVERT_TZ(start_dt,'UTC','America/Vancouver'))
Note that you need to load the timezone data into MySQL before this will work.

                        
这篇关于如何按日期分组，计时时区和DST？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！