List:General Discussion« Previous MessageNext Message »
From:Larry McGhaw Date:July 15 2011 5:40pm
Subject:RE: Substring confusion.
View as plain text  
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

Thread
Substring confusion.Paul Halliday15 Jul
  • RE: Substring confusion.Larry McGhaw15 Jul