STR_TO_DATE

[yzerman]

I have a query that keeps failing.

 

I am using Mysql version 5.0.24a

 

Here is the table setup:

 

+---------------------+-------------------+

| date (timestamp)      | time (varchar 255) |

+---------------------+-------------------+

| 2008-04-25 00:00:00 | 10:00AM              |

+---------------------+-------------------+

 

Now the query:

SELECT *,STR_TO_DATE(meta_title, '%h:%m%p') as time, DATE_FORMAT(date, "%Y-%m-%d') from content ORDER BY DATE_FORMAT(date, '%Y-%m-%d'), STR_TO_DATE(time, '%h:%m%p') ASC

 

Ok, now I have rows with:

 

07:45AM

10:00AM

12:00PM

12:15PM

10:00PM

10:45PM

 

The rows that are on the hour, return as a properly formatted timestamp (10:00AM)

The rows that are inbetween hours, return as null strings.

 

Modifying the table structure is not possible, neither is storing the time in the date timestamp.

 

Is this a bug in MySQL or am I doing something wrong?

[fenway]

Why are you ordering by a dateformat expression? What do you actually want it sorted by?

[yzerman]

The date (timestamp %Y-%m-%d) and time (%h:%m%p). I thought that was obvious by the query.

 

Basically the problem I am running into is when I sort by the date, the time field does not get sorted. So I tried to sort by the date (as shown in the query) and the time (meta_title or time, I screwed up the query) so that events on the same date would be arranged by time (12AM to 12PM to 11:59PM)

[fenway]

The obvious question... you're storing time in a varchar, and you have a timestamp without a time!?!?

 

The only way to do this now, without fixing it, is to use STR_TO_TIME() to fix the time to proper TIME field, and then order by it.