送给不会计算时间的你，MySQL日期函数总结

已于 2022-07-07 08:24:13 修改
阅读量440 收藏 1
点赞数
分类专栏： 关系型数据库 文章标签： mysql 数据库 database
于 2022-01-22 17:49:11 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u014163312/article/details/122638571
版权

文章目录


平时的开发过程中，经常会涉及到在SQL中计算时间或者表示时间。借助MySQL中内置的一些函数，可以方便的操作时间，获得想要的结果。本篇博客主要介绍常用的一些日期和时间函数等。

基本函数

获得当前日期+时间(date + time)函数

now()

由于now() 函数简短易记，建议总是使用 now()。

以下3个函数也都可以获得当前日期+时间。

current_timestamp() 和 current_timestamp

localtime() 和 localtime

localtimestamp() 和 localtimestamp

例如：

SELECT now();   // 2022-01-22 15:54:41
SELECT CURRENT_TIMESTAMP;
SELECT LOCALTIMESTAMP;
SELECT LOCALTIME;

sysdate()

sysdate() 日期时间函数跟 now() 类似。

SELECT SYSDATE();

不同之处在于： now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。
可以通过下面的sql测试：

select NOW(), sysdate(), sleep(3), sysdate(), NOW();

获得当前日期(date)函数：

curdate()

其中，下面的两个日期函数等同于 curdate()

current_date() 和 current_date

获得当前时间(time)函数：

curtime()

其中，下面的两个时间函数等同于 curtime()

current_time() 和 current_time

获得当前 UTC 日期时间函数

因为我国位于东八时区，所以本地时间 = UTC 时间 + 8 小时。
UTC 时间在业务涉及多个国家和地区的时候，非常有用。

utc_date()

utc_time()

utc_timestamp()

select UTC_DATE();
select UTC_TIME();
select UTC_TIMESTAMP();

MySQL 日期时间 Extract(选取) 函数

选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒

set @dt = '2021-09-10 07:15:30.123456';

select date(@dt); -- 2021-09-10

select time(@dt); -- 07:15:30.123456

select year(@dt); -- 2021

-- 季度
select quarter(@dt); -- 3

select month(@dt); -- 9

select week(@dt); -- 36

select day(@dt); -- 10

select hour(@dt); -- 7

select minute(@dt); -- 15

select second(@dt); -- 30

select microsecond(@dt); -- 123456

MySQL Extract() 函数，可以上面实现类似的功能

set @dt = '2021-09-10 07:15:30.123456';

select extract(year from @dt); -- 2021

select extract(quarter from @dt); -- 3

select extract(month from @dt); -- 9

select extract(week from @dt); -- 36

select extract(day from @dt); -- 10

select extract(hour from @dt); -- 7

select extract(minute from @dt); -- 15

select extract(second from @dt); -- 30

select extract(microsecond from @dt); -- 123456

select extract(year_month from @dt); -- 202109

select extract(day_hour from @dt); -- 1007

select extract(day_minute from @dt); -- 100715

select extract(day_second from @dt); -- 10071530

select extract(day_microsecond from @dt); -- 10071530123456

select extract(hour_minute from @dt); -- 715

select extract(hour_second from @dt); -- 71530

select extract(hour_microsecond from @dt); -- 71530123456

select extract(minute_second from @dt); -- 1530

select extract(minute_microsecond from @dt); -- 1530123456

select extract(second_microsecond from @dt); -- 30123456

MySQL Extract() 函数除了没有date()，time() 的功能外，其他功能一应俱全。并且还具有选取day_microsecond等功能。
注意，这里不是只选取 day 和 microsecond，而是从日期的 day 部分一直选取到 microsecond 部分。

MySQL 返回星期和月份名称函数

dayname()

monthname()

set @dt = '2021-02-01 7:15';

select DAYNAME(@dt);
-- Monday

select MONTHNAME(@dt);
-- February

MySQL dayof… 函数

分别返回日期参数，在一周、一月、一年中的位置。

dayofweek()

注意，1 = Sunday, 2 = Monday, …, 7 = Saturday

dayofmonth()

dayofyear()

set @dt = '2021-02-01';

select dayofweek(@dt); -- 2
-- 星期天为一周的第一天

select dayofmonth(@dt); -- 1

select dayofyear(@dt); -- 32

