AI 助理
备案控制台
开发者社区 数据库 文章 正文

【mysql】日期和时间函数

2022-03-14 148
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： 【mysql】日期和时间函数

日期和时间函数

1. 获取日期、时间

函数 用法
CURDATE() ，CURRENT_DATE() 返回当前日期，只包含年、月、日
CURTIME() ， CURRENT_TIME() 返回当前时间，只包含时、分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() 返回当前系统日期和时间
UTC_DATE() 返回UTC（世界标准时间）日期
UTC_TIME() 返回UTC（世界标准时间）时间

举例：

SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0
FROM DUAL;

在这里插入图片描述

2. 日期与时间戳的转换

函数 用法
UNIX_TIMESTAMP() 以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() ->1634348884
UNIX_TIMESTAMP(date) 将时间date以UNIX时间戳的形式返回。
FROM_UNIXTIME(timestamp) 将UNIX时间戳的时间转换为普通格式的时间

举例：

mysql> SELECT UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
|            1576380910 |
+-----------------------+
1 row in set (0.01 sec)

mysql> SELECT UNIX_TIMESTAMP(CURDATE());
+---------------------------+
| UNIX_TIMESTAMP(CURDATE()) |
+---------------------------+
|                1576339200 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP(CURTIME());
+---------------------------+
| UNIX_TIMESTAMP(CURTIME()) |
+---------------------------+
|                1576380969 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11')
+---------------------------------------+
| UNIX_TIMESTAMP('2011-11-11 11:11:11') |
+---------------------------------------+
|                            1320981071 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(1576380910);
+---------------------------+
| FROM_UNIXTIME(1576380910) |
+---------------------------+
| 2019-12-15 11:35:10       |
+---------------------------+
1 row in set (0.00 sec)

3. 获取月份、星期、星期数、天数等函数

函数 用法
YEAR(date) / MONTH(date) / DAY(date) 返回具体的日期值
HOUR(time) / MINUTE(time) / SECOND(time) 返回具体的时间值
MONTHNAME(date) 返回月份：January，...
DAYNAME(date) 返回星期几：MONDAY，TUESDAY.....SUNDAY
WEEKDAY(date) 返回周几，注意，周1是0，周2是1，。。。周日是6
QUARTER(date) 返回日期对应的季度，范围为1～4
WEEK(date) ， WEEKOFYEAR(date) 返回一年中的第几周
DAYOFYEAR(date) 返回日期是一年中的第几天
DAYOFMONTH(date) 返回日期位于所在月份的第几天
DAYOFWEEK(date) 返回周几，注意：周日是1，周一是2，。。。周六是7

举例：

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;

在这里插入图片描述

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

在这里插入图片描述

4. 日期的操作函数

函数 用法
EXTRACT(type FROM date) 返回指定日期中特定的部分，type指定返回的值
  • EXTRACT(type FROM date)函数中type的取值与含义：

在这里插入图片描述

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;

5. 时间和秒钟转换的函数

函数 用法
TIME_TO_SEC(time) 将 time 转化为秒并返回结果值。转化的公式为：小时*3600+分钟*60+秒
SEC_TO_TIME(seconds) 将 seconds 描述转化为包含小时、分钟和秒的时间

举例：

mysql> SELECT TIME_TO_SEC(NOW());
+--------------------+
| TIME_TO_SEC(NOW()) |
+--------------------+
|               78774 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT SEC_TO_TIME(78774);
+--------------------+
| SEC_TO_TIME(78774) |
+--------------------+
| 21:52:54            |
+--------------------+
1 row in set (0.12 sec)

6. 计算日期和时间的函数

第1组：

函数 用法
DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type) 返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type) 返回与date相差INTERVAL时间间隔的日期

上述函数中type的取值：

在这里插入图片描述

举例：

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;
SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;

第2组：

函数 用法
ADDTIME(time1,time2) 返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数
SUBTIME(time1,time2) 返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数
DATEDIFF(date1,date2) 返回date1 - date2的日期间隔天数
TIMEDIFF(time1, time2) 返回time1 - time2的时间间隔
FROM_DAYS(N) 返回从0000年1月1日起，N天以后的日期
TO_DAYS(date) 返回日期date距离0000年1月1日的天数
LAST_DAY(date) 返回date所在月份的最后一天的日期
MAKEDATE(year,n) 针对给定年份与所在年份中的天数返回一个日期
MAKETIME(hour,minute,second) 将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n) 返回time加上n后的时间

