title: Flooring a unix timestamp with timezone support
body: <p>I have a table, where each row has a timestamp. The timestamp is a unix timestamp. I'd like to do some reporting using this data, which requires me to group this data by a specific period (for now, let's just say day: so 86400 seconds). This can be achieved by flooring the timestamp, i.e:</p>
  
  <pre><code>mysql&gt; set time_zone='+00:00';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; select NOW(), FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP()/86400)*86400);
  +---------------------+----------------------------------------------------+
  | NOW()               | FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP()/86400)*86400) |
  +---------------------+----------------------------------------------------+
  | 2013-01-17 06:06:37 | 2013-01-17 00:00:00                                |
  +---------------------+----------------------------------------------------+
  1 row in set (0.01 sec)
  </code></pre>
  
  <p>This allows me to do the following data rollup:</p>
  
  <pre><code>select FROM_UNIXTIME(FLOOR(timestamp/86400)*86400) groupts, count(some_id) from table group by groupts order by groupts;
  </code></pre>
  
  <p>All of this works perfectly, now I'd like to compare the data with some other report, where I only have the data in a specific timezone, let's say its EST.</p>
  
  <p>Unfortunately (and obviously), flooring doesn't work anymore:</p>
  
  <pre><code>mysql&gt; set time_zone='-05:00';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql&gt; select NOW(), FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP()/86400)*86400);
  +---------------------+----------------------------------------------------+
  | NOW()               | FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP()/86400)*86400) |
  +---------------------+----------------------------------------------------+
  | 2013-01-17 01:10:38 | 2013-01-16 19:00:00                                |
  +---------------------+----------------------------------------------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>Which finally brings me to my question:
  Is there any way to floor the timestamp based on a specific timezone? i.e, flooring will ensure all values from 00:00-23:59 in that timezone end up in a specific group?</p>
  
  <p>Thanks!</p>