MYSQL - datetime到秒 [英] MYSQL - datetime to seconds

问题描述

我无法找到（谷歌搜索，阅读mysql参考手册）如何在MySQL中获取 DATETIME的秒数 p>我不是要从datetime中提取秒，而是将其转换成秒。

解决方案

如果你想有所不同介于DATETIME值之间，请使用 TIMESTAMPDIFF ：

TIMESTAMPDIFF单位，datetime_expr1，datetime_expr2）

返回 datetime_expr2 - datetime_expr1 ，其中 datetime_expr1 和 datetime_expr2 是日期或日期时间表达式。一个表达式可能是日期，而另一个表达式可能是日期时间;如果需要，日期值被视为具有时间部分00：00：00的日期时间。结果单位（整数）由单位参数给出。单位的合法价值与 TIMESTAMPADD（）函数的说明中列出的相同。

mysql> SELECT TIMESTAMPDIFF（MONTH，'2003-02-01'，'2003-05-01'）;

- > 3

mysql> SELECT TIMESTAMPDIFF（YEAR，'2002-05-01'，'2001-01-01'）;

- > -1

mysql> SELECT TIMESTAMPDIFF（MINUTE，'2003-02-01'，'2003-05-01 12:05:55'）;

- > 128885

单位也可以 HOUR 这是您在其中一个评论中所要求的。

I wasn't able to find out (googling, reading mysql reference manual) how to get value of DATETIME in seconds in MySQL.

I dont mean to extract seconds from datetime, but to convert it into seconds.

解决方案

If you want to have the difference between to DATETIME values, use TIMESTAMPDIFF:

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Returns datetime_expr2 – datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function.

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
-> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
-> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
-> 128885

unit can also be HOUR which is what you asked for in one of the comments.

这篇关于MYSQL - datetime到秒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！