[SOLVED] Time Sorting

[tobeyt23]

I have time stored in a DB as such: 7:30 AM, 4:00 PM and 1:00 PM when they come out they are showing 1:00 PM, 4:00 PM and 7:30 AM. How can i sort these correctly?

[Adam]

Need far more information than that pal!

 

What's the table structure like at the minute?

How are you entering them?

How are you retrieving them?

 

Adam

[PFMaBiSmAd]

That format cannot be directly be sorted. You need to use a TIME or a DATETIME data type.

[tobeyt23]

Table Structure:

id	int(10) unsigned	NO	PRI		auto_increment
private	int(1)	NO		0	
hidden	int(1)	NO		0	
title	varchar(200)	YES			
eDate	date	YES			
eDate_end	date	YES			
eTime	varchar(255)	YES			
eTime_note	varchar(255)	YES			
descr	text	YES			
visitor_name	varchar(255)	YES			
college_name	varchar(255)	YES			
location	varchar(255)	YES			
url	varchar(255)	YES			
file	varchar(255)	YES			
team_id	int(11)	YES			
opponent	varchar(255)	YES			
home_away	varchar(4)	YES			
depart_time	varchar(255)	YES			
return_time	varchar(255)	YES			
direction_id	int(11)	YES			
created_by	int(11)	YES			
date_created	datetime	YES			
updated_by	int(11)	YES			
date_updated	datetime	YES			
deleted	int(1)	NO		0	
deleted_by	int(11)	YES			
date_deleted	datetime	YES			

Query:

SELECT
e.id,
e.title,
e.eDate,
e.eDate_end,
e.eTime,
e.eTime,
e.eTime_note,
e.descr,
e.location,
e.url,
e.`file`
FROM
calendar_events e
WHERE
e.deleted =  '0' AND
e.hidden =  '0' AND
e.eDate >=  '2009-04-01' AND
e.eDate <=  '2009-04-30' AND
e.private =  '0'
ORDER BY
e.eDate ASC,
e.eTime,
e.title

 

data in eTime:

 

[tobeyt23]

Can these fields be converted to time types? So i don't lose the data.

[PFMaBiSmAd]

You can add a TIME type column and use the mysql STR_TO_DATE() function to produce a TIME value from your existing column data, then rename the columns and delete the old varchar column - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

[tobeyt23]

This is what i am doing but doesn't seem be getting the AM PM am i missing something:

 

SELECT
STR_TO_DATE(calendar_events.eTime,'%h:%i')
FROM
calendar_events

 

the data is store like this '8:30 AM' or '11:15 PM'

[PFMaBiSmAd]

The format string should be -

 

'%l:%i %p'