在MySQL中将日期格式化为ISO 8601格式的SELECT查询

Question

我试图从我的数据库中获取一个标准时间戳并将其显示为ISO 8601格式。因为我无法轻松地在PHP中实现它，所以我尝试在SELECT语句中实现。这是我拥有的内容，但它显示了一个错误：

SELECT * FROM table_name ORDER BY id DESC DATE_FORMAT(date,"%Y-%m-%dT%TZ")

我做错了什么？

- Adam

Answer 1

DATE_FORMAT(DateColumn) 必须在 SELECT 列表中:

SELECT DATE_FORMAT(date, '%Y-%m-%dT%TZ') AS date_formatted
FROM table_name 
ORDER BY id DESC 

- ypercubeᵀᴹ

---

在某些地区（例如西班牙），使用Z来表示+01:00时区不起作用，需要使用时间差。在PHP中，以下代码可以正常工作：date("c", strtotime($comment['datetime']))。 - David

对我也不起作用，最后的格式值类似于530Z，而不是+02:00。 - Daniel W.

@David，这是因为该函数假定DateTime是在UTC时间。 - sennett

@MladenJanjetović Z不是一个参数（否则它将被%前缀），它只是ISO 8601用于指定时区为UTC的约定。因此，如果您将日期时间存储在不同的时区中，请先将其转换为UTC，然后使用此格式字符串。最好使用UTC时区发送ISO 8601日期，而不是使用您自己的服务器时区。 - Domenico De Felice

虽然不完全是所问的问题，但谷歌把我带到了这里：要获得带有毫秒的ISO字符串格式，我需要使用DATE_FORMAT(date, '%Y-%m-%dT%T.000Z')。 - Aymeric Bouzy aybbyk

Answer 2

这对我起作用了

DATE_FORMAT( CONVERT_TZ(`timestamp`, @@session.time_zone, '+00:00')  ,'%Y-%m-%dT%TZ')

- ofumbi

---

这是正确的解决方案。假设UTC而没有进行时区转换是一个错误，会产生意想不到的结果。 - SnakeDrak

这将显示当前时间，OP要求基于date字段的时间。 - undefined

Answer 3

DATE_FORMAT只能用在MySQL的date列上，而不能用于timestamp。

UNIX timestamp是一个整数，包含自1970年1月1日UTC以来的秒数。为了将其格式化为ISO 8601日期，您需要使用FROM_UNIXTIME()函数。

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

- Professor Falken

Answer 4

从数据库加载日期字段并使用PHP将其转换为ISO格式非常简单；请参见PHP date的c格式字符串：http://www.php.net/manual/en/function.date.php

echo date('c'); // expected "2013-03-08T14:45:37+05:00"

- AL the X

---

那是一个PHP函数，不是MySQL函数。 - Christoffer Hammarström

OP 表示在 PHP 上执行操作时遇到了困难。我提供了答案的那一部分。 - AL the X

Answer 5

DATE_FORMAT(date, '%Y-%m-%dT%TZ') 应该放在 select 子句中。

SELECT DATE_FORMAT(date, '%Y-%m-%dT%TZ') 
FROM table_name 
ORDER BY id DESC 

- user319198

Answer 6

为什么在PHP中实现这个很难？

date("Y-m-d\TH:i:sO",strtotime($sqldata['time']));

无论如何，需要在选择的字段中输入 DATE_FORMAT ，而不是添加到末尾。

- Niet the Dark Absol

---

date("c", strtotime($sqldata['time'])); 的意思是使用 PHP 中的 date 函数，将 $sqldata['time'] 转换为 ISO 8601 格式的日期和时间字符串。 - chaim

仅作为旁注：如果使用PHP来转换日期，如果数据集非常大则可能会导致服务器内存问题。 - Erenor Paz

@GuntramBlohmsupportsMonica 或许不是，但是问题的提出者是，所以我提供了一个 PHP 的解决方案。 - Niet the Dark Absol

Answer 7

您应该将DATE_FORMAT移动到查询的选择部分，就像这样：

SELECT *, DATE_FORMAT(date,"%Y-%m-%dT%TZ") AS date FROM table_name ORDER BY id DESC

- Andreas Stokholm

Answer 8

另一种支持任何时区的DateTime格式化解决方案：

SELECT CONCAT(DATE_FORMAT( `date` ,'%Y-%m-%dT%T'), TIME_FORMAT( TIMEDIFF(NOW(), UTC_TIMESTAMP), '+%H:%i') ) AS date_formatted;

它将添加日期的时区信息。

- SnakeDrak