MySQL week… 函数

week()

weekofyear()

dayofweek()

weekday()

yearweek()

MySQL 的 YEARWEEK 是获取年份和周数的一个函数，函数形式为:

YEARWEEK(date,[mode])

其中第二个参数是 mode ，具体指的意思如下

ModeFirst day of weekRange Week1 is the first week …
0Sunday0-53with a Sunday in this year
1Monday0-53with more than 3 days this year
2Sunday1-53with a Sunday in this year
3Monday1-53with more than 3 days this year
4Sunday0-53with more than 3 days this year
5Monday0-53with a Monday in this year
6Sunday1-53with more than 3 days this year
7Monday1-53with a Monday in this year

weekofyear函数是计算出当前日期所在周数，和YEARWEEK(‘日期’,1)的周数一致，但YEARWEEK(‘日期’,1)在小于10的时候，不带0。

set @dt = '2021-02-01';

select week(@dt); -- 5

select week( @dt, 3 ); -- 5

select weekofyear(@dt); -- 5

select dayofweek(@dt); -- 2

select weekday(@dt); -- 0

select yearweek(@dt); -- 202105

MySQL week() 函数，可以有两个参数，具体可看手册。
weekofyear() 和 week() 一样，都是计算“某天”是位于一年中的第几周。 weekofyear(@dt) 等价于 week(@dt,3)。

MySQL weekday() 函数和 dayofweek() 类似，都是返回“某天”在一周中的位置。不同点在于参考的标准：
weekday：(0 = Monday, 1 = Tuesday, …, 6 = Sunday)；
dayofweek：(1 = Sunday, 2 = Monday, …, 7 = Saturday)；

MySQL yearweek() 函数，返回 year(2021) + week 位置(05)。

MySQL last_day() 函数：返回月份中的最后一天。

set @dt = '2021-02-01 7:15';

select LAST_DAY(@dt);
-- 2021-02-28

MySQL 日期时间计算函数

MySQL 为日期增加一个时间间隔：date_add()

set @dt = now();

select date_add(@dt, interval 1 day); -- add 1 day

select date_add(@dt, interval -1 day); -- sub 1 day

select date_add(@dt, interval 1 hour); -- add 1 hour

select date_add(@dt, interval 1 minute); 

select date_add(@dt, interval 1 second);

select date_add(@dt, interval 1 microsecond);

select date_add(@dt, interval 1 week);

select date_add(@dt, interval 1 month);

select date_add(@dt, interval 1 quarter);

select date_add(@dt, interval 1 year);

select date_add(@dt, interval '01:15:30' hour_second);

select date_add(@dt, interval '1 01:15:30' day_second);

MySQL adddate(), addtime()函数，可以用 date_add() 来替代。
建议：总是使用 date_add() 日期时间函数来替代 adddate(), addtime()。

MySQL 为日期减去一个时间间隔：date_sub()

MySQL date_sub() 日期时间函数 和 date_add() 用法一致，不再赘述。
另外，MySQL 中还有两个函数 subdate(), subtime()，建议，用 date_sub() 来替代。

MySQL 日期相减函数：datediff(date1,date2)

语法：传入两个日期参数，比较DAY天数，第一个参数减去第二个参数的天数值。

select DATEDIFF( NOW(),'2021-01-01');

MySQL 时间相减函数：timediff(time1,time2)

返回两个时间相减得到的差值，time1-time2

SELECT TIMEDIFF( NOW(), '2021-01-01 00:00' );

注意： timediff(time1,time2) 函数的两个参数类型必须相同。

如果任一参数为NULL，TIMEDIFF函数将返回NULL。
如果传递两个不同类型的参数，一个是DATETIME，另一个是TIME，TIMEDIFF函数也返回NULL。

MySQL 日期转换函数、时间转换函数

MySQL (时间、秒)转换函数：time_to_sec(time), sec_to_time(seconds)

select time_to_sec('01:00:05'); -- 3605

select sec_to_time(3605); -- '01:00:05'

MySQL (日期、天数)转换函数：to_days(date), from_days(days)

select to_days('0000-00-00'); -- 0

select to_days('2008-08-08'); -- 733627

select from_days(0); -- '0000-00-00'

select from_days(733627); -- '2008-08-08'

MySQL 获得国家地区时间格式函数：get_format()

