PHP/SQL : Sorting

Question

I have field(time) with data like this:

9:00-11:00 am
7:00-9:00 am
6:30-7:30 pm
1:00-2:30 pm

Select * from table order by time ASC result:

1:00-2:30 pm
6:30-7:30 pm
7:00-9:00 am
9:00-11:00 am

I cant sort it correctly. Any help will do.

Thanks

Answer 1

Store your times in two separate TIME fields (start_time and end_time for example), which can reasonably be sorted by the database. Just storing a time range as text doesn't tell the database anything, it can't magically understand what it means.

Answer 2

Store your date ranges in two TIMESTAMP columns in MySQL, then you can sort on the columns however you want. You can even do more advanced sorts like sorting on the duration between the two timestamps easily using MySQL.

Answer 3

I am not recommending that you do this, but to show you why you'd want to split this up into multiple columns as @deceze recommended.

You can use mysql's STR_TO_DATE() to (kindof) convert the string to a date. Note that it completely ignores the first part of the time range, so it's effectively sorting on:

11:00 am
9:00 am
7:30 pm
2:30 pm

The query is:

SELECT 
  time, 
  STR_TO_DATE(time,'%h:%i-%h:%i %p') 
FROM time_table 
ORDER BY STR_TO_DATE(time,'%h:%i-%h:%i %p') ASC

A second query that effectively sorts on this:

9:00am
7:00am
6:30pm
1:00pm

is:

SELECT 
  time, 
  STR_TO_DATE(CONCAT(SUBSTRING_INDEX(time,'-',1), SUBSTRING(time,LOCATE(' ', time)+1)), '%h:%i%p') 
FROM time_table
ORDER BY STR_TO_DATE(CONCAT(SUBSTRING_INDEX(time,'-',1), SUBSTRING(time,LOCATE(' ', time)+1)), '%h:%i%p') ASC

(I'm certain someone that's more proficient with regular expressions would be able to shorten this one).