【数据库】常用时间日期函数-日期加减、格式化、取指定部分、与字符串转换、取系统当前日期

一、日期格式化

DATE_FORMAT(date，format)：根据参数对date进行格式化

format的格式：

 %Y 年, 数字, 4 位
 %y 年, 数字, 2 位
 %m 月, 数字(01……12)
 %d 月份中的天数, 数字(00……31)
 %H 小时(00……23)
 %T 时间,24 小时(hh:mm:ss)

二、日期加减

1、Oracle

DATE_ADD(date，INTERVAL number type) 
DATE_SUB(date，INTERVAL number type)
date的格式可以是date也可以说datetime，如果参数date是date格式，则返回date格式结果，如果参数date是datetime格式，则返回datetime格式结果

type格式：

    SECOND 秒
    MINUTE 分钟
    HOUR 小时
    DAY 天
    MONTH 月
    QUARTER
    YEAR 年

2、sqlserver

（1）datediff 求日期差

select datediff(day,'2004-09-01','2004-09-18')

select datediff(d,'2020-02-24 20:59:00','2020-02-25 20:00:00')

（2）dateadd：日期加

例如：向日期加上2天

select dateadd(day,2,'2004-10-15')  --返回：2004-10-17 00:00:00.000

 

3、postgresql

SELECT now()::timestamp + '1 sec';  --加一秒钟

select now()::timestamp + '1 year 1 month 1 day 1 hour 1 min 1 sec';  --加1年1月1天1时1分1秒

4、mysql

求两个时间的差

TIMESTAMPDIFF(SECOND,start_time,submit_time)/60 AS use_time

三、系统当前日期时间

获取日期：

SELECT CURDATE() 
SELECT CURRENT_DATE()

获取时间：

SELECT CURTIME() 
SELECT CURRENT_TIME()

获取日期和时间：

SELECT NOW() 
SELECT CURRENT_TIMESTAMP() 

select getdate()（SqlServer）

pg：now()

四、format格式字符串编号

1、Oracle

STR_TO_DATE(date，format )：

