MySQL查询-在输出格式日期? [英] mysql query - format date on output?

问题描述

在我的表中，日期存储如下:2011-03-03T13:30:00

In my table, dates are stored like this: 2011-03-03T13:30:00

我正在尝试这样输出日期:2011年3月3日，下午1:30

I'm trying to output dates like this: March 3, 2011 1:30 PM

我宁愿将其用于查询中，而不要使用php对其进行格式化，但是这样做有些困难.尝试DATE_FORMAT的各种迭代，但是它没有给我我想要的东西，也许是因为它的存储方式是什么?

I'd much rather work it into the query rather than use php to format it, but I'm having some difficulty doing that. Trying various iterations of DATE_FORMAT, but it's not giving me what I want, maybe because of the way it's being stored?

推荐答案

在处理日期时，您基本上需要执行两个不同的操作:日期转换为字符串，反之亦然.您可以使用的功能是DATE_FORMAT()和STR_TO_DATE().可以在中找到完整参考.手册.

You basically have two different operations you may need to perform when handling dates: date to string and vice versa. The functions you can use are DATE_FORMAT() and STR_TO_DATE(). Full reference can be found in the manual.

用法示例:

SELECT
    DATE_FORMAT(CURRENT_TIMESTAMP, '%d/%m/%Y %H:%i:%s'),
    STR_TO_DATE('31/12/2001 23:55:00', '%d/%m/%Y %H:%i:%s')

如果您的日期不是真实的日期而是字符串，则需要将两次:从字符串转换为日期，然后再次从日期转换为字符串:

If your dates are not real dates but strings, you'll need to convert twice: from string to date and again from date to string:

SELECT
    STR_TO_DATE('2011-03-03T13:30:00', '%Y-%m-%dT%H:%i:%s'),
    DATE_FORMAT(STR_TO_DATE('2011-03-03T13:30:00', '%Y-%m-%dT%H:%i:%s'), '%M %e, %Y %l:%i %p')

这篇关于MySQL查询-在输出格式日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！