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

【mysql】日期和时间函数

2022-03-14 152
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS PostgreSQL，集群系列 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 版日期时间函数
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
兮动人
目录
相关文章
技术蜜糖罐
|
7月前
|
关系型数据库 MySQL 数据挖掘
轻松入门MySQL：利用MySQL时间函数优化产品销售数据统计与分析（9）
轻松入门MySQL：利用MySQL时间函数优化产品销售数据统计与分析（9）
技术蜜糖罐
199 0
见贤思₇
|
7月前
|
关系型数据库 MySQL 数据挖掘
MYSQL日期与时间函数的实用技巧
MYSQL日期函数与时间函数是数据库操作的关键工具，可轻松处理、查询、比较和格式化日期时间数据。它们能提取日期的年、月、日等部分，便于筛选和统计；同时，也能处理时间数据，如计算时间差、获取当前时间，助力用户更好地管理时间信息。掌握这些函数，不仅能提升数据库操作效率，还能为数据分析和报表生成提供有力支持。无论初学者还是资深数据库管理员，精通MYSQL的日期和时间函数都至关重要，以满足各种数据处理需求，确保数据的准确性和高效性。
见贤思₇
397 0
蓝易云
|
9天前
|
关系型数据库 MySQL
mysql中单独获取已知日期的年月日其中之一
通过上述内容，您应该能够更好地在MySQL中处理和提取日期和时间信息，以满足各种业务需求。
蓝易云
26 5
2G冲浪词条
|
1月前
|
关系型数据库 MySQL
Mysql 中日期比较大小的方法有哪些？
在 MySQL 中，可以通过多种方法比较日期的大小，包括使用比较运算符、NOW() 函数、DATEDIFF 函数和 DATE 函数。这些方法可以帮助你筛选出特定日期范围内的记录，确保日期格式一致以避免错误。
2G冲浪词条
67 1
小Tomkk
|
2月前
|
存储 自然语言处理 关系型数据库
mysql 8.0 日期维度表生成（可运行）
mysql 8.0 日期维度表生成（可运行）
小Tomkk
64 2
肾透侧视攻城狮
|
1月前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列（7）作者——LJS[含MySQL 聚合、数学、字符创、日期、控制流函数等使用详解；注意点及常见报错问题所对应的解决方法]
MySQL 聚合、数学、字符创、日期、控制流函数等使用详解；注意点及常见报错问题所对应的解决方法
肾透侧视攻城狮
36 0
小Tomkk
|
2月前
|
关系型数据库 MySQL 数据处理
企业级应用 mysql 日期函数变量，干货已整理
本文详细介绍了如何在MySQL8.0中使用DATE_FORMAT函数进行日期格式的转换，包括当日、昨日及不同时间段的数据获取，并提供了实际的ETL应用场景和注意事项，有助于提升数据处理的灵活性和一致性。
小Tomkk
50 0
你的小笔记本YY
|
7月前
|
关系型数据库 MySQL Linux
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】（代码演示＆可cv代码）
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】（代码演示＆可cv代码）
你的小笔记本YY
477 4
【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】（代码演示＆可cv代码）
快捷资讯
|
6月前
|
关系型数据库 MySQL 数据库
mysql之日期时间函数
SELECT SECOND('12:45:31'); -- 结果: 31 请注意，以上结果取决于实际查询执行的时间和日期。在实际的数据库操作中，通常会使用列名作为参数，而不是直接使用硬编码的日期或时间值。例如： SELECT YEAR(birthdate) FROM employees; 这将返回 employees 表中每位员工的 birthdate 列的年份。日期和时间函数在处理数据时非常有用，特别是当你需要对日期和时间进行操作或计算时。 ————————————————
快捷资讯
47 0
3hvg2zg3cgsi6
|
6月前
|
存储 关系型数据库 MySQL
MySQL 日期表制作
MySQL 日期表制作
3hvg2zg3cgsi6
78 0

热门文章

最新文章

  • 1
    MySQL数据库下载安装教程（Windows&Linux）
  • 2
    MySQL常用函数：IF、SUM等用法
  • 3
    Python处理数据库：MySQL与SQLite详解 | python小知识
  • 4
    从新手到高手：彻底掌握MySQL表死锁
  • 5
    MySQL事务隔离级别及默认隔离级别的设置
  • 6
    Liunx部署java项目Tomcat、Redis、Mysql教程
  • 7
    Mysql索引：深入理解InnoDb聚集索引与MyisAm非聚集索引
  • 8
    MySQL导入.sql文件后数据库乱码问题
  • 9
    MySQL的插件式认证到底是干什么的？
  • 10
    数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
  • 1
    Java医院绩效考核系统源码B/S+avue+MySQL助力医院实现精细化管理
    82
  • 2
    Linux对MySQL语法校验
    95
  • 3
    【MySQL系列笔记】分库分表
    231
  • 4
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    170
  • 5
    服务器Linux系统配置mysql数据库主从自动备份
    221
  • 6
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    422
  • 7
    golang使用gorm操作mysql3，数据查询
    100
  • 8
    golang使用gorm操作mysql2
    68
  • 9
    golang使用gorm操作mysql1
    118
  • 10
    Hbase与MySQL对比，区别是什么？
    326

    • 相关课程

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

    • 相关电子书

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

    • 相关实验场景

    更多
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）
  • 手动部署MySQL数据库（CentOS 7）

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    手把手教你白嫖阿里云服务器(免费领服务器)