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

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.

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

00steve wrote:
[color=blue]
> 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[/color]

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

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

Andy Barfield wrote:

Hmmmm - someone goofed - sorry :(

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

"00steve" <298sam@tay.ac.uk> wrote in message
news:dfedf0ce.0406230925.67ccfce6@posting.google.c om...[color=blue]
> 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.[/color]

Something like this should work:

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

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

00steve wrote:
[color=blue]
> 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[/color]

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

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

Marcin Dobrucki wrote:[color=blue]
> 00steve wrote:[color=green]
>> The times are stored as follows:
>>
>> 10:30pm
>> 11:30pm[/color]
>
>
> 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[/color]

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!