Databases
StackOverflow2013
Postsdbo
POFlooring a unix timestamp with timezone support
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFlooring a unix timestamp with timezone support
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USPeter Schuller
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFlooring a unix timestamp with timezone support
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POFlooring a unix timestamp with timezone support
      1. USevilscary
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload