用时区偏移量代替Z值的MySQL ISO 8601日期

Question

我需要选择一个 ISO 8601 格式的日期。

DATE_FORMAT(date, '%Y-%m-%dT%TZ') 

这将产生类似于的内容

2013-11-13T15:47:530Z

但我需要的是具有偏移量而不是Z值：

2013-11-13T15:47:53+02:00

我该如何使用纯MySQL实现这个功能？

- Daniel W.

---

时区不会保存在 MySQL 的日期时间对象中吗？你想要日期时间的偏移量。 - Daniel W.

据我所知，没有这样的功能...（请注意，偏移量和时区并不完全相同。） - Jon Skeet

你是否只关心“即时”？如果是这样，我建议将“所有东西”都存储在UTC中，并最好将数据库的时区设置为UTC以确保安全。然后，在数据库外进行任何其他处理。 - Jon Skeet

不行，因为 CET 并不在偏移量 0 处。就像我说的，为了简单起见，我认为你应该把它们存储为 UTC。但是我认为你应该对 MySQL 类型代表什么以及你的客户端支持什么进行更多的研究。（我们不知道你用什么来与 MySQL 进行通信...） - Jon Skeet

MySQL没有datetimeoffset类型，但是您可以存储两个列 - 一个datetime用于主要部分和一个time类型用于偏移量。但像Jon所说的那样，将其转换为UTC并存储单个datetime值可能更容易。除非您有特定需要保留偏移量，否则通常不需要。 - Matt Johnson-Pint

Answer 1

你需要在数据库中添加一个额外的列来存储时区。我不知道有任何一种数据库可以将日期时间与时区/偏移量一起存储。

或者按照ISO 8601格式将日期存储为带偏移量的字符串。 编辑： 我稍微纠正了一下，对于一些较新的数据库是可能的！

• Postgresql Date/Time Types，确实可以包括时区。
  • 数据类型："timestamp [ (p) ] with time zone" 仅用于输入和输出，它存储在UTC中。
  • 数据类型："time with time zone"，仅限时间，存储时区偏移量。从8版开始。
• ORACLE Datetime Datatypes and Time Zone Support.
  • 数据类型："TIMESTAMP WITH TIME ZONE"，存储时区偏移量。从9i版本开始。
  • 数据类型："TIMESTAMP WITH TIME ZONE"，存储时区偏移量或时区名称。从10g版本开始。
  • 也可参考Oracle TIMESTAMP WITH TIMEZONE named zone vs offset。
• MicroSoft SQL Server 2008
  • 数据类型："datetimeoffset"，存储时区偏移量。
• Sybase
  • 数据类型："TIMESTAMP WITH TIME ZONE"，存储时区偏移量。
  • TIMESTAMP WITH TIME ZONE的支持是SQL/2008标准的可选SQL语言特性F411。

• 似乎实际上是相当标准的 SQL99。

• 不适用于Mysql。版本 5.6。

• 也不适用于SQLite。版本 3。

我发现自己纠正了错误，这很令人欣慰；-)

- Trygve Sørdal

Answer 2

这个片段适用于没有毫秒的ISO 8601，不支持Zulu时间：

select IF(
LENGTH(value) < 23,
STR_TO_DATE(value,'%Y-%m-%dT%TZ'),
CASE SUBSTRING(value from 20 for 1)
WHEN '+' THEN
DATE_ADD(
STR_TO_DATE(SUBSTRING(value from 1 for 19),'%Y-%m-%dT%TZ'),
INTERVAL SUBSTRING(value from 21 for 2) HOUR)
WHEN '-' THEN
DATE_SUB(
STR_TO_DATE(SUBSTRING(value from 1 for 19),'%Y-%m-%dT%TZ'),
INTERVAL SUBSTRING(value from 21 for 2) HOUR
)
END
)
from THE_NAMESPACE.THE_TABLE.THE_COLUMN as value;

- Bernd Hopp