In my sample table is there some way to find out what the first 2 earliest times ever recorded is? I'm not after the earliest row recorded but the earliest based on H:i:s (not Y-m-d). In this case it would be row 3 and row 4 since it has a time of 4:07AM and 5:08AM, respectively, regardless of the date.
id first_name time
1 joe 2011-10-01 10:01:00
2 mary 2011-10-02 06:05:00
3 sam 2011-10-03 04:07:00
4 mel 2011-10-04 05:08:00
5 bo 2011-10-05 13:10:00
select time from table order by time(time) asc limit 2
You need to extract the time component from the date/time field (confusingly called "time"). Fortunately, MySQL has a function for this:
select time(`time`)
from t
order by time(`time`)
limit 2;
The other answers take a look at the whole DATETIME column, if you want the earliest time (H:m:s) you will need to run a DATE_FORMAT():
SELECT time FROM table ORDER BY DATE_FORMAT(time, '%H:%m:%s') ASC LIMIT 2;
You can also use TIME() instead: ORDER BY TIME(time) ASC. If you want to select the time (without a PHP function) you can use SELECT TIME(time) as time.
The answers with ORDER BY TIME(time) will fulfil your requirement and it's good if your requirement doesn't have to scale.
My MySQL explains me that it has to sort all rows for this query. If efficiency is a concern for you, you have to split your time row into two rows, date and timeonly. With an index on timeonly, there's no need to look at all rows.