the new field - time - is a character field, length of 5, and your order by will sort the data as such - so "00:..." is before "03:..." The only reason why you don't have "00:.." times when you use a timestamp between <today at 3am> and <tomorrow at 3am>, is because there are no times that start with "00:" in the criteria .. those times will occur tomorrow.
-----Original Message----- From: Paul Halliday [mailto:paul.halli...@gmail.com] Sent: Friday, July 15, 2011 9:53 AM To: mysql mailing list Subject: Substring confusion. 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=lmcg...@connx.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
