title: Grouping MySQL datetime into intervals irrespective of timezone
body: <p>This question has been asked <a href="https://stackoverflow.com/questions/4342370/grouping-into-interval-of-5-minutes-within-a-time-range">before</a> but I am facing a slightly different problem.</p>
  
  <p>I have a table which logs events and stores their timestamps (as datetime). I need to be able to break up time into chunks and get number of events that occurred in that interval. The interval can be custom (Say from 5 minutes to 1 hour and even beyond).</p>
  
  <p>The obvious solution is to convert the datetime to unix_timestamp divide it by number of seconds in the interval, take its floor function and multiply it back by the number of seconds. Finally convert the unix_timestamp back to the datetime format.</p>
  
  <p>This works fine for small intervals. </p>
  
  <pre><code>select 
  from_unixtime(floor(unix_timestamp(event.timestamp)/300)*300) as start_time,
  count(*) as total 
  from event 
  where timestamp&gt;='2012-08-03 00:00:00' 
  group by start_time;
  </code></pre>
  
  <p>This gives the correct output</p>
  
  <pre><code>+---------------------+-------+
  | start_time          | total |
  +---------------------+-------+
  | 2012-08-03 00:00:00 |    11 |
  | 2012-08-03 00:05:00 |     4 |
  | 2012-08-03 00:10:00 |     4 |
  | 2012-08-03 00:15:00 |     7 |
  | 2012-08-03 00:20:00 |     8 |
  | 2012-08-03 00:25:00 |     1 |
  | 2012-08-03 00:30:00 |     1 |
  | 2012-08-03 00:35:00 |     3 |
  | 2012-08-03 00:40:00 |     3 |
  | 2012-08-03 00:45:00 |     5 |
  ~~~~~OUTPUT SNIPPED~~~~~~~~~~~~
  </code></pre>
  
  <p>But if I increase the interval to say 1 hour (3600 sec)</p>
  
  <pre><code>mysql&gt; select from_unixtime(floor(unix_timestamp(event.timestamp)/3600)*3600) as start_time, count(*) as total from event where timestamp&gt;='2012-08-03 00:00:00' group by start_time;
  +---------------------+-------+
  | start_time          | total |
  +---------------------+-------+
  | 2012-08-02 23:30:00 |    35 |
  | 2012-08-03 00:30:00 |    30 |
  | 2012-08-03 01:30:00 |    12 |
  | 2012-08-03 02:30:00 |    18 |
  | 2012-08-03 03:30:00 |    12 |
  | 2012-08-03 04:30:00 |     4 |
  | 2012-08-03 05:30:00 |     3 |
  | 2012-08-03 06:30:00 |    13 |
  | 2012-08-03 07:30:00 |   269 |
  | 2012-08-03 08:30:00 |   681 |
  | 2012-08-03 09:30:00 |  1523 |
  | 2012-08-03 10:30:00 |   911 |
  +---------------------+-------+
  </code></pre>
  
  <p>The reason, as far as I could gauge, for the boundaries not being set properly is that unix_timestamp will convert time from my local timezone (GMT + 0530) to UTC and then output the numerical value. </p>
  
  <p>So a value like 2012-08-03 00:00:00 will actually be 2012-08-02 18:30:00. Dividing and using floor will set the minutes part to 00. But when I use from_unixtime, it will convert it back to GMT + 0530 and hence give me intervals that begin at 30 mins. </p>
  
  <p>How do I ensure the query works correctly irrespective of the timezone? I use MySQL 5.1.52 so to_seconds() is not available</p>
  
  <p><strong>EDIT:</strong>
  The query should also fire correctly irrespective of the interval (can be hours, minutes, days). A generic solution would be appreciated</p>