MySQL和日期时间 [英] MySQL and datetime

问题描述

如果我有一个带有DATETIME列的表，则可以插入具有以下格式的日期：

If I have a table with a DATETIME column I can insert dates that have a format like:

2015-03-25 10:10:10
2015-03-25 10:10
2015-03-25 10
2015-03-25 

它将用零填充其余部分。但是我不能使用

It will fill in the remainder with zeros. I can't however use

2015-03 
2015

因为它将给出日期时间值错误错误。但是，可以在诸如 [..] WHERE timestamp< '2015-03' ..

As it will give an 'Incorrect datetime value' error. It is however possible to use these last two in a SELECT like [..] WHERE timestamp < '2015-03' ..

MySQL是否有一种方法可以在月份的剩余日期时间中用01-01填充日期时间，如果在日期时间中省略了这一天，还是我必须自己手动进行？

Is there a way that MySQL will fill in the remainder of datetimes with 01-01 for the month and day if omitted in datetimes or do I have to do that manually myself?

即我想在INSERT语句中使用'2015-03'，或执行 SELECT DATE_FORMAT（'2015-03'，'％Y％m％dT％H％i％S'）

I.e. I would like to use '2015-03' in an INSERT statement, or do something like SELECT DATE_FORMAT('2015-03', '%Y%m%dT%H%i%S')

推荐答案

如日期和时间文字：

MySQL识别 DATE 值格式如下：

MySQL recognizes DATE values in these formats:

• 作为'YYYY-MM-DD'或'YY-MM-DD'格式。允许使用宽松语法：任何标点符号都可以用作日期部分之间的分隔符。例如，'2012-12-31'，'2012/12/31'，'2012 ^ 12 ^ 31'和'2012 @ 12 @ 31'是等效的。

• As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A "relaxed" syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.

为不带分隔符的字符串，格式为'YYYYMMDD'或'YYMMDD' ，前提是该字符串可以作为日期使用。例如，'20070523'和'070523'被解释为'2007-05- 23'，但'071332'是非法的（具有无意义的月份和日期部分），并变为'0000-00 -00'。

As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.

作为 YYYYMMDD 或 YYMMDD 格式，条件是该数字应作为日期使用。例如， 19830905 和 830905 被解释为'1983-09-05' code>。

As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

MySQL识别 DATETIME 和 TIMESTAMP 值，格式如下：

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

• 作为'YYYY-MM-DD HH：MM：SS'或'YY- MM-DD HH：MM：SS'格式。这里也允许使用宽松语法：任何标点符号都可以用作日期部分或时间部分之间的分隔符。例如，'2012-12-31 11:30:45'，'2012 ^ 12 ^ 31 11 + 30 + 45'，'2012/12/31 11 * 30 * 45'和'2012 @ 12 @ 31 11 ^ 30 ^ 45' 是等效的。

• As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A "relaxed" syntax is permitted here, too: Any punctuation character may be used as the delimiter between date parts or time parts. For example, '2012-12-31 11:30:45', '2012^12^31 11+30+45', '2012/12/31 11*30*45', and '2012@12@31 11^30^45' are equivalent.

在日期和时间部分与小数秒部分之间唯一识别的分隔符是小数点。

The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.

日期和时间部分可以用 T 分隔，而不是空格。例如，'2012-12-31 11:30:45' '2012-12-31T11：30：45'是等效的。

The date and time parts can be separated by T rather than a space. For example, '2012-12-31 11:30:45' '2012-12-31T11:30:45' are equivalent.

作为在'YYYYMMDDHHMMSS'或<$中没有定界符的字符串c $ c>'YYMMDDHHMMSS'格式，条件是该字符串可以作为日期使用。例如，'20070523091528'和'070523091528'被解释为'2007-05- 23 09:15:28'，但'071122129015'是非法的（具有无意义的分钟部分），并变为 '0000-00-00 00:00:00'。

As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.

作为 YYYYMMDDHHMMSS中的数字或 YYMMDDHHMMSS 格式，条件是该数字应作为日期使用。例如， 19830905132800 和 830905132800 被解释为'1983-09-05 13:28 ：00'。

As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.

值得注意的是，MySQL 不支持您要使用的不完整格式。

Notably, MySQL does not support the incomplete formats that you wish to use.

MySQL 会发生接受某些不完整格式您尝试过（显然用零填充）是未记录的行为，很可能是开发人员所不希望的行为。 不能（并且不应该），尤其是因为可能存在行为中断的极端情况；

That MySQL happens to accept some of the incomplete formats you've tried (apparently by padding with zeroes) is undocumented behaviour, quite possibly unintended by the developers. It cannot (and should not) be relied upon, not least because edge cases could exist under which the behaviour breaks; or because the behaviour could be changed without warning in a future release.

如果绝对有必要向MySQL提供这种不完整的时间文字，则该行为是绝对必要的。不应该这样，因为您的数据访问层应该知道它正在处理的值的类型，并以受支持的格式将其提供给MySQL），因此可以使用其 STR_TO_DATE（） 函数相应地解析它们：

If it's absolutely necessary to provide such incomplete temporal literals to MySQL (which it shouldn't be, as your data access layer ought to be aware of the type of values it is handling and provide them to MySQL in a supported format), you can use its STR_TO_DATE() function to parse them accordingly:

未指定的日期或时间部分的值为0，因此在 str中指定的值不完整 产生将部分或全部部分设置为0的结果：

Unspecified date or time parts have a value of 0, so incompletely specified values in str produce a result with some or all parts set to 0:

mysql> SELECT STR_TO_DATE('abc','abc');
        -> '0000-00-00'
mysql> SELECT STR_TO_DATE('9','%m');
        -> '0000-09-00'
mysql> SELECT STR_TO_DATE('9','%s');
        -> '00:00:09'

日期值部分的范围检查如第11.3.1节 DATE，DATETIME和TIMESTAMP类型 。例如，这意味着允许零日期或部分值为0的日期，除非将SQL模式设置为不允许此类值。

Range checking on the parts of date values is as described in Section 11.3.1, "The DATE, DATETIME, and TIMESTAMP Types". This means, for example, that "zero" dates or dates with part values of 0 are permitted unless the SQL mode is set to disallow such values.

因此，例如，您可以使用：

So, for example, you might use:

STR_TO_DATE('2015-03', '%Y-%m');

这篇关于MySQL和日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！