SELECT STR_TO_DATE(‘2021-03-25', '%Y-%m-%d')

2、sqlserver

convert(datetime,'YYYY-MM-DD   HH24:MI:SS')
cast('YYYY-MM-DD   HH24:MI:SS'   as   datetime)

select convert(datetime,'2018-08-08 08:08:08);

  0   或   100   (*)     默认值   mon dd yyyy hh:miAM（或PM）     
  1   或   101   美国   mm/dd/yyyy     
  2   或   102   ANSI   yy.mm.dd     
  3   或   103   英国/法国   dd/mm/yy     
  4   或   104   德国   dd.mm.yy     
  5   或   105   意大利   dd-mm-yy     
  6   或   106   -   dd mon yy     
  7   或   107   -   mon dd, yy     
  8   或   108   -   hh:mm:ss     
  9   或   109   (*)     默认值+  毫秒   mon dd yyyy   hh:mi:ss:mmmAM（或PM）     
  10   或   110   美国   mm-dd-yy     
  11   或   111   日本   yy/mm/dd     
  12   或   112   ISO   yymmdd     
  13   或   113   (*)     欧洲默认值 + 毫秒 dd mon yyyy   hh:mm:ss:mmm(24h)     
  14   或   114   -   hh:mi:ss:mmm(24h)     
  20   或   120   (*)     ODBC   规范   yyyy-mm-dd   hh:mm:ss[.fff]     
  21   或   121   (*)     ODBC   规范（带毫秒）   yyyy-mm-dd   hh:mm:ss[.fff]     
  -   126(***)   ISO8601   yyyy-mm-dd   Thh:mm:ss:mmm（不含空格）     
  -   130*   科威特   dd   mon   yyyy   hh:mi:ss:mmmAM     
  -   131*   科威特   dd/mm/yy   hh:mi:ss:mmmAM  

转日期：

Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
转时间：

Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49

五、获取日期时间的一部分

（一）sqlserver

1. datepart 返回代表指定日期的指定日期部分的整数。

SELECT DATEPART(month, '2004-10-15')  --返回 10

2. datename 返回代表指定日期的指定日期部分的字符串

SELECT datename(weekday, '2004-10-15')  --返回：星期五

3. day(), month(),year() --可以与datepart对照一下

select 当前日期=convert(varchar(10),getdate(),120) ,当前时间=convert(varchar(8),getdate(),114)

select datename(dw,'2004-10-15')

select 本年第多少周=datename(week,'2004-10-15'),今天是周几=datename(weekday,'2004-10-15')

（二）pgsql

DATEPART(month, '2004-10-15')  --返回 10

justify_day()

pg格式转换函数详解：

https://blog.csdn.net/qq_41780234/article/details/125547734

如：select date_part('day','2020-02-25 20:00:00'::TIMESTAMP-'2020-02-24 20:59:00') 

（三）Oracle

1、trunc(date[,fmt])-截取指定格式日期

fmt 指定要截取的日期格式。可选项，不带该参数时，截去时分秒，格式只能分为：一种 y,yy,yyy,yyyy,mm,dd,d；另一种w,iw,ww。

比较常用的是：不带第二个参数，也就是截取到日。

--参数是日期，不带第2个参数
SELECT TRUNC(SYSDATE) FROM DUAL;--2017/6/7

--返回当前日期
SELECT TRUNC(SYSDATE, 'DD') FROM DUAL;--2017/6/7
--返回当年第一天
SELECT TRUNC(SYSDATE, 'YYYY') FROM DUAL;--2017/1/1
SELECT TRUNC(SYSDATE, 'YYY') FROM DUAL;--2017/1/1
SELECT TRUNC(SYSDATE, 'YY') FROM DUAL;--2017/1/1
SELECT TRUNC(SYSDATE, 'Y') FROM DUAL;--2017/1/1
--返回当月第一天
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;--2017/6/1
--返回当前星期的第一天
SELECT TRUNC(SYSDATE, 'D') FROM DUAL;--2017/6/4
--这种方式会报错
SELECT TRUNC(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

计算年龄：TRUNC( months_between( SYSDATE, p.BIRTH_DAY ) / 12 ) AS AGE

2、extract()提取指定部分

提取年月日、时分秒、时区

select 
 extract(year from systimestamp) year
,extract(month from systimestamp) month
,extract(day from systimestamp) day
,extract(minute from systimestamp) minute
,extract(second from systimestamp) second
,extract(timezone_hour from systimestamp) th
,extract(timezone_minute from systimestamp) tm
,extract(timezone_region from systimestamp) tr
,extract(timezone_abbr from systimestamp) ta
from dual

3、interval间隔日期

默认最多两位，可以改变为三位

--间隔1天零1小时零1分零1秒
SELECT interval '1 1:1:1' day to second FROM DUAL

六、时间日期与字符串互转

https://blog.csdn.net/feyehong/article/details/120177756

（一）Oracle

1.1 日期转字符串

to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') 
1.1.1 yyyy年mm月dd日hh24時mi分ss秒
1.1.2 yyyy-mm-dd hh24:mi:ss
1.1.3 yyyyfm-mm-dd hh24:mi:ss
1.1.4 yyyy/mm/dd
1.1.5 yyyymmdd
1.2 字符串转日期

 to_date('20210908', 'yyyymmdd') ，字符串同上

（二）SqlServer

2.1日期转字符串

 CONVERT(varchar (100), GETDATE(), 111)
2.1.1 yyyy/mm/dd
2.1.2 yyyy-mm-dd
2.1.3 yyyymmdd
2.1.4 yyyy-mm-dd hh:mm:ss
2.1.5 yyyy年mm月dd日
2.2 字符串转日期

 CAST(‘字符串日期’ as ‘数据类型-一般为date或者datetime’)
如果字符串日期并不是合法的日期的话,会报错
TRY_CAST(‘字符串日期’ as ‘数据类型-一般为date或者datetime’)
尝试将字符串日期转换为日期类型,如果转换失败的话,会返回NULL

（三）Mysql

3.1 日期转字符串

DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时%i分%s秒' );
3.1.1 yyyy年mm月dd日 hh时ii分ss秒
3.1.2 yyyy-mm-dd hh:ii:ss
3.2 字符串转日期

STR_TO_DATE('2019年01月17日 19时05分05秒', '%Y年%m月%d日 %H时%i分%s秒');