MySQL get_format() 语法：

get_format(date|time|datetime, ‘eur’|‘usa’|‘jis’|‘iso’|‘internal’

MySQL get_format() 用法的全部示例：

select get_format(date,'usa') ; -- '%m.%d.%Y'

select get_format(date,'jis') ; -- '%Y-%m-%d'

select get_format(date,'iso') ; -- '%Y-%m-%d'

select get_format(date,'eur') ; -- '%d.%m.%Y'

select get_format(date,'internal') ; -- '%Y%m%d'

select get_format(datetime,'usa') ; -- '%Y-%m-%d %H.%i.%s'

select get_format(datetime,'jis') ; -- '%Y-%m-%d %H:%i:%s'

select get_format(datetime,'iso') ; -- '%Y-%m-%d %H:%i:%s'

select get_format(datetime,'eur') ; -- '%Y-%m-%d %H.%i.%s'

select get_format(datetime,'internal') ; -- '%Y%m%d%H%i%s'

select get_format(time,'usa') ; -- '%h:%i:%s %p'

select get_format(time,'jis') ; -- '%H:%i:%s'

select get_format(time,'iso') ; -- '%H:%i:%s'

select get_format(time,'eur') ; -- '%H.%i.%s'

select get_format(time,'internal') ; -- '%H%i%s'

MySQL get_format() 函数在实际中用到机会的比较少。

MySQL Str to Date (字符串转换为日期)函数：str_to_date(str, format)

select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09

select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09

select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09

select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30

select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

可以看到，str_to_date(str,format) 转换函数，可以把一些杂乱无章的字符串转换为日期格式。另外，它也可以转换为时间。“format” 可以参看 MySQL 手册。

MySQL Date/Time to Str(日期/时间转换为字符串)函数：date_format(date, format), time_format(time, format)

MySQL 日期、时间转换函数：date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。

日期格式化案例：

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'); /*2021-02-26*/
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'); /*2021-02-26 10:47:50*/
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i:%s %p'); /*2021-02-26 10:47:50 AM*/
SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 12 HOUR),'%Y-%m-%d %H:%i:%s'); /*2021-02-26 22:47:50*/
SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 12 HOUR),'%Y-%m-%d %h:%i:%s %p'); /*2021-02-26 10:47:50 PM*/
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H:%i:%s'); /*2021年02月26日 10:47:50*/

DATE_FORMAT 转换时注意时区。

日期格式字符串:

格式描述
%a缩写星期名
%b缩写月名
%c月，数值
%D带有英文前缀的月中的天
%d月的天，数值(00-31)
%e月的天，数值(0-31)
%f微秒
%H小时 (00-23)
%h小时 (01-12)
%I大写i。小时 (01-12)
%i分钟，数值(00-59)
%j年的天 (001-366)
%k小时 (0-23)
%l小写L。小时 (1-12)
%M月名
%m月，数值(00-12)
%pAM 或 PM
%r时间，12-小时（hh:mm:ss AM 或 PM）
%S秒(00-59)
%s秒(00-59)
%T时间, 24-小时 (hh:mm:ss)
%U周 (00-53) 星期日是一周的第一天
%u周 (00-53) 星期一是一周的第一天
%V周 (01-53) 星期日是一周的第一天，与 %X 使用
%v周 (01-53) 星期一是一周的第一天，与 %x 使用
%W星期名
%w周的天 （0=星期日, 6=星期六）
%X年，其中的星期日是周的第一天，4 位，与 %V 使用
%x年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y年，4 位
%y年，2 位

MySQL 拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2001,31); -- '2001-01-31'

select makedate(2001,32); -- '2001-02-01'

select maketime(12,15,30); -- '12:15:30'

MySQL 时间戳(Timestamp)函数

MySQL 获得当前时间戳函数：current_timestamp, current_timestamp()

MySQL (Unix 时间戳、日期)转换函数：

unix_timestamp(),

unix_timestamp(date),

from_unixtime(unix_timestamp),

from_unixtime(unix_timestamp,format)

MySQL 时间戳(timestamp)转换、增、减函数：

timestamp(date) – date to timestamp

timestamp(dt,time) – dt + time

timestampadd(unit,interval,datetime_expr) –

timestampdiff(unit,datetime_expr1,datetime_expr2) –

时间差函数：timestampdiff

