SELECT 2010-10-24 09:02:46 post as 'Oct 24, 2010 at 9:02AM'

Question

How can I convert my date field from mysql from 2010-10-24 09:02:46 to post on my site as 'Oct 24, 2010 at 9:02AM'

(SELECT TIME_FORMAT(`dPostTime`, '%b %e %l:%i %p')) as post_time 

This won't post the date, just the time.

Thanks so much!

Answer 1

The description of the TIME_FORMAT function says:

This is used like the DATE_FORMAT() function, but the format string may contain format specifiers only for hours, minutes, seconds, and microseconds.

So use the DATE_FORMAT function if you want to format the date as well.

Answer 2

SELECT DATE_FORMAT(dPostTime, '%b %e, %l:%i%p') AS post_time

To replace %b %e with "Today" :

SELECT 
    CASE WHEN DAY(dPostTime) = DAY(NOW()) THEN 
        DATE_FORMAT(dPostTime, 'Today at %l:%i%p') 
    ELSE 
        DATE_FORMAT(dPostTime, '%b %e, %l:%i%p') END AS post_time

Answer 3

I'd suggest the better option is to select it from the database as a timestamp value, and then use PHP's date() function to output the text value to the site. Timestamps are much easier to work with than formatted dates. Even if all you want to do is output it straight to the web page, I would still prefer to leave the formatting to PHP rather than SQL.

SQL:

SELECT UNIX_TIMESTAMP(dPostTime) as post_time ...

PHP:

print date('M d Y h:iA', $data['post_time']);