postgresql ISO 8601 日期使用什么数据类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13141433/
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
What datatype to use for ISO 8601 dates?
提问by Robert H
I am redesigning a database that currently imports ISO 8601 dates in the format of 2012-10-27T07:30:38+00:00into a varchar field. The current database is hosted on a MySQL 5.5 server.
我正在重新设计一个当前将 ISO 8601 日期2012-10-27T07:30:38+00:00导入 varchar 字段格式的数据库。当前数据库托管在 MySQL 5.5 服务器上。
In searching through documentation and various SO posts, I have not found a definitive answer on what datatype I should use in MySQL for this purpose. The closest post is: MySQL insert to DATETIME: is it safe to use ISO::8601 format?where it provides a work around of sorts, however this is not a desirable option.
在搜索文档和各种 SO 帖子时，我还没有找到关于为此目的应该在 MySQL 中使用哪种数据类型的明确答案。最近的帖子是： MySQL 插入到 DATETIME：使用 ISO::8601 格式是否安全？它提供了一种解决方法，但这不是一个理想的选择。
The MySQL documentation (http://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html) does not say, and the only reference I can find on official documentation is located on page: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
MySQL 文档（http://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html）没有说，我能在官方文档上找到的唯一参考位于页面：http: //dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
which states "The possible values for the first and second arguments result in several possible format strings (for the specifiers used, see the table in the DATE_FORMAT() function description). ISO format refers to ISO 9075, not ISO 8601."
其中指出“第一个和第二个参数的可能值导致几种可能的格式字符串（对于使用的说明符，请参阅 DATE_FORMAT() 函数描述中的表格）。ISO 格式指的是 ISO 9075，而不是 ISO 8601。”
Now the PostgreSQL documentation specifically mentions ISO8601 ( http://www.postgresql.org/docs/9.2/static/datetime-keywords.htmland http://www.postgresql.org/docs/9.2/static/datatype-datetime.html) which leads me to my question:
现在 PostgreSQL 文档特别提到了 ISO8601（http://www.postgresql.org/docs/9.2/static/datetime-keywords.html和http://www.postgresql.org/docs/9.2/static/datatype-datetime。 html) 这让我想到了我的问题：
Does MySQL correctly support ISO 8601, or should I consider a database with native support?
MySQL 是否正确支持 ISO 8601，还是应该考虑使用本机支持的数据库？
--Edit--
- 编辑 -
Attempting to insert the example time stamp above into a datetime column gives the following error:
尝试将上面的示例时间戳插入日期时间列会出现以下错误：
10:55:55 insert into test(date1) values('2012-10-27T07:30:38+00:00') Error Code: 1292. Incorrect datetime value: '2012-10-27T07:30:38+00:00' for column 'date1' at row 1 0.047 sec
采纳答案by a_horse_with_no_name
Do not store date or timestamp values in a varchar column. You have no way of ensuring that a correct value is stored (no one prevents you from storing 2012-02-31 28:99:70in there.
不要在 varchar 列中存储日期或时间戳值。您无法确保存储正确的值（没有人阻止您存储2012-02-31 28:99:70在那里。
If you don't need the time part, use a datedatatype (available in MySQL and PostgreSQL) if you do need the time use a timestamp(or datetimein MySQL) data type.
如果您不需要时间部分，请使用date数据类型（在 MySQL 和 PostgreSQL 中可用），如果您确实需要时间，请使用timestamp（或datetime在 MySQL 中）数据类型。
The formatting of the values should be done in your frontend or if you absolutely have to do it in SQL using e.g. to_char() (or the equivalent in MySQL) when retrieving the values.
值的格式化应该在您的前端完成，或者如果您绝对必须在检索值时使用例如 to_char()（或 MySQL 中的等效项）在 SQL 中进行格式化。
Again: never store dates or timestamps in a varchar (just as you should never stored real numbers in a varchar column).
再说一遍：永远不要在 varchar 中存储日期或时间戳（就像永远不要在 varchar 列中存储实数一样）。
Here is the MySQL overview for date/time datatypes: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html
这是日期/时间数据类型的 MySQL 概述：http: //dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html
Here is the PostgreSQL overview for date7time datatypes: http://www.postgresql.org/docs/current/static/datatype-datetime.html
以下是 date7time 数据类型的 PostgreSQL 概述：http: //www.postgresql.org/docs/current/static/datatype-datetime.html
Edit
编辑
If you are concerned about the literal format. Both DBMS support the standard ANSI Date and Timestamp literals:
如果您担心文字格式。两个 DBMS 都支持标准的 ANSI 日期和时间戳文字：
insert into some_table
(ts_column, date_column)
values
(timestamp '2012-10-27T07:30:38+00:00', DATE '2012-12-30');
SQLFiddle for PostgreSQL: http://sqlfiddle.com/#!12/cdd39/1
PostgreSQL 的 SQLFiddle：http://sqlfiddle.com/#!12/cdd39/1
Note the keywords timestampand datein front of the character literal.
请注意关键字timestamp和date字符文字前面。
Edit 2
编辑 2
It seems MySQL cannot insertsuch a value, although it canuse that literal in a SELECT statement:
MySQL 似乎无法插入这样的值，尽管它可以在 SELECT 语句中使用该文字：
回答by Clodoaldo Neto
Postgresql can handle that format:
Postgresql 可以处理这种格式：
without
没有
select timestamp '2012-10-27T07:30:38+00:00';
timestamp
---------------------
2012-10-27 07:30:38
or with time zone:
或时区：
select timestamptz '2012-10-27T07:30:38+00:00';
timestamptz
------------------------
2012-10-27 05:30:38-02
Whereas MySQL seems to not care much about the time zone part:
而 MySQL 似乎不太关心时区部分：
create table t (ts timestamp);
insert into t (ts) values
('2012-10-27T07:30:38+03:00'),
('2012-10-27T07:30:38-02:00');
select * from t;
+---------------------+
| ts |
+---------------------+
| 2012-10-27 07:30:38 |
| 2012-10-27 07:30:38 |
+---------------------+
回答by Alexander Behling
Just another advantage of using datetime or timestamp for storing. In phpMyAdmin or phpPgAdmin the value is displayed as a string e.g. 2015-01-22 11:13:42. So it's much easier to find a date as if it's stored as a varchar or int.
使用日期时间或时间戳进行存储的另一个优势。在 phpMyAdmin 或 phpPgAdmin 中，该值显示为字符串，例如 2015-01-22 11:13:42。因此，查找日期要容易得多，就好像它存储为 varchar 或 int 一样。
And I think it's important what timezone the server has. I've see that a timestamp in MySql doesn't display microseconds. I know MySQL use UTC-Time for timestamp and datetime-fields. So provide the values as a UTC-timestamp.
我认为服务器的时区很重要。我已经看到 MySql 中的时间戳不显示微秒。我知道 MySQL 使用 UTC-Time 作为时间戳和日期时间字段。因此，将值作为 UTC 时间戳提供。
回答by tadman
The ISO 8601date format, "YYYY-MM-DD", is what MySQL emits internally when displaying date values and it can import them just the same.
在ISO 8601的日期格式“YYYY-MM-DD”，是显示日期值，它可以导入他们一样的东西时，MySQL的内部发出。
These are preferable to "American style" dates like "MM/DD/YY" where there's too much ambiguity to be automatically resolved.
这些比“美式”日期更可取，比如“MM/DD/YY”，因为有太多的歧义无法自动解决。
ISO 9075 appears to refer to the entirety of the SQL standard, not a specific date format, though the standard itself does have standard formatting for dates and times.
ISO 9075 似乎是指整个 SQL 标准，而不是特定的日期格式，尽管标准本身确实具有日期和时间的标准格式。
