当前位置 博文首页 > 韩超的博客 (hanchao5272):MySql时间戳timestamp与长整形long的
'%Y-%M-%d %H:%m:%s'
,例如:2019-07-06 15:18:47
。秒数
,从1970年1月1日
开始的。mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2019-07-06 15:18:47 |
+---------------------+
1 row in set (0.01 sec)
mysql> select UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1562397569 |
+------------------+
1 row in set (0.01 sec)
mysql> select unix_timestamp('2019-07-06 15:18:47') AS 当前时间;
+--------------+
| 当前时间 |
+--------------+
| 1562397527 |
+--------------+
1 row in set (0.01 sec)
mysql> select unix_timestamp('2019-07-06 15:18') AS 当前时间;
+--------------+
| 当前时间 |
+--------------+
| 1562397480 |
+--------------+
1 row in set (0.00 sec)
mysql> select unix_timestamp('2019-07-06 15') AS 当前时间;
+--------------+
| 当前时间 |
+--------------+
| 1562396400 |
+--------------+
1 row in set (0.01 sec)
mysql> select unix_timestamp('2019-07-06') AS 当前时间;
+--------------+
| 当前时间 |
+--------------+
| 1562342400 |
+--------------+
1 row in set (0.00 sec)
mysql> select unix_timestamp('2019-07') AS 当前时间;
+--------------+
| 当前时间 |
+--------------+
| 0.000000 |
+--------------+
1 row in set, 1 warning (0.00 sec)
mysql> select unix_timestamp('20190706') AS 当前时间;
+--------------+
| 当前时间 |
+--------------+
| 1562342400 |
+--------------+
1 row in set (0.02 sec)
mysql> select unix_timestamp('2019-07-06') AS 当前时间;
+--------------+
| 当前时间 |
+--------------+
| 1562342400 |
+--------------+
1 row in set (0.00 sec)
mysql> select unix_timestamp('2019/07/06') AS 当前时间;
+--------------+
| 当前时间 |
+--------------+
| 1562342400 |
+--------------+
1 row in set (0.00 sec)
通过unix_timestamp
可以进行时间戳转换的格式有:
%Y-%M-%d %H:%m:%s
、%Y/%M/%d %H:%m:%s
、%Y/%M/%d %H/%m/%s
%Y-%M-%d %H:%m
、%Y/%M/%d %H:%m
、%Y/%M/%d %H/%m
%Y-%M-%d %H
、%Y/%M/%d %H
%Y-%M-%d
、%Y/%M/%d
、%Y%M%d
mysql> select from_unixtime(1562397527,'%Y-%M-%d %H:%m:%s') AS 当前时间;
+---------------------+
| 当前时间 |
+---------------------+
| 2019-07-06 15:07:47 |
+---------------------+
1 row in set (0.01 sec)
mysql> select from_unixtime(1562397527,'%Y-%M-%d') AS 当前时间;
+--------------+
| 当前时间 |
+--------------+
| 2019-07-06 |
+--------------+
1 row in set (0.00 sec)
mysql> select from_unixtime(1562397527,'%Y年%M月%d日') AS 当前时间;
+-------------------+
| 当前时间 |
+-------------------+
| 2019年07月06日 |
+-------------------+
1 row in set (0.01 sec)
mysql> select from_unixtime(1562397527000,'%Y年%M月%d日') AS 当前时间;
+--------------+
| 当前时间 |
+--------------+
| NULL |
+--------------+
1 row in set (0.01 sec)
通过from_unixtime
可以转换的长整形需为长度为10的秒数,13位的毫秒数不支持。
通过from_unixtime
可以转换成的格式则决定于传入的格式化参数,主要参数有:
%Y
: 年%M
: 月%d
: 日%H
: 时%m
: 分%s
: 秒