MySQL timestampdiff() 函数就比 datediff() 功能强多了，datediff() 只能计算两个日期(date)之间相差的天数。

语法：timestampdiff(interval, datetime1, datetime2)

结果：返回（时间2-时间1）的时间差，结果单位由interval参数给出。

frac_second 毫秒（低版本不支持，用second，再除于1000）
second 秒
minute 分钟
hour 小时
day 天
week 周
month 月
quarter 季度
year 年

注意：MySQL 5.6之后才支持毫秒的记录和计算，如果是之前的版本，最好是在数据库除datetime类型之外的字段，再建立用于存储毫秒的int字段，然后自己进行转换计算。
所有格式

SELECT TIMESTAMPDIFF(FRAC_SECOND,'2012-10-01','2013-01-13'); # 暂不支持
SELECT TIMESTAMPDIFF(SECOND,'2012-10-01','2013-01-13'); # 8985600
SELECT TIMESTAMPDIFF(MINUTE,'2012-10-01','2013-01-13'); # 149760
SELECT TIMESTAMPDIFF(HOUR,'2012-10-01','2013-01-13'); # 2496
SELECT TIMESTAMPDIFF(DAY,'2012-10-01','2013-01-13'); # 104
SELECT TIMESTAMPDIFF(WEEK,'2012-10-01','2013-01-13'); # 14
SELECT TIMESTAMPDIFF(MONTH,'2012-10-01','2013-01-13'); # 3
SELECT TIMESTAMPDIFF(QUARTER,'2012-10-01','2013-01-13'); # 1
SELECT TIMESTAMPDIFF(YEAR,'2012-10-01','2013-01-13'); # 0

MySQL 时区(timezone)转换函数convert_tz(dt, from_tz, to_tz)

select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00

时区转换也可以通过 date_add, date_sub, timestampadd 来实现。

select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00

select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00

