By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,723 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,723 IT Pros & Developers. It's quick & easy.

Sort by time (hh:mm:am/pm - format) in text string

P: n/a
Hi,

I have a group of records that I need to sort by time field. The time
field is a text string (should've been a date/time - I know -doh!) I
was wondering if anyone knew how to write a query to sort in Ascending
order with the earliest appearing first.

The times are stored as follows:

10:30pm
11:30pm

etc.

Many, many thanks to anyone who can help with this one. Cheers.
Jul 17 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
00steve wrote:
Hi,

I have a group of records that I need to sort by time field. The time
field is a text string (should've been a date/time - I know -doh!) I
was wondering if anyone knew how to write a query to sort in Ascending
order with the earliest appearing first.

The times are stored as follows:

10:30pm
11:30pm


Try something built from this MySQL query :

select time_format(time, '%I:%i%p') as the_time from timetest order by
the_time asc;

----------------------- Sample output ----------------------------
mysql> select * from timetest;
+---------+
| time |
+---------+
| 10:15pm |
| 12:14pm |
| 12:15am |
| 9:30am |
+---------+
4 rows in set (0.00 sec)

mysql> select time_format(time, '%I:%i%p') as the_time from timetest
order by the_time asc;
+----------+
| the_time |
+----------+
| 09:30AM |
| 10:15AM |
| 12:14PM |
| 12:15PM |
+----------+
4 rows in set (0.00 sec)
----------------------------- ends --------------------------------

Hope this helps,

Regards,

Andy

Jul 17 '05 #2

P: n/a
Andy Barfield wrote:

Hmmmm - someone goofed - sorry :(
Jul 17 '05 #3

P: n/a
"00steve" <29****@tay.ac.uk> wrote in message
news:df**************************@posting.google.c om...
Hi,

I have a group of records that I need to sort by time field. The time
field is a text string (should've been a date/time - I know -doh!) I
was wondering if anyone knew how to write a query to sort in Ascending
order with the earliest appearing first.

The times are stored as follows:

10:30pm
11:30pm

etc.

Many, many thanks to anyone who can help with this one. Cheers.


Something like this should work:

SELECT * FROM classes ORDER BY CAST(start_time AS DATETIME)

Jul 17 '05 #4

P: n/a
00steve wrote:
I have a group of records that I need to sort by time field. The time
field is a text string (should've been a date/time - I know -doh!) I
was wondering if anyone knew how to write a query to sort in Ascending
order with the earliest appearing first.

The times are stored as follows:

10:30pm
11:30pm


If the records are in a database, then make the database work for you
as described by Andy in previous article. If you just have a bunch of
dates, then the mktime() function can help you convert that into UNIX
time. Put those in an array, and then sort that according to whatever
principle you want. Then using date(), you can display the times in
more human readable form.

/Marcin
Jul 17 '05 #5

P: n/a
Marcin Dobrucki wrote:
00steve wrote:
The times are stored as follows:

10:30pm
11:30pm

If the records are in a database, then make the database work for you
as described by Andy in previous article. If you just have a bunch of
dates, then the mktime() function can help you convert that into UNIX
time. Put those in an array, and then sort that according to whatever
principle you want. Then using date(), you can display the times in
more human readable form.

/Marcin


I was caught by the am/pm marker - which the TIME_FORMAT function
ignores - I could do it in PHP with no probems, but this one had me
pulling what little hair I have out by the roots! How big is the
database? What would be the work involved in converting it - could you
simply alter the table and add a date/time field to the database and
then run a utility that would populate it from the original time field?

Just some ideas - good luck steve!

Jul 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.