12類oracle日期函數(shù)超全面總結(jié)

前言

在使用oracle數(shù)據(jù)庫(kù)過程中，對(duì)日期的使用不可避免，那oracle中的日期函數(shù)有哪些呢？本篇就日期函數(shù)進(jìn)行整理了，不一定全部了解記住，但是要做到心中有數(shù)，以后在寫腳本的時(shí)候就不會(huì)繞彎子了。

1、sysdate、current_date及systimestamp

sysdate

oracle使用計(jì)算機(jī)操作系統(tǒng)的當(dāng)前日期和時(shí)間，是通過sysdate函數(shù)實(shí)現(xiàn)的,在任何可以使用oracle函數(shù)的地方都可以使用sysdate函數(shù)，可以將它視為每個(gè)表的一個(gè)隱藏列或者偽列。也就是說即使一張表沒有sysdate這個(gè)字段，也可以如下這樣查詢。

select sysdate from all_users;
>> 2022-10-15 23:09:37

current_date

此字段返回的是當(dāng)前會(huì)話的時(shí)區(qū)的系統(tǒng)日期，我們一般使用的是東八區(qū)的時(shí)間，如果將當(dāng)前會(huì)話時(shí)間修改成東九區(qū)試試：

alter session set time_zone='+09:00'; 
select current_date, sysdate from dual;
>> 2022-10-15 10:11:51  2022-10-15 09:11:51

通過例子就很明顯看出兩者區(qū)別了，一個(gè)依賴于時(shí)區(qū)，一個(gè)不依賴。

systimestamp

函數(shù)systimestamp，它返回的是本機(jī)的系統(tǒng)時(shí)間，精確到微秒，并包含了當(dāng)前時(shí)區(qū)，它跟會(huì)話的時(shí)區(qū)無(wú)關(guān)。

select  systimestamp  from dual
>> 2022-10-15 23:15:43.699000 +08:00 

2、兩個(gè)日期的差

如何計(jì)算兩個(gè)日期的差呢，可以直接進(jìn)行相減，返回的數(shù)值單位是天，也可以用一個(gè)日期加減某個(gè)數(shù)值代表n天后(前)。

比如十天后發(fā)工資，那么十天后是哪一天呢?

select  sysdate+10 from dual 
>> 2022-10-25 23:18:16

再比如今年春節(jié)是2023-01-22，那么距離過年還有哪個(gè)多少天呢？

select to_date('2023-01-22','yyyy-mm-dd') - sysdate from dual  
>> 98.02703703703703703703703703703703703704

暈，還有3個(gè)月呢~

3、添加月份、減少月份

既然天數(shù)可以加減，那么月份是否也可以這樣操作了。其實(shí)就用到了這個(gè)函數(shù)：add_months，不管是向前或向后查，只不過是參數(shù)是正還是負(fù)而已。

例如：查詢一下三個(gè)月后、兩個(gè)月前分別是哪一天：

select add_months(sysdate,3), add_months(sysdate,-2)   from  dual 
>> 2023-01-15 23:23:40  2022-08-15 23:23:40

那么對(duì)于年份呢，只要在上面參數(shù)基礎(chǔ)上乘以12就可以推算了。

4、greatest和least

這兩個(gè)函數(shù)在數(shù)值函數(shù)中分別代表取一組數(shù)值中的最大值和最小值，在日期中也有相關(guān)應(yīng)用。greatest是從一組日期數(shù)據(jù)中取距離當(dāng)前最近的日期，least是在一組日期中選擇最早的日期。

select greatest(to_date('2013-11-11','yyyy-mm-dd'),to_date('2013-12-11','yyyy-mm-dd'),to_date('2014-01-05','yyyy-mm-dd'),to_date('2013-11-11','yyyy-mm-dd')) from dual
>> 2014-01-05 00:00:00
select least(to_date('2013-11-11','yyyy-mm-dd'),to_date('2013-12-11','yyyy-mm-dd'),to_date('2014-01-05','yyyy-mm-dd'),to_date('2013-11-11','yyyy-mm-dd')) from dual
>> 2013-11-11 00:00:00

