我有一个带有日期字段的表,其中包含人类日期,例如:'2008-01-08 19:23:32'
现在,我必须将此字段以及同一表的其他一些字段复制到另一个表,但是日期需要在UNIX时间戳中。
mysql中是否有任何函数可以在查询本身内部将人类日期转换为unix时间戳?
请注意,如果表中存储的日期不是UTC,则可能必须先将其转换为UTC时区,然后再调用unix_timestamp()。 我不确定unix_timestamp()是否在计算时间戳之前将日期时间自动转换为UTC。
请注意,这是datetime字段类型,而不是date字段类型。 它们是有区别的。
mysql> select unix_timestamp('2008-01-08 19:23:32');
+---------------------------------------+
| unix_timestamp('2008-01-08 19:23:32') |
+---------------------------------------+
| 1199849012 |
+---------------------------------------+
1 row in set (0.04 sec)
在此处找到:http://www.epochconverter.com/
SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');
UNIX_TIMESTAMP()应该可以解决问题!
从MySQL文档:
If called with no argument, returns a Unix timestamp (seconds since
'1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP()
is called with a date argument, it returns the value of the argument
as seconds since '1970-01-01 00:00:00' UTC. date may be a DATE string,
a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or
YYYYMMDD. The server interprets date as a value in the current time
zone and converts it to an internal value in UTC.
mysql> SELECT UNIX_TIMESTAMP();
-> 1196440210
mysql> SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');
-> 1196440219
是。
SELECT UNIX_TIMESTAMP(column) FROM TABLE
查询:
SELECT UNIX_TIMESTAMP(TIMESTAMP(`opened`)) as timestamp_date, `opened` as datetime_type FROM `myterminal`
输出:
| timestamp_date | datetime_type
|------------------- |---------------------
| 1536602012 | 2018-09-10 14:53:32
| 1536603854 | 2018-09-10 15:24:14