List:General Discussion« Previous MessageNext Message »
From:Paul Halliday Date:July 15 2011 4:52pm
Subject:Substring confusion.
View as plain text  
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/
Thread
Substring confusion.Paul Halliday15 Jul
  • RE: Substring confusion.Larry McGhaw15 Jul