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.halliday@stripped]
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=1
