Sql实战-(日期常用函数)

小土豆Yuki

发布于 2023-12-18 19:26:14

1020

发布于 2023-12-18 19:26:14

返回对应日期所对应的星期几和周数

mysql> select weekday('20231216')+1,week(now())+1;
+-----------------------+---------------+
| weekday('20231216')+1 | week(now())+1 |
+-----------------------+---------------+
|                     6 |            51 |
+-----------------------+---------------+
1 row in set (0.00 sec)

返回两个日期时间之间的差值

mysql> select datediff(now(),'20231201'),timestampdiff(day,now(),'20231201'),timestampdiff(hour,'2023-12-01 12:00:00','2023-12-01 11:00:00');
+----------------------------+-------------------------------------+-----------------------------------------------------------------+
| datediff(now(),'20231201') | timestampdiff(day,now(),'20231201') | timestampdiff(hour,'2023-12-01 12:00:00','2023-12-01 11:00:00') |
+----------------------------+-------------------------------------+-----------------------------------------------------------------+
|                         15 |                                 -15 |                                                              -1 |
+----------------------------+-------------------------------------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

将日期格式进行转化

mysql> select now(),date_format(now(),'%y-%m-%d %h:%i:%s');
+---------------------+----------------------------------------+
| now()               | date_format(now(),'%y-%m-%d %h:%i:%s') |
+---------------------+----------------------------------------+
| 2023-12-16 23:36:59 | 23-12-16 11:36:59                      |
+---------------------+----------------------------------------+
1 row in set (0.00 sec)

返回某日期被减去或加上指定时间间隔后的日期

mysql> select now(),date_sub(now(),interval 1 day),date_add(now(),interval 1 minute);
+---------------------+--------------------------------+-----------------------------------+
| now()               | date_sub(now(),interval 1 day) | date_add(now(),interval 1 minute) |
+---------------------+--------------------------------+-----------------------------------+
| 2023-12-16 23:39:18 | 2023-12-15 23:39:18            | 2023-12-16 23:40:18               |
+---------------------+--------------------------------+-----------------------------------+
1 row in set (0.00 sec)

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2023-12-16，如有侵权请联系 cloudcommunity@tencent.com 删除

sql