select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00
拾年一剑
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL获取当前时间
chenzhi7665的博客
09-18 7981
写项目的时候功能需要在sql语句中获取当前时间,于是想到了now();然后又想到了当初不会这个的时候看的一篇写的很好的博客，于是就去百度给搜了出来。 原地址链接 -->http://blog.sina.com.cn/s/blog_6d39dc6f0100m7eo.html 1....
SQL获取当前时间(日期)
05-22
--获取当前日期(如：yyyymmdd) select CONVERT (nvarchar(12),GETDATE(),112)
SQL Server 中日期比较
M翠峰如簇的专栏
05-15 3709
1. 当前系统日期、时间 select getdate() 2. dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值 例如：向日期加上2天 select dateadd(day,2,'2004-10-15') --返回：2004-10-17 00:00:00.000 3. datediff 返回跨两个指定日期的日期和时间边界数。 select dat
SQL获取目标时间点或日期的方法
weixin_50853979的博客
05-28 2万+
1.时间函数直接获取系统时间点、日期 （1）NOW函数 ①语法：Now() ②作用：获取当前系统的日期和时间点。 例：输入“=NOW()”返回“2022-4-16 16:23:26”，空格左边是日期，右边是时间点。 （2）GETDATE函数 ①语法：GETDATE() ②作用： 例：输入“=TODAY（）”返回“2022-4-16 16:23:26.243” （3）CURDATE函数 ①语法：CURDATE() ②作用：获取系统当前日期。 例： （4）CURTIME函数 ①语法
sql获取当前时间
最新发布
Pumpkin_ln的博客
09-13 4470
sql获取当前时间
MySQL 日期时间函数常用总结
01-21
获得当前日期+时间（date + time）1.1 函数：now()  相关函数：current_timestamp()，localtime()，localtimestamp()  举例说明：  2. 获得当前日期（date）  函数：curdate()  相关函数：current_date()...
mysql 获取当前日期函数及时间格式化参数详解
09-10
主要介绍了mysql 获取当前日期函数now()及时间格式化DATE_FROMAT函数以及参数详细介绍,需要的朋友可以参考下
php Mysql日期和时间函数集合
10-30
本文是脚本之家小编给大家收集的比较全的Mysql日期和mysql时间函数，感兴趣的朋友跟随一起看看吧
mysql常用函数实例总结【聚集函数、字符串、数值、时间日期处理等】
12-14
本文实例讲述了mysql常用函数。分享给大家供大家参考，具体如下： 本文内容： mysql函数的介绍 聚集函数 avg count max min sum 用于处理字符串的函数 合并字符串函数：concat(str1,str2,str3…) 比较字符...
SQL中常用的日期函数
weixin_43422340的博客
03-19 4708
SQL中常用的日期函数 1.getdate()返回当前系统日期 select getdate() --2021-03-19 18:30:33.563 2.dateadd(日期部分，常数，日期) 返回将日期的指定日期部分加常数后的结果 日期部分可以写成： select dateadd(yy,1,getdate()) --等同于select dateadd(year,1,getdate()) --2022-03-19 18:30:33.563 select dateadd(yy,-2,getdate())
SQL获得当前时间函数（MySQL）
SDFGSSFSDFS的博客
06-15 7073
MySQL获取时间函数
SQL常用（查询日期）语句
人在杰途的博客
06-03 9595
SQL常用日期查询语句及显示格式设置SQL常用日期查询语句及显示格式设置1.查询当前年、月、周相关时间1.1.查询当前年份1.2.查询当前年份第几天1.3.查询当前月份第几天1.4.查询当前周的星期几2.查询当前第几季度。
SQL读取系统时间的语法（转）
Jasper1207的博客
06-19 2313
--获取当前日期(如：yyyymmdd) select CONVERT (nvarchar(12),GETDATE(),112) --获取当前日期(如：yyyymmdd hh:MM:ss)select GETDATE() --获取当前日期(如：yyyy-mm-dd)Select Datename(year,GetDate())+'-'+Datename(month,GetDate(...
sql时间函数
weixin_44740082的博客
07-22 1037
文章目录1.CURDATE() 当前日期2.CURTIME() 当前时间3.TIMESTAMPDIFF4.DATEDIFF5.week、yearweek、dayofweek6.DATE_FORMAT() 1.CURDATE() 当前日期 2.CURTIME() 当前时间 3.TIMESTAMPDIFF 有参数设置，可以精确到天（DAY）、小时（HOUR），分钟（MINUTE）和秒（SECOND），使用起来比datediff函数更加灵活。对于比较的两个时间，时间小的放在前面，时间大的放在后面。 TIMES
MySQL获取当前日期、时间、时间戳函数
热门推荐
Burn_的博客
07-24 6万+
MySQL获取及转换日期、时间、时间戳函数1.MySQL 获得当前日期时间 函数1.1获得当前日期（date）函数：curdate()1.2获得当前时间（time）函数：curtime()1.3获得当前日期+时间（date + time）函数：now()1.4获得当前日期+时间（date + time）函数：sysdate()1.5MySQL 获得当前时间戳函数：current_timestamp, current_timestamp()2.MySQL 日期转换函数、时间转换函数2.1MySQL Date/
数据库---MYSQL获取当前时间
fly__ing的博客
03-26 870
MYSQL获取当前时间 1.SYSDATE()函数 格式为“YYYY-MM-DD HH：MM：SS”或“YYYYMMDDHHMMSS”，具体格式根据函数用在字符串或数字语境中而定。 如： UPDATE table_test SET update_time = DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%s') WHERE some_parameter = 'test' 2.NOW() 函数 如: UPDATE table_test SET update_t
sql语句获取当前的时间
我只是心态好
03-24 3207
UPDATE tl_check SET is_check = 0,update_time = NOW(),is_lost=0 WHERE is_delete=0 and book_no=#{bookNo}
sql中常见的日期获取
Test architect
11-24 3995
4.本月的第一个星期一。–2.显示本月最后一天。–3.上个月的最后一天。
[SQL挖掘机] - 日期函数 - current_date
统计小白er & paul_work
07-27 979
current_date函数时，它通常被用来获取当前的日期。它返回一个表示当前日期的日期值。需要注意的是，current_date函数返回的日期值不包含时间部分，仅包含年、月、日。current_date函数没有任何参数，因此只需简单地调用它即可获得系统当前日期。这将返回当前日期，例如：“2023-07-26”。
mysql 计算日期函数datediff
08-21
总结起来，MySQL的DATEDIFF函数用于计算两个日期之间的天数差，而忽略时间的部分。<span class="em">1</span><span class="em">2</span><span class="em">3 #### 引用[.reference_title] - *1* *2* *3* [[Mysql] ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值