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

前言

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

1、sysdate、current_date及systimestamp

sysdate

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

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

current_date

此字段返回的是當(dāng)前會話的時區(qū)的系統(tǒng)日期，我們一般使用的是東八區(qū)的時間，如果將當(dāng)前會話時間修改成東九區(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ū)別了，一個依賴于時區(qū)，一個不依賴。

systimestamp

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

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

2、兩個日期的差

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

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

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

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

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

暈，還有3個月呢~

3、添加月份、減少月份

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

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

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

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

4、greatest和least

這兩個函數(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對字符串進(jìn)行轉(zhuǎn)化，否則會把他們無法判斷是否為日期格式，只能當(dāng)成字符串去處理，容易出現(xiàn)結(jié)果錯誤的情況。

5、next_day

此函數(shù)表示從指定日期開始算，下周幾是幾號？此函數(shù)的第二個參數(shù)范圍是[1,7],超出范圍會報錯，注意的是：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日，下個周二是10月17日。如果參數(shù)超出范圍會報錯：

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

6、last_day

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

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ì)算兩個日期之間相差的月份數(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日入職新公司，兩個月試用期，新員工過了試用期后的每月第一天可以做轉(zhuǎn)正報告。那小剛什么時候可以做轉(zhuǎn)正報告？

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ì)算那個月的最后一天，再+1代表下個月的第一天。

9、round 和 trunc

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

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

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

trunc的函數(shù)作用：舍去，無論某天處于什么時間，使用trunc函數(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ù)to_date 和 to_char作用是相反的，但是用法很類似。

to_date是將字符串轉(zhuǎn)換為時間格式，而to_char是將日期轉(zhuǎn)換為字符串格式。這兩個函數(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使用的時候可以在format參數(shù)中插入字符串，以雙引號的方式插入，至于雙引號在sql中的用法，在我的另一篇文章也有介紹。

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

最常見的to_char錯誤

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

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

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

to_date整形參數(shù)

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

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

11、new_time 切換時區(qū)

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

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

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都是全球時區(qū)的縮寫，可以另行科普一下。

12、extract

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

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抽取年月日部分和抽取時分是不同的，抽取時分的時候需要這樣寫：

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í)，這個函數(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ù)使用非常靈活，可以變換各種格式進(jìn)行查詢處理。

總結(jié)

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