MySQL时间函数from_unixtime()date_format()unix_timestamp()now()使用说明

MySQL时间函数from_unixtime()date_format()unix_timestamp()now()使用说明

now() 当前时间
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-09-10 19:20:19 |
+---------------------+
unix_timestamp() 当前时间戳
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1536578429 |
+------------------+
unix_timestamp(now()) 当前时间转换成时间戳
mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1536578445 |
+-----------------------+
unix_timestamp(‘2018-08-08’) 指定时间转换成时间戳
mysql> select unix_timestamp('2018-08-08');
+------------------------------+
| unix_timestamp('2018-08-08') |
+------------------------------+
|                   1533657600 |
+------------------------------+
from_unixtime(1533657600) 将时间戳转换为时间 默认格式 ‘%Y-%m-%d %H:%i:%s’
mysql> select from_unixtime(1533657600);
+---------------------------+
| from_unixtime(1533657600) |
+---------------------------+
| 2018-08-08 00:00:00       |
+---------------------------+
from_unixtime(1533657600, ‘%Y-%m-%d’) 将时间戳转换为时间 并进行格式化
mysql> select from_unixtime(1533657600, '%Y-%m-%d');
+---------------------------------------+
| from_unixtime(1533657600, '%Y-%m-%d') |
+---------------------------------------+
| 2018-08-08                            |
+---------------------------------------+
查询时间戳字段 转换为时间格式显示
mysql> select from_unixtime(ctime, '%Y-%m-%d') from logs limit 1;
+----------------------------------+
| from_unixtime(ctime, '%Y-%m-%d') |
+----------------------------------+
| 2018-09-18                       |
+----------------------------------+
where时间戳字段
mysql> select count(*) from logs where from_unixtime(ctime, '%Y%m%d')=20180808 limit 1;
+----------+
| count(*) |
+----------+
|    12345 |
+----------+
date_format() 将时间进行格式化显示
mysql> select date_format(now(), '%Y/%m/%d');
+--------------------------------+
| date_format(now(), '%Y/%m/%d') |
+--------------------------------+
| 2018/08/08                     |
+--------------------------------+
where时间字段
mysql> select count(*) from logs where date_format(cdate, '%Y%m%d')=20180910 limit 1;
+----------+
| count(*) |
+----------+
|      123 |
+----------+
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页