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=gcdmg-mysql-2@m.gmane.org

RE: Substring confusion.

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 b=
etween and ,
is because there are no times that start with "00:" in the criteria .. thos=
e times will occur tomorrow.



-----Original Message-----
From: Paul Halliday [mailto:paul.halliday@gmail.com]=20
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.

--=20
Paul Halliday
http://www.squertproject.org/

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dlmcghaw@connx.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg