CODEWITHSUNDEEP
phpmysqlsortingdatetime
Jade Baribar
Jade Baribar

Reputation: 81

PHP/SQL : Sorting

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

Upvotes: 0

Views: 138

Answers (3)

hafichuk
hafichuk

Reputation: 10781

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).

Upvotes: 0

nickb
nickb

Reputation: 59699

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.

Upvotes: 0

deceze
deceze

Reputation: 522042

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.

Upvotes: 3

Related Questions