asked on

Cast time in Mysql

I have a database that has start_time and am_pm in different columns stored as text.. I am trying to cast it as time and sort it.. by time..
also select distinct hours...
How ever it does not work can someone help?
I have
CAST(CONCAT(start_time, ' ', ampm) AS TIME) AS myTime
ORDER BY myTime
select distinct HOUR(myTime) from `crs_sec_schedule_reader` order by ampm

leakim971

Hello starter,

Please post some text values you see in the database (so not converted)

Regards.

l_starter_l

ASKER

The values are stored as
in table start_time
07:15
05:30
12:15
01:20

in am_pm
Am
Pm

I also tried to do this

SELECT DISTINCT Hour( CAST( CONCAT( start_time, ' ', am_pm ) AS TIME ) ) AS myTime
FROM crs_sec_schedule_reader ORDER BY myTime

Open in new window

leakim971

Try :

SELECT DISTINC STR_TO_DATE(CONCAT(start_time, ':00 ', am_pm),"%r") AS myTime FROM crs_sec_schedule_reader ORDER BY myTime

Open in new window

l_starter_l

ASKER

I need to select only the hours..

leakim971

So :

SELECT DISTINC HOUR(STR_TO_DATE(CONCAT(start_time, ':00 ', am_pm),"%r")) AS myTime FROM crs_sec_schedule_reader ORDER BY myTime

Open in new window

leakim971

I play with this :

I get 0 and 12

select HOUR(STR_TO_DATE(CONCAT('12:23',':00 ','AM'),"%r")), HOUR(STR_TO_DATE(CONCAT('12:23',':00 ','PM'),"%r")) from dual

Open in new window

l_starter_l

ASKER

when I do
SELECT DISTINCT HOUR(STR_TO_DATE(CONCAT(start_time, ':00 ', am_pm),"%r")) AS myTime FROM crs_sec_schedule_reader ORDER BY myTime
It list 7 to 23..
I need no military time..just am_pm..

l_starter_l

ASKER

how do i convert it back to regular time..

l_starter_l

ASKER

also maintaining regular time sorting e.g. 12pm come before 1 pm..

leakim971

With DATE_FORMAT :

SELECT DISTINCT DATE_FORMAT(STR_TO_DATE(CONCAT(start_time, ':00 ', am_pm),"%r"),"%r") AS myTime FROM crs_sec_schedule_reader ORDER BY myTime

Open in new window

leakim971

Have a look here for the DATE_FORMAT section here : http://dev.mysql.com/doc/refman/5.0/fr/date-and-time-functions.html

Once you get a DATE with (STR_TO_DATE(CONCAT(start_time, ':00 ', am_pm),"%r") you can format as you want, look the available formats and just put it a second parameter or a combinaison of them

leakim971

For example you have this in your table :
start_time | am_pm
07:15 | AM
05:30 | PM
12:15 | PM
01:20 | AM

Could you post what you want it display ?
This ?
07:15 AM
05:30 PM
12:15 PM
01:20 AM

You should do your ORDER with the military one and display what you want

l_starter_l

ASKER

yes I would like it to display like %r

7:00 am
8:00 am etc..

I need it to only display the hour values though..that is wht i used HOUR can you try to incorporate the into the query above

l_starter_l

ASKER

The hour extracts the hour and displays it as int from 1 to 23...
How do I display the int as 7:00 am etc..

leakim971

>yes I would like it to display like %r
7:00 am
8:00 am etc..

This is hour AND minute but no second :

>How do I display the int as 7:00 am

SELECT DISTINCT DATE_FORMAT(STR_TO_DATE(CONCAT(start_time, ':00 ', am_pm),"%r"),"%I:%i %p") AS myTime FROM crs_sec_schedule_reader

Open in new window

l_starter_l

ASKER

ok but this still doesn't solve my problem..I only need the distinct hours...
e.g.
lets say I have
5:15
,6:15,
6:13,
7:15.
12:20pm
1:30pm
etc...
I only need to display the time distinct hours in order..e.g.
5:00
6:00
7:00
12:00
1:00
not the minutes only the distinct hours beginning from the first one to the last time in order...

leakim971

Why not :

SELECT DISTINCT DATE_FORMAT(STR_TO_DATE(CONCAT(LEFT(start_time,2), ':00:00 ', am_pm),"%r"),"%I:%i %p") AS myTime FROM crs_sec_schedule_reader

Open in new window

l_starter_l

ASKER

SELECT DISTINCT DATE_FORMAT(STR_TO_DATE(CONCAT(LEFT(start_time,2), ':00:00 ', am_pm),"%r"),"%I:%i %p") AS myTime FROM crs_sec_schedule_reader

that works the time is displayed right format.but it is not in order..Is there a way to order it?

leakim971

Yes, ID:25910940 or :

SELECT DISTINCT DATE_FORMAT(STR_TO_DATE(CONCAT(LEFT(start_time,2), ':00:00 ', am_pm),"%r"),"%I:%i %p") AS myTime FROM crs_sec_schedule_reader ORDER BY myTime

Open in new window

ASKER CERTIFIED SOLUTION

leakim971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

l_starter_l

ASKER

k i changed it and did
SELECT DISTINCT DATE_FORMAT( STR_TO_DATE( CONCAT( LEFT( start_time, 2 ) , ':00:00 ', am_pm ) , "%r" ) , "%I:%i %p" ) AS myTime
FROM crs_sec_schedule_reader
ORDER BY HOUR( STR_TO_DATE( CONCAT( start_time, ':00 ', am_pm ) , "%r" ) )

and it worked for me..Thanks for your help

leakim971

You're welcome! Thanks for the points and see you soon!