MYSQL - 日期时间到秒

MYSQL - datetime to seconds

提问by Mike

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

我无法找到（谷歌搜索，阅读 mysql 参考手册）如何在 MySQL 中以秒为单位获取DATETIME 的值。

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

我不是要从日期时间中提取秒数，而是将其转换为秒数。

回答by Pascal MARTIN

If by "convert to seconds", you mean "convert to an UNIX Timestamp" (i.e. number of seconds since 1970-01-01), then you can use the UNIX_TIMESTAMPfunction :

如果通过“转换为秒”，您的意思是“转换为UNIX 时间戳” （即自此以来的秒数1970-01-01），那么您可以使用该UNIX_TIMESTAMP函数：

select UNIX_TIMESTAMP(your_datetime_field)
from your_table
where ...

And, for the sake of completness, to convert from an Unix Timestamp to a datetime, you can use the FROM_UNIXTIMEfunction.

而且，为了完整起见，要将 Unix 时间戳转换为日期时间，您可以使用该FROM_UNIXTIME函数。

回答by Felix Kling

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

如果您想区分两个 DATETIME 值，请使用TIMESTAMPDIFF：

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Returns datetime_expr2 – datetime_expr1, where datetime_expr1and datetime_expr2are 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.

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

返回datetime_expr2 – datetime_expr1，其中datetime_expr1和datetime_expr2是日期或日期时间表达式。一个表达式可能是日期，另一个表达式可能是日期时间；日期值被视为在必要时具有时间部分“00:00:00”的日期时间。结果的单位（整数）由 unit 参数给出。unit 的合法值与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

unitcan also be HOURwhich is what you asked for in one of the comments.

unit也可以HOUR是您在其中一条评论中要求的内容。

The unit argument can be any of the following:

unit 参数可以是以下任何一种：

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

• 微秒
• 第二
• 分钟
• 小时
• 日
• 星期
• 月
• 四分之一
• 年

The level of usefulness of some of the other options will of course be determined by the granularity of the data. For instance, "MICROSECOND" will only have limited use if you are not storing microseconds in your DATETIME values.

其他一些选项的有用程度当然将由数据的粒度决定。例如，如果您没有在 DATETIME 值中存储微秒，则“MICROSECOND”的用途有限。

回答by Rolf Kransse

Use TIME_TO_SEC in previous versions for mysql

在 mysql 的先前版本中使用 TIME_TO_SEC

SELECT TIME_TO_SEC(time column) FROM table

SELECT TIME_TO_SEC（时间列）FROM 表

回答by Shanaka

i used in mysql

我在 mysql 中使用过

TO_SECONDS(your date goes here) method to convert date to seconds from year 0

TO_SECONDS（您的日期在此处）方法将日期从第 0 年转换为秒

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

回答by Biggum

Starting in mysql 5.5.0 you can use to_seconds()

从 mysql 5.5.0 开始，您可以使用 to_seconds()

TO_SECONDS(FIELD_NAME)

FIELD_NAME must be DATETIMEtype

FIELD_NAME 必须是DATETIME类型

回答by O. Jones

The function UNIX_TIMESTAMP(datetime) returns the unix time, which happens to be the number of seconds since 1-Jan-1970 0000 UTC. That may be what you need, but not if you're dealing with dates of birth, historical dates, or dates after 2037.

函数 UNIX_TIMESTAMP( datetime) 返回 unix 时间，它恰好是自 1-Jan-1970 0000 UTC 以来的秒数。这可能是您需要的，但如果您要处理出生日期、历史日期或 2037 年之后的日期，则不需要。

回答by Rinoyrix

I have created my own query for your problem:

我为您的问题创建了自己的查询：

SELECT HOUR(`colname`) * 3600 + MINUTE(`colname`) * 60 + SECOND(`colname`)
FROM widgets
WHERE id = 1;

• Use id = 1if you have to take a specific row.
• The output will be in seconds.

• 使用id = 1，如果你必须采取特定的行。
• 输出将以秒为单位。