值得注意的是，這里必須用to_date對(duì)字符串進(jìn)行轉(zhuǎn)化，否則會(huì)把他們無(wú)法判斷是否為日期格式，只能當(dāng)成字符串去處理，容易出現(xiàn)結(jié)果錯(cuò)誤的情況。

5、next_day

此函數(shù)表示從指定日期開始算，下周幾是幾號(hào)？此函數(shù)的第二個(gè)參數(shù)范圍是[1,7],超出范圍會(huì)報(bào)錯(cuò)，注意的是：1代表周日，7代表周六。

select next_day(to_date('2022-10-15','yyyy-mm-dd'),2) from dual
>> 2022-10-17 00:00:00

如上sql表示，假設(shè)今天是10月15日，下個(gè)周二是10月17日。如果參數(shù)超出范圍會(huì)報(bào)錯(cuò)：

select next_day(to_date('2022-10-15','yyyy-mm-dd'),8) from dual  
> ora-01846: 周中的日無(wú)效

6、last_day

此函數(shù)用來計(jì)算某個(gè)月的最后一天是哪天，有了這個(gè)函數(shù)就不要每個(gè)月都判斷一下了。

select last_day(sysdate) from dual
>> 2022-10-31 23:36:12
select last_day(to_date('2022-11-15','yyyy-mm-dd')) from dual
>> 2022-11-30 00:00:00   

7、months_between

此函數(shù)用來計(jì)算兩個(gè)日期之間相差的月份數(shù)，返回結(jié)果可能帶小數(shù)。例如計(jì)算下自己的周歲，才發(fā)現(xiàn)還未成年呢~

  select floor(months_between(sysdate ,to_date('2005-08-24','yyyy-mm-dd'))/12) from dual
>> 17

8、組合日期函數(shù)

如上列舉的日期函數(shù)中，根據(jù)參數(shù)格式都可以組合使用。

舉例：小剛2022年10月28日入職新公司，兩個(gè)月試用期，新員工過了試用期后的每月第一天可以做轉(zhuǎn)正報(bào)告。那小剛什么時(shí)候可以做轉(zhuǎn)正報(bào)告？

select last_day(add_months(to_date('2022-10-28','yyyy-mm-dd'),2))+1 from dual
>> 2023-01-01 00:00:00

思路：先用add_months計(jì)算倆月后是哪一天，再用last_day計(jì)算那個(gè)月的最后一天，再+1代表下個(gè)月的第一天。

9、round 和 trunc

在數(shù)值函數(shù)中，round和trunc函數(shù)是對(duì)小數(shù)的處理，那么在日期函數(shù)中，是否有相同或類似的應(yīng)用呢？

我們用一個(gè)日期減去另一個(gè)日期的時(shí)候經(jīng)常會(huì)帶有小數(shù)，這是因?yàn)閛racle中保留了時(shí)、分、秒。有的時(shí)候我們不需要時(shí)、分、秒，所以就需要用到round和trunc這兩個(gè)函數(shù)了。

round函數(shù)作用是：如果某天的時(shí)間在中午之前，那么使用round函數(shù)會(huì)將該天的時(shí)間設(shè)置為0點(diǎn)（就是午夜），如果為中午之后就設(shè)置為次日零點(diǎn)，這也就是時(shí)間方面的四舍五入了。

trunc的函數(shù)作用：舍去，無(wú)論某天處于什么時(shí)間，使用trunc函數(shù)后都會(huì)將改天時(shí)間置為當(dāng)日0點(diǎn)。

select sysdate from dual
>> 2022-10-15 23:46:48
select to_date('2022-10-17','yyyy-mm-dd')-round(sysdate), to_date('2022-10-17','yyyy-mm-dd')-trunc(sysdate) from dual  
>> 1  2

如上，今天是10月15日 23點(diǎn)，因此round(sysdate)四舍五入為次日零點(diǎn)，即10月16日零點(diǎn)，因此距離17日相差1天。而trunc(sysdate)則是15日，因此相差2天。

10、使用to_date 和 to_char設(shè)置日期時(shí)間格式

基本使用

函數(shù)to_date 和 to_char作用是相反的，但是用法很類似。

