mysql 查询 - 输出格式日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4596457/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql query - format date on output?
提问by newbie-shame
In my table, dates are stored like this: 2011-03-03T13:30:00
在我的表中，日期是这样存储的：2011-03-03T13:30:00
I'm trying to output dates like this: March 3, 2011 1:30 PM
我正在尝试输出这样的日期：2011 年 3 月 3 日下午 1:30
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?
我更愿意将它处理到查询中，而不是使用 php 对其进行格式化，但我在这样做时遇到了一些困难。尝试 DATE_FORMAT 的各种迭代，但它没有给我想要的东西，也许是因为它的存储方式？
回答by álvaro González
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.
在处理日期时，您基本上有两种不同的操作可能需要执行：日期到字符串，反之亦然。您可以使用的功能是DATE_FORMAT()和STR_TO_DATE()。完整的参考可以在手册中找到。
Usage example:
用法示例：
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')
回答by moinudin
回答by John K.
The MySQL date storage format is actually YYYY-MM-DD, but using the str_to_date() and date_format() functions you can accept and generate any date format required.
MySQL 日期存储格式实际上是 YYYY-MM-DD，但是使用 str_to_date() 和 date_format() 函数您可以接受并生成所需的任何日期格式。
select DATE_FORMAT(DateTable.MyDate,'%d %b %y')
from DateTable
would return
04 Nov 08
回答by John Parker
You should really use a DATETIME field for such things (and clean the input on the way in) rather than having to sort this out at the point of output.
您真的应该将 DATETIME 字段用于此类事情（并在输入时清理输入），而不必在输出点对其进行排序。
Irrespective, you can simply use the DATE_FORMATfunction to re-format your field into the format you require, although this might produce some un-expected results on a VARCHAR or CHAR field. (If so, you'll have to use STR_TO_DATEor failing that some various string functionsto extract the date bits you require.)
无论如何，您可以简单地使用DATE_FORMAT函数将您的字段重新格式化为您需要的格式，尽管这可能会在 VARCHAR 或 CHAR 字段上产生一些意外的结果。（如果是这样，您将不得不使用STR_TO_DATE或无法使用某些各种字符串函数来提取您需要的日期位。）
