选择2010-10-24 09:02:46发表于'2010年10月24日上午9:02'

问题描述：

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!

如何将我的日期字段从mysql转换为2010-10-24 09:02:46发布到我的网站为'2010年10月24日上午9:02' p>

 （SELECT TIME_FORMAT（`dPostTime`，'％b％e％l：％i％p'））as as  post_time 
  code>  pre> 
 
 这不会发布日期，只发布时间。 p> 
 
 
非常感谢！ p> \  n  div>

答

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

答

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.

答

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']);

选择2010-10-24 09:02:46发表于'2010年10月24日上午9:02'

相关推荐