MySQL时间函数from_unixtime()date_format()unix_timestamp()now()使用说明
now() 当前时间
mysql> select now();
---------------------
| now() |
---------------------
| 2018-09-10 19:20:19 |
---------------------
1
2
3
4
5
6
unix_timestamp() 当前时间戳
mysql> select unix_timestamp();
------------------
| unix_timestamp() |
------------------
| 1536578429 |
------------------
1
2
3
4
5
6
unix_timestamp(now()) 当前时间转换成时间戳
mysql> select unix_timestamp(now());
-----------------------
| unix_timestamp(now()) |
-----------------------
| 1536578445 |
-----------------------
1
2
3
4
5
6
unix_timestamp(‘2018-08-08’) 指定时间转换成时间戳
mysql> select unix_timestamp(‘2018-08-08‘);
------------------------------
| unix_timestamp(‘2018-08-08‘) |
------------------------------
| 1533657600 |
------------------------------
1
2
3
4
5
6
from_unixtime(1533657600) 将时间戳转换为时间 默认格式 ‘%Y-%m-%d %H:%i:%s’
mysql> select from_unixtime(1533657600);
---------------------------
| from_unixtime(1533657600) |
---------------------------
| 2018-08-08 00:00:00 |
---------------------------
1
2
3
4
5
6
from_unixtime(1533657600, ‘%Y-%m-%d’) 将时间戳转换为时间 并进行格式化
mysql> select from_unixtime(1533657600, ‘%Y-%m-%d‘);
---------------------------------------
| from_unixtime(1533657600, ‘%Y-%m-%d‘) |
---------------------------------------
| 2018-08-08 |
---------------------------------------
1
2
3
4
5
6
查询时间戳字段 转换为时间格式显示
mysql> select from_unixtime(ctime, ‘%Y-%m-%d‘) from logs limit 1;
----------------------------------
| from_unixtime(ctime, ‘%Y-%m-%d‘) |
----------------------------------
| 2018-09-18 |
----------------------------------
1
2
3
4
5
6
where时间戳字段
mysql> select count(*) from logs where from_unixtime(ctime, ‘%Y%m%d‘)=20180808 limit 1;
----------
| count(*) |
----------
| 12345 |
----------
1
2
3
4
5
6
date_format() 将时间进行格式化显示
mysql> select date_format(now(), ‘%Y/%m/%d‘);
--------------------------------
| date_format(now(), ‘%Y/%m/%d‘) |
--------------------------------
| 2018/08/08 |
--------------------------------
1
2
3
4
5
6
where时间字段
mysql> select count(*) from logs where date_format(cdate, ‘%Y%m%d‘)=20180910 limit 1;
----------
| count(*) |
----------
| 123 |
----------
————————————————
版权声明：本文为CSDN博主「gocuber」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/gocuber/article/details/80195591