在 MySQL SELECT 中格式化日期为 ISO 8601
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9321809/
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
Format date in MySQL SELECT as ISO 8601
提问by Adam
I'm trying to grab the date from my database in a standard timestamp and display it as ISO 8601. I'm unable to easily do it in PHP so I'm trying to do it in my SELECT statement. This is what I have, but it displays an error:
我正在尝试以标准时间戳从我的数据库中获取日期并将其显示为 ISO 8601。我无法在 PHP 中轻松执行此操作，因此我尝试在我的 SELECT 语句中执行此操作。这是我所拥有的，但它显示一个错误：
SELECT * FROM table_name ORDER BY id DESC DATE_FORMAT(date,"%Y-%m-%dT%TZ")
What am I doing wrong?
我究竟做错了什么？
回答by ypercube??
The DATE_FORMAT(DateColumn)has to be in the SELECTlist:
本DATE_FORMAT(DateColumn)必须在SELECT列表：
SELECT DATE_FORMAT(date, '%Y-%m-%dT%TZ') AS date_formatted
FROM table_name
ORDER BY id DESC
回答by ofumbi
This worked for me
这对我有用
DATE_FORMAT( CONVERT_TZ(`timestamp`, @@session.time_zone, '+00:00') ,'%Y-%m-%dT%TZ')
回答by Professor Falken
DATE_FORMAT only works on MySQL date columns, not timestamps.
DATE_FORMAT 仅适用于 MySQL 日期列，不适用于时间戳。
A UNIX timestamp is an integer containing the number of seconds since Jan 1, 1970 UTC. To format this as an ISO 8601 date you need to use the FROM_UNIXTIME() function instead.
UNIX 时间戳是一个整数，包含自 1970 年 1 月 1 日 UTC 以来的秒数。要将其格式化为 ISO 8601 日期，您需要改用 FROM_UNIXTIME() 函数。
FROM_UNIXTIME takes the same format strings as DATE_FORMAT, so to format a column named 'created' you'd:
FROM_UNIXTIME 采用与 DATE_FORMAT 相同的格式字符串，因此要格式化名为“created”的列，您需要：
SELECT created /* e.g. 1288799488 */ ,
FROM_UNIXTIME(created,'%Y-%m-%dT%TZ') /* e.g. 2010-11-03T08:51:28Z */
FROM table_name
回答by AL the X
Loading the date field from the database and converting it to ISO format with PHP is straight-forward; see the cformat string to PHP date: http://www.php.net/manual/en/function.date.php
从数据库加载日期字段并使用 PHP 将其转换为 ISO 格式非常简单；请参阅cPHP的格式字符串date：http: //www.php.net/manual/en/function.date.php
echo date('c'); // expected "2013-03-08T14:45:37+05:00"
回答by Niet the Dark Absol
Why is it hard to do it in PHP?
为什么在 PHP 中很难做到？
date("Y-m-d\TH:i:sO",strtotime($sqldata['time']));
Anyway, that DATE_FORMATneeds to be in the fields to select, not tacked on to the end.
无论如何，这DATE_FORMAT需要在要选择的字段中进行，而不是添加到最后。
回答by Andreas Stokholm
You should move the DATE_FORMAT to the select part of your query like this:
您应该将 DATE_FORMAT 移动到查询的选择部分，如下所示：
SELECT *, DATE_FORMAT(date,"%Y-%m-%dT%TZ") AS date FROM table_name ORDER BY id DESC
回答by Andreas Stokholm
DATE_FORMAT(date, '%Y-%m-%dT%TZ')should be in selectclause.
DATE_FORMAT(date, '%Y-%m-%dT%TZ')应该在select子句中。
SELECT DATE_FORMAT(date, '%Y-%m-%dT%TZ')
FROM table_name
ORDER BY id DESC
