Does anyone know why this happens:
mysql> SELECT COUNT(*) AS count, SUBSTRING(timestamp,12,5) AS time
FROM event WHERE timestamp BETWEEN '2011-07-15 03:00:00' AND
'2011-07-16 02:59:59' GROUP BY time ORDER BY time limit 4;
+-------+-------+
| count | time |
+-------+-------+
| 5 | 03:00 |
| 2 | 03:01 |
| 2 | 03:02 |
| 5 | 03:03 |
+-------+-------+
mysql> SELECT COUNT(*) AS count, SUBSTRING(timestamp,12,5) AS time
FROM event WHERE timestamp BETWEEN '2011-07-14 03:00:00' AND
'2011-07-15 02:59:59' GROUP BY time ORDER BY time limit 4;
+-------+-------+
| count | time |
+-------+-------+
| 8 | 00:00 |
| 4 | 00:01 |
| 3 | 00:02 |
| 1 | 00:03 |
+-------+-------+
First one returns what I expect, when I do it on a day in the past, it
always starts at 00:00 instead of 03:00.
If I change it a bit though, I get what I expect:
mysql> SELECT COUNT(*) AS count, SUBSTRING(timestamp,10,7) AS time
FROM event WHERE timestamp BETWEEN '2011-07-14 03:00:00' AND
'2011-07-15 02:59:59' GROUP BY time ORDER BY time limit 4;
+-------+---------+
| count | time |
+-------+---------+
| 4 | 4 03:00 |
| 6 | 4 03:01 |
| 1 | 4 03:02 |
| 2 | 4 03:03 |
+-------+---------+
Why is this?
Thanks.
--
Paul Halliday
http://www.squertproject.org/
