专栏首页SQL实现MySQL 常用日期、时间函数介绍

MySQL 常用日期、时间函数介绍

1 日期格式化

MySQL 使用 DATE_FORMAT() 格式化日期,和格式化相关的函数还有 STR_TO_DATE()TIME_FORMAT()

1.1 DATE_FORMAT(date,format)

DATE_FORMAT 根据 format 串格式化 date 值,每个格式符前必须包含 %

下表列出经常用到的格式:

格式

描述

%a

缩写星期名 (Sun..Sat)

%b

缩写月名 (Jan..Dec)

%c

月, 数值(0..12)

%D

带有英文前缀的月中的天 (0th, 1st, 2nd, 3rd, …)

%d

一个月里面的某一天, 数值(00..31)

%e

一个月里面的某一天, 数值(0..31)

%f

微妙(000000..999999)

%H

小时(00..23)

%h

小时(01..12)

%I

小时(01..12)

%i

分钟, 数值(00..59)

%j

一年中的某一天 (001..366)

%k

小时(0..23)

%l

小时(1..12)

%M

月名 (January..December)

%m

月份, 数值(00..12)

%p

AM or PM

%r

时间, 12-小时(hh:mm:ss AM 或者PM)

%S

秒(00..59)

%s

秒(00..59)

%T

时间, 24-小时(hh:mm:ss)

%W

星期名 (Sunday..Saturday)

%w

周几 (0=Sunday..6=Saturday)

%Y

年, 数值, 4 个数字

%y

年, 数值, 2 个数字

日期格式化的例子

#输出格式如:2020-01-23 00:24:12
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %T')

#输出格式如:20200123
SELECT DATE_FORMAT(NOW(),'%Y%m%d')

#本月第一天,如:2020-01-01
SELECT DATE_FORMAT(NOW(),'%Y-%m-01')

1.2 TIME_FORMAT(time,format)

TIME_FORMAT()用法与 DATE_FORMAT()函数类似,但是格式字符串可能仅包含小时,分钟,秒和微秒的格式说明符。其他说明符产生 NULL 值或 0。

如果时间值中的小时部分大于23,则 %H%k 小时格式说明符会产生一个比通常范围 0..23 大的值。其它小时格式说明符会产生模 12 的小时值。

SELECT TIME_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');
SELECT TIME_FORMAT('25:12:23', '%H %k %h %I %l');
-------------------------------------------------
0000-00-00 11:22:08
25 25 01 01 1

1.3 STR_TO_DATE(str,format)

将字符串转换成日期或者时间,或者日期时间,取决于给定的 format 包含了哪部分内容。如果 format 包含了日期和时间格式,将返回 datetime 类型的值;如果只包含日期格式,则返回 date 类型的值;如果只包含了时间格式,将返回 time 类型的值。

SELECT STR_TO_DATE('01/23/2020','%m/%d/%Y');
SELECT STR_TO_DATE('Jan 23,2020','%M %d,%Y');
SELECT STR_TO_DATE('23,01,2020 00:28:12','%d,%m,%Y %h:%i:%s');
# 未指定日期或者时间部分的值为 0
SELECT STR_TO_DATE('abc','abc');
--------------------------------------------------------------
2020-01-23
2020-01-23
2020-01-23 00:28:12
0000-00-00

1.4 GET_FORMAT()

GET_FORMAT() 有两个参数,第一个参数是DATETIMEDATETIMETIMESTAMP 中的一种,第二参数可以是EURUSAJISISOINTERNAL 等值。

该函数返回日期/时间的格式字符串,与 DATE_FORMAT()STR_TO_DATE()函数结合使用时非常有帮助。

函数调用

结果

GET_FORMAT(DATE,'USA')

'%m.%d.%Y'

GET_FORMAT(DATE,'JIS')

'%Y-%m-%d'

GET_FORMAT(DATE,'ISO')

'%Y-%m-%d'

GET_FORMAT(DATE,'EUR')

'%d.%m.%Y'

GET_FORMAT(DATE,'INTERNAL')

'%Y%m%d'

GET_FORMAT(DATETIME,'USA')

'%Y-%m-%d %H.%i.%s'

GET_FORMAT(DATETIME,'JIS')

'%Y-%m-%d %H:%i:%s'

GET_FORMAT(DATETIME,'ISO')

'%Y-%m-%d %H:%i:%s'

GET_FORMAT(DATETIME,'EUR')

'%Y-%m-%d %H.%i.%s'

GET_FORMAT(DATETIME,'INTERNAL')

'%Y%m%d%H%i%s'

GET_FORMAT(TIME,'USA')

'%h:%i:%s %p'

GET_FORMAT(TIME,'JIS')

'%H:%i:%s'

GET_FORMAT(TIME,'ISO')

'%H:%i:%s'

GET_FORMAT(TIME,'EUR')

'%H.%i.%s'

GET_FORMAT(TIME,'INTERNAL')

'%H%i%s'

SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'ISO'));
SELECT STR_TO_DATE('2020-01-28 16:33:00',GET_FORMAT(TIMESTAMP,'ISO'));
----------------------------------------------------------------------
2020-01-28
2020-01-28 16:33:00

2 日期计算

2.1 DATE_ADD(date,INTERVAL expr unit)

date_add() 可用于日期、时间的加减计算,类似的函数还有 date_sub()adddate()addtime()subdate()subtime()

date_add() 接收两个参数,第一个参数可以是 date 类型或者 datetime 类型,第二个参数是个间隔值,表示将在第一个参数的基础上增加或者减少某个单位时间的值。

SELECT DATE_ADD('2020-01-28',INTERVAL 1 DAY);
SELECT DATE_ADD('2020-01-28',INTERVAL -1 YEAR);
SELECT DATE_ADD('2020-01-28 15:28:01',INTERVAL 2 HOUR);
SELECT DATE_ADD('2020-01-28 15:28:01',INTERVAL '1 2' DAY_HOUR);
---------------------------------------------------------------
2020-01-29
2019-01-28
2020-01-28 17:28:01
2020-01-29 17:28:01

2.2 DATEDIFF(expr1,expr2)

DATEDIFF() 函数用于计算两个日期之间相差的天数,计算方式是 expr1 - expr2。expr1 和 expr2 是日期或日期时间表达式,在计算中仅使用值的日期部分。

SELECT DATEDIFF('2020-01-28','2020-01-27');
SELECT DATEDIFF('2020-01-28 00:00:00','2020-01-27 23:59:59');
-------------------------------------------------------------
1
1

3 获取特定日期

3.1 获取当前日期

获取当前日期可以使用 CURDATE()CURRENT_DATE()SYSDATE() 等函数。

同样的,获取当前时间可以使用 CURTIME()CURRENT_TIME()NOW()CURRENT_TIMESTAMP()、SYSDATE()、LOCALTIME()LOCALTIMESTAMP()

3.2 获取年月日时分秒

SET @now:='2020-01-28 17:28:02';
SELECT YEAR(@now);
SELECT MONTH(@now);
SELECT DAY(@now);
SELECT HOUR(@now);
SELECT MINUTE(@now);
SELECT SECOND(@now);
----------------------------------
2020
1
28
17
28
2

3.3 其它

# 获取本月第一天
SELECT DATE_SUB(CURDATE(), INTERVAL DAY(CURDATE()) - 1 DAY);
# 获取本月最后一天
SELECT LAST_DAY(CURDATE());
# 获取今年第一天
SELECT DATE_SUB(CURDATE(), INTERVAL DAYOFYEAR(CURDATE()) - 1 DAY);
SELECT STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-01-01'),'%Y-%m-%d');
# 获取今年最后一天
SELECT DATE_ADD(DATE_SUB(CURDATE(), INTERVAL DAYOFYEAR(CURDATE()) DAY),INTERVAL 1 YEAR);
SELECT STR_TO_DATE(CONCAT(YEAR(CURDATE()),'-12-31'),'%Y-%m-%d');

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-01-28

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL 动态排序

    有时候,我们希望依据某些条件逻辑来排序。比如,对于员工表 emp,我们希望按照薪资(sal )从低到高排序。有一个要求,处于管理岗位的职工排在普通员工的后面,即...

    白日梦想家
  • SQL 将多列的数据转到一列

    如题。假设我们要把 emp 表中的 ename、job 和 sal 字段的值整合到一列中,每个员工的数据(按照 ename -> job -> sal 的顺序展...

    白日梦想家
  • SQL 打印成绩单

    这是 HackerRank 上的一道中级难度的 SQL 挑战题,实际上考察的是动态排序。

    白日梦想家
  • 当SaaS遇上AI,就像“傻子”遇上“爱”

    ? 11月29日-30日,腾讯产业加速器首次聚合闭门辅导在南京举行,作为腾讯云启智慧产业生态平台的重要组成部分,腾讯产业加速器此次闭门辅导意味着腾讯在AI和S...

    腾讯SaaS加速器
  • 001.LVM简介

    LVM是 Logical Volume Manager(逻辑卷管理)的简写,它由Heinz Mauelshagen在Linux 2.4内核上实现。LVM将一个或...

    木二
  • 统计文件中出现的单词次数

    这里以kevin.txt文件内容(单词由一个或多个空格字符分隔)为例进行简单说明 [root@centos6-test06 ~]# cat /root/kevi...

    洗尽了浮华
  • linux下rsync和tar增量备份梳理

    前面总结过一篇全量备份/增量备份/差异备份说明,下面介绍下linux下rsync和tar两种增量备份的操作记录: 1)rsync备份 rsync由于本身的特性,...

    洗尽了浮华
  • Serverless Component 介绍和使用指南

    我们希望通过 Serverless Components 让广大开发者更加便捷,平滑的联动云厂商提供的种种服务。

    Aceyclee
  • 对标A11,高通或将于今年12月发布骁龙845

    VRPinea
  • 高通骁龙845的问世,会改变现有的VR格局吗?

    VRPinea

扫码关注云+社区

领取腾讯云代金券