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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