to_date是將字符串轉(zhuǎn)換為時(shí)間格式，而to_char是將日期轉(zhuǎn)換為字符串格式。這兩個(gè)函數(shù)我們平時(shí)用的實(shí)在是太多了，舉例說明一下。

select to_date('2022/11/11 12:01:14','yyyy-mm-dd hh:mi:ss') from dual
>> 2022-11-11 12:01:14
select to_char(sysdate, 'yyyymmdd hh:mi:ss') from dual
>> 20221015 11:54:17

在函數(shù)to_char使用的時(shí)候可以在format參數(shù)中插入字符串，以雙引號(hào)的方式插入，至于雙引號(hào)在sql中的用法，在我的另一篇文章也有介紹。

select to_char(sysdate , 'yyyy"年"mm"月"dd"日"')  from  dual;
>> 2022年10月15日

最常見的to_char錯(cuò)誤

在轉(zhuǎn)換的時(shí)候，月份和分鐘經(jīng)常混淆。這里要注意下，mm代表的是月份，mi代表是分鐘，由于oracle中支持的時(shí)間很靈活，所以這塊弄混了不會(huì)提示的。

select to_char(sysdate ,'yyyy-mi-dd  hh:mm:ss') from dual
>> 2022-57-15  11:10:36

如上，即使mi與mm互換了位置也不會(huì)報(bào)錯(cuò)的。如果分鐘的數(shù)值是1-12內(nèi)，那這個(gè)錯(cuò)就很難發(fā)現(xiàn)了。

to_date整形參數(shù)

函數(shù)to_date的參數(shù)不僅可以接受字符串的參數(shù)，還可以接受數(shù)值類型的參數(shù)，這樣使oracle處理時(shí)間更加的靈活。

select  to_date(20221222,'yyyy-mm-dd') from dual 
>> 2022-12-22 00:00:00

11、new_time 切換時(shí)區(qū)

先看下函數(shù)參數(shù)用法：new_time(date,‘this’,‘other’)，函數(shù)共有3個(gè)參數(shù)：date代表的是this時(shí)區(qū)的時(shí)間，‘this’代表的是用三個(gè)字母組成的縮寫代替，代表當(dāng)前時(shí)區(qū)，‘other’也是用三個(gè)字母組成的縮寫代替，表示其他時(shí)區(qū)。聽起來比較模糊，舉例理解一下。

查詢一下‘2022-10-01 12:00:00’這個(gè)時(shí)間在北京和夏威夷的時(shí)間分別是多少？

select  to_date('2022-10-01 12:00:00','yyyy-mm-dd hh24:mi:ss'),new_time(to_date('2022-10-01 12:00:00','yyyy-mm-dd hh24:mi:ss'),'est','hst') from dual
>> 2022-10-01 12:00:00  2022-10-01 07:00:00

其中，est和hst都是全球時(shí)區(qū)的縮寫，可以另行科普一下。

12、extract

函數(shù)extract可用來代替to_char函數(shù)來選擇日期值中的某一部分（如從一個(gè)日期中選擇月份和天）。

select extract(year from sysdate) from dual;  
>> 2022 
select extract(month from sysdate) from dual;   
>> 10
select extract(day from sysdate) from dual;   
>> 16

注意：extract抽取年月日部分和抽取時(shí)分是不同的，抽取時(shí)分的時(shí)候需要這樣寫：

select extract(hour from timestamp  '2022-12-22 22:34:12') from dual
>> 22
select extract(minute from timestamp  '2022-12-22 22:34:12') from dual
>> 34

其實(shí)，這個(gè)函數(shù)的功能幾乎可以用to_char取代，因?yàn)槠涔δ芡耆梢蕴娲?，如下?/p>

select to_char(sysdate ,'yyyy') from dual
>> 2022
select to_char(sysdate ,'hh') from dual
>> 12

綜上，oracle當(dāng)中的時(shí)間函數(shù)使用非常靈活，可以變換各種格式進(jìn)行查詢處理。

總結(jié)

關(guān)于12類oracle日期函數(shù)超全面總結(jié)的文章就介紹至此，更多相關(guān)oracle日期函數(shù)內(nèi)容請(qǐng)搜索碩編程以前的文章，希望以后支持碩編程！