举例：

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;
mysql> SELECT ADDTIME(NOW(), 50);
+---------------------+
| ADDTIME(NOW(), 50)  |
+---------------------+
| 2019-12-15 22:17:47 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT ADDTIME(NOW(), '1:1:1');
+-------------------------+
| ADDTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 23:18:46     |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBTIME(NOW(), '1:1:1');
+-------------------------+
| SUBTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 21:23:50     |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT SUBTIME(NOW(), '-1:-1:-1'); 
+----------------------------+
| SUBTIME(NOW(), '-1:-1:-1') |
+----------------------------+
| 2019-12-15 22:25:11        |
+----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT FROM_DAYS(366);
+----------------+
| FROM_DAYS(366) |
+----------------+
| 0001-01-01     |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT MAKEDATE(2020,1);
+------------------+
| MAKEDATE(2020,1) |
+------------------+
| 2020-01-01       |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT MAKEDATE(2020,32);
+-------------------+
| MAKEDATE(2020,32) |
+-------------------+
| 2020-02-01        |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKETIME(1,1,1);
+-----------------+
| MAKETIME(1,1,1) |
+-----------------+
| 01:01:01        |
+-----------------+
1 row in set (0.00 sec)
mysql> SELECT PERIOD_ADD(20200101010101,1);
+------------------------------+
| PERIOD_ADD(20200101010101,1) |
+------------------------------+
|               20200101010102 |
+------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TO_DAYS(NOW());
+----------------+
| TO_DAYS(NOW()) |
+----------------+
|          737773 |
+----------------+
1 row in set (0.00 sec)

举例：查询 7 天内的新增用户数有多少？

SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7

7. 日期的格式化与解析

函数 用法
DATE_FORMAT(date,fmt) 按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt) 按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type) 返回日期字符串的显示格式
STR_TO_DATE(str, fmt) 按照字符串fmt对str进行解析，解析为一个日期

上述非GET_FORMAT函数中fmt参数常用的格式符：

格式符 说明 格式符 说明
%Y 4位数字表示年份 %y 表示两位数字表示年份
%M 月名表示月份（January,....） %m 两位数字表示月份（01,02,03。。。）
%b 缩写的月名（Jan.，Feb.，....） %c 数字表示月份（1,2,3,...）
%D 英文后缀表示月中的天数（1st,2nd,3rd,...） %d 两位数字表示月中的天数(01,02...)
%e 数字形式表示月中的天数（1,2,3,4,5.....）
%H 两位数字表示小数，24小时制（01,02..） %h和%I 两位数字表示小时，12小时制（01,02..）
%k 数字形式的小时，24小时制(1,2,3) %l 数字形式表示小时，12小时制（1,2,3,4....）
%i 两位数字表示分钟（00,01,02） %S和%s 两位数字表示秒(00,01,02...)
%W 一周中的星期名称（Sunday...） %a 一周中的星期缩写（Sun.，Mon.,Tues.，..）
%w 以数字表示周中的天数(0=Sunday,1=Monday....)
%j 以3位数字表示年中的天数(001,002...) %U 以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u 以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T 24小时制 %r 12小时制
%p AM或PM %% 表示%

GET_FORMAT函数中date_type和format_type参数取值如下：

举例：

mysql> SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
+--------------------------------+
| DATE_FORMAT(NOW(), '%H:%i:%s') |
+--------------------------------+
| 22:57:34                        |
+--------------------------------+
1 row in set (0.00 sec)
SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y')
FROM DUAL;

SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s')
FROM DUAL;

SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;
mysql> SELECT GET_FORMAT(DATE, 'USA');
+-------------------------+
| GET_FORMAT(DATE, 'USA') |
+-------------------------+
| %m.%d.%Y                |
+-------------------------+
1 row in set (0.00 sec)

SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),
FROM DUAL;
mysql> SELECT STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d'); 
+-----------------------------------------------+
| STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') |
+-----------------------------------------------+
| 2020-01-01                                    |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
文章标签：
云数据库 RDS MySQL 版
关系型数据库
Unix
MySQL
关键词：
云数据库 RDS MySQL 版日期
云数据库 RDS MySQL 版时间函数
云数据库 RDS MySQL 版日期时间函数
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
兮动人
目录
相关文章
技术蜜糖罐
|
6月前
|
关系型数据库 MySQL 数据挖掘
轻松入门MySQL：利用MySQL时间函数优化产品销售数据统计与分析（9）
轻松入门MySQL：利用MySQL时间函数优化产品销售数据统计与分析（9）
技术蜜糖罐
163 0
见贤思₇
|
6月前
|
关系型数据库 MySQL 数据挖掘
MYSQL日期与时间函数的实用技巧
MYSQL日期函数与时间函数是数据库操作的关键工具，可轻松处理、查询、比较和格式化日期时间数据。它们能提取日期的年、月、日等部分，便于筛选和统计；同时，也能处理时间数据，如计算时间差、获取当前时间，助力用户更好地管理时间信息。掌握这些函数，不仅能提升数据库操作效率，还能为数据分析和报表生成提供有力支持。无论初学者还是资深数据库管理员，精通MYSQL的日期和时间函数都至关重要，以满足各种数据处理需求，确保数据的准确性和高效性。
见贤思₇
334 0
2G冲浪词条
|
17天前
|
关系型数据库 MySQL
Mysql 中日期比较大小的方法有哪些？
在 MySQL 中，可以通过多种方法比较日期的大小，包括使用比较运算符、NOW() 函数、DATEDIFF 函数和 DATE 函数。这些方法可以帮助你筛选出特定日期范围内的记录，确保日期格式一致以避免错误。
2G冲浪词条
31 1
小Tomkk
|
1月前
|
存储 自然语言处理 关系型数据库
mysql 8.0 日期维度表生成（可运行）
mysql 8.0 日期维度表生成（可运行）
小Tomkk
45 2
肾透侧视攻城狮
|
18天前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列（7）作者——LJS[含MySQL 聚合、数学、字符创、日期、控制流函数等使用详解；注意点及常见报错问题所对应的解决方法]
MySQL 聚合、数学、字符创、日期、控制流函数等使用详解；注意点及常见报错问题所对应的解决方法
肾透侧视攻城狮
17 0
小Tomkk
|
1月前
|
关系型数据库 MySQL 数据处理
企业级应用 mysql 日期函数变量，干货已整理
本文详细介绍了如何在MySQL8.0中使用DATE_FORMAT函数进行日期格式的转换，包括当日、昨日及不同时间段的数据获取，并提供了实际的ETL应用场景和注意事项，有助于提升数据处理的灵活性和一致性。
小Tomkk
41 0
你的小笔记本YY
|
6月前
|
关系型数据库 MySQL Linux
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】（代码演示＆可cv代码）
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】（代码演示＆可cv代码）
你的小笔记本YY
462 4
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】（代码演示＆可cv代码）
快捷资讯
|
5月前
|
关系型数据库 MySQL 数据库
mysql之日期时间函数
SELECT SECOND('12:45:31'); -- 结果: 31 请注意，以上结果取决于实际查询执行的时间和日期。在实际的数据库操作中，通常会使用列名作为参数，而不是直接使用硬编码的日期或时间值。例如： SELECT YEAR(birthdate) FROM employees; 这将返回 employees 表中每位员工的 birthdate 列的年份。日期和时间函数在处理数据时非常有用，特别是当你需要对日期和时间进行操作或计算时。 ————————————————
快捷资讯
42 0
3hvg2zg3cgsi6
|
5月前
|
存储 关系型数据库 MySQL
MySQL 日期表制作
MySQL 日期表制作
3hvg2zg3cgsi6
56 0
讓丄帝愛伱
|
6月前
|
存储 关系型数据库 MySQL
MySQL 格式化日期函数 DATE_FORMAT(), FROM_UNIXTIME() 和 UNIX_TIMESTAMP() 之间区别
MySQL 格式化日期函数 DATE_FORMAT(), FROM_UNIXTIME() 和 UNIX_TIMESTAMP() 之间区别
讓丄帝愛伱
197 1

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    RDS入门——Excel文件转存到RDS数据库实践
  • 3
    报错：java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Driver at java.base/jdk.internal.loader.Bui
  • 4
    icinga2 check_mysql_health插件
  • 5
    Linux操作系统上的mysql安装
  • 6
    mysql查询拼接
  • 7
    PostgreSQL 12 preview - CTE 增强，支持用户语法层控制 materialized 优化
  • 8
    阿里云RDS for MySQL 发布三节点企业版
  • 9
    mysql常用操作
  • 10
    Mariadb/Mysql TEMPORARY表
  • 1
    《MySQL 入门教程》第 05 篇 账户和权限，Java高并发编程详解深入理解pdf
    53
  • 2
    实时计算 Flink版产品使用合集之支持 MySQL 的并行复制吗
    71
  • 3
    实时计算 Flink版产品使用合集之flink-connector-mysql-cdc 和 flink-sql-connector-mysql-cdc有什么区别
    364
  • 4
    Mysql优化提高笔记整理，来自于一位鹅厂大佬的笔记，阿里P7亲自教你
    60
  • 5
    Echarts+vue+java+mysql实现数据可视化
    183
  • 6
    MYSQL--JSON_OBJECT 和 JSON_ARRAYAGG
    178
  • 7
    实时计算 Flink版产品使用合集之flink-connector-mysql-cdc 和 flink-connector-sqlserver-cdc 这两个CDC连接器只能选择其中一个使用吗
    168
  • 8
    MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
    126
  • 9
    MySQL 实现数据分片如何进行数据查询
    258
  • 10
    MySQL各字符集、排序规则的由来、用法，区别和联系
    133

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • One Box： 解读事务与分析一体化数据库 HybridDB for MySQL
  • One Box：解读事务与分析一体化数据库HybridDB for MySQL
  • 如何支撑HTAP场景-HybridDB for MySQL系统架构和技术演进

    • 相关实验场景

    更多
  • 一小时快速掌握 SQL 语法
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    阿里云OSS设置跨域访问