Nate Nate - 1 month ago 18
MySQL Question

How to group rows with a Unix timestamp by week?

I currently use the following query to get data grouped by day:

SELECT DATE(from_unixtime(timestampcolumn)) as date, COUNT(*)
FROM db.table
WHERE timestampcolumn BETWEEN :startTime AND :endTime
GROUP BY DATE(from_unixtime(timestampcolumn))
ORDER BY timestampcolumn


The
DATE()
function returns the string
YYYY-MM-DD
, so the above query is simple and works great for getting data grouped by each day, but how can I modify it to return data grouped by each week?




In response to Jonathan's answer:

I tried making an example in SQL Fiddle, but the
DATE()
SQL function does not work in SQL Fiddle for some reason (it simply doesn't work in SQL Fiddle, but it does on both my live and wamp servers).

So here's an example you can try out if you'd like:

SETUP:

CREATE TABLE example(id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), timestamp INT, data INT);

INSERT INTO example (timestamp, data) VALUES (1355400000, 1);
INSERT INTO example (timestamp, data) VALUES (1355659200, 1);
INSERT INTO example (timestamp, data) VALUES (1357694861, 1);
INSERT INTO example (timestamp, data) VALUES (1355918400, 1);
INSERT INTO example (timestamp, data) VALUES (1356955200, 1);
INSERT INTO example (timestamp, data) VALUES (1358510400, 1);
INSERT INTO example (timestamp, data) VALUES (1358769600, 1);
INSERT INTO example (timestamp, data) VALUES (1358769600, 1);
INSERT INTO example (timestamp, data) VALUES (1371824368, 1);
INSERT INTO example (timestamp, data) VALUES (1371833476, 1);
INSERT INTO example (timestamp, data) VALUES (1371840324, 1);
INSERT INTO example (timestamp, data) VALUES (1371850523, 1);
INSERT INTO example (timestamp, data) VALUES (1371863191, 1);
INSERT INTO example (timestamp, data) VALUES (1371865401, 1);
INSERT INTO example (timestamp, data) VALUES (1371872379, 1);
INSERT INTO example (timestamp, data) VALUES (1372006190, 1);
INSERT INTO example (timestamp, data) VALUES (1372051945, 1);
INSERT INTO example (timestamp, data) VALUES (1372189402, 1);
INSERT INTO example (timestamp, data) VALUES (1372207830, 1);
INSERT INTO example (timestamp, data) VALUES (1372229733, 1);
INSERT INTO example (timestamp, data) VALUES (1372350338, 1);
INSERT INTO example (timestamp, data) VALUES (1372358259, 1);


QUERY:

SELECT DATE(from_unixtime(timestamp)) as date, COUNT(*)
FROM example
WHERE timestamp BETWEEN 0 AND 9999999999999999999999999999999
GROUP BY DATE(from_unixtime(timestamp))
ORDER BY timestamp


OUTPUT:

2012-12-13 1
2012-12-16 1
2012-12-19 1
2012-12-31 1
2013-01-08 1
2013-01-18 1
2013-01-21 2
2013-06-21 7
2013-06-23 1
2013-06-24 1
2013-06-25 2
2013-06-26 1
2013-06-27 2


Now, dividing the time stamp by
(7 * 24 * 3600)
.

QUERY:

SELECT DATE(from_unixtime(timestamp / (7 * 24 * 3600))) as date, COUNT(*)
FROM example
WHERE timestamp BETWEEN 0 AND 9999999999999999999999999999999
GROUP BY DATE(from_unixtime(timestamp / (7 * 24 * 3600)))
ORDER BY timestamp


OUTPUT:

1969-12-31 22

Answer

One basic idea should be to group by the integer value of the time stamp divided by the number of seconds in a week: timestampcolumn / (7 * 24 * 3600)

You then need to consider edge effects and time zones:

  • Was 1970-01-01 on the appropriate day of the week (it was a Thursday, so probably not).
  • When exactly does the week start for your given data; is it affected by winter and summer time (standard and daylight saving time)?

You can deal with those by adding appropriate values to the time stamp column before dividing. One final twist: some systems might compensate for leap seconds. POSIX doesn't. You'll have to decide whether that matters to you.

Another option to consider is whether there is a way to format the date as a week value (e.g. ISO 8601 notation such as 2013-W23 for week 23 of 2013). You can then simply group by that string.


This is what I meant:

SELECT timestamp / (7 * 24 * 3600) AS weekno, COUNT(*)
  FROM example
 WHERE timestamp BETWEEN 0 AND 9999999999999999999999999999999
 GROUP BY timestamp / (7 * 24 * 3600)
 ORDER BY weekno

You might be able to use GROUP BY weekno, or you might have to ORDER BY timestamp / (7 * 24 * 3600), but you manufacture the week number. If you need to produce a date too, then you use:

SELECT timestamp / (7 * 24 * 3600) AS weekno,
       DATE(FROM_UNIXTIME((7 * 24 * 3600) * INT(timestamp / (7 * 24 * 3600))))) AS weekstart,
       COUNT(*)
  FROM example
 WHERE timestamp BETWEEN 0 AND 9999999999999999999999999999999
 GROUP BY timestamp / (7 * 24 * 3600)
 ORDER BY weekno

You could probably also use MIN(DATE(FROM_UNIXTIME(timestamp))) for weekstart, unless you think the first day of the week won't always be represented in the data.