MySQL和日期时间

Question

如果我有一张带有DATETIME列的表，我可以插入格式为以下形式的日期：

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

它会用零填充余数。但我不能使用其他方法。

2015-03 
2015

作为它将会产生一个“不正确的日期时间值”错误。然而，在SELECT语句中使用这两种方法是可能的，例如：[..] WHERE timestamp < '2015-03' ..

MySQL是否有一种方式，如果datetime中省略了月份和日期，会用01-01填充其余部分，还是我必须手动进行操作？

即，我想在INSERT语句中使用“2015-03”，或者做类似于SELECT DATE_FORMAT('2015-03', '%Y%m%dT%H%i%S')的事情。

- Lieuwe

Answer 1

如日期和时间文字中所述：

MySQL识别以以下格式表示的DATE值：

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

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

• 数字日期可以使用YYYYMMDD或YYMMDD格式，前提是该数字作为日期是有意义的。例如，19830905和830905被解释为'1983-09-05'。

MySQL识别以以下格式表示的DATETIME和TIMESTAMP值：

• 字符串日期时间可以使用'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'是等效的。

  日期和时间部分与小数秒部分之间仅识别小数点作为分隔符。

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

• 无分隔符的字符串日期时间可以使用'YYYYMMDDHHMMSS'或'YYMMDDHHMMSS'格式，前提是该字符串作为日期是有意义的。例如，'20070523091528'和'070523091528'被解释为'2007-05-23 09:15:28'，但'071122129015'是不合法的（具有无意义的分钟部分），变成'0000-00-00 00:00:00'。

• 数字日期时间可以使用YYYYMMDDHHMMSS或YYMMDDHHMMSS格式，前提是该数字作为日期是有意义的。例如，19830905132800和830905132800被解释为'1983-09-05 13:28:00'。

值得注意的是，MySQL不支持您想要使用的不完整格式。MySQL恰好接受了您尝试过的一些不完整格式（显然通过填充零），这是未经记录的行为，很可能是开发人员无意中造成的。不能（也不应该）依赖此行为，因为边缘情况可能存在导致行为破裂；或者因为行为可能在未来的版本中被更改而没有警告。如果绝对必要向MySQL提供这样的不完整时间文字（这不应该是必须的，因为您的数据访问层应该知道它正在处理的值的类型并以支持的格式提供它们给MySQL），则可以使用其STR_TO_DATE()函数相应地解析它们：

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> <strong>SELECT STR_TO_DATE('abc','abc');</strong>
        -> '0000-00-00'
mysql> <strong>SELECT STR_TO_DATE('9','%m');</strong>
        -> '0000-09-00'
mysql> <strong>SELECT STR_TO_DATE('9','%s');</strong>
        -> '00:00:09'

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.

因此，例如，您可能会使用：

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

- eggyal

---

附录：在问题中给出的WHERE子句示例中，不完整的格式之所以被“支持”，是因为这些文字被解析为纯字符串；并且根据数据库连接的排序规则进行字典排序比较。虽然这不是真正的时间类型比较（也不能使用时间列上的索引），但由于MySQL的日期时间格式布局，这样的字典排序产生与真实的时间排序相同的结果。 - eggyal

感谢。我之所以想要“使用”这些时间戳，是因为MySQL在首次使用时不会（全部）错误。我希望不必编写将正确的ISO8601（例如'2015-03'）转换为MySQL DATETIME格式的代码，但可惜。 - Lieuwe

@Lieuwe：通常情况下，实现这个功能的代码已经存在于数据访问层中。所以，除非你要自己编写（鉴于已经有很多出色的库提供了这样的功能，不建议这样做），你真的不应该去纠结这些底层实现细节。 - eggyal

我使用C++，接收一个通常符合ISO8601标准的日期时间字符串，需要将其存储到数据库中。我可以明确地使用strptime、std::get_time或sscanf等方法尝试将字符串解析为时间结构，然后重新组装成MySQL能够理解的日期时间字符串，但这不是重点。如果我尝试将非数字内容插入到数字数据库字段中，或者将无效字符插入到枚举字段中，MySQL会直接返回错误。我原本希望对于ISO8601时间戳，即使我不知道该字符串的确切格式，MySQL也能做同样的处理。 - Lieuwe

Answer 2

试试unix_timestamp()

SELECT like [..] WHERE unix_timestamp(timestamp) < '2015-03'

- M3ghana