12類oracle日期函數超全面總結

前言

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

1、sysdate、current_date及systimestamp

sysdate

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

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

current_date

此字段返回的是當前會話的時區的系統日期，我們一般使用的是東八區的時間，如果將當前會話時間修改成東九區試試：

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

通過例子就很明顯看出兩者區別了，一個依賴于時區，一個不依賴。

systimestamp

函數systimestamp，它返回的是本機的系統時間，精確到微秒，并包含了當前時區，它跟會話的時區無關。

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

2、兩個日期的差

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

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

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

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

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

暈，還有3個月呢~

3、添加月份、減少月份

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

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

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

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

4、greatest和least

這兩個函數在數值函數中分別代表取一組數值中的最大值和最小值，在日期中也有相關應用。greatest是從一組日期數據中取距離當前最近的日期，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對字符串進行轉化，否則會把他們無法判斷是否為日期格式，只能當成字符串去處理，容易出現結果錯誤的情況。

5、next_day

此函數表示從指定日期開始算，下周幾是幾號？此函數的第二個參數范圍是[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表示，假設今天是10月15日，下個周二是10月17日。如果參數超出范圍會報錯：

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

6、last_day

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

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

此函數用來計算兩個日期之間相差的月份數，返回結果可能帶小數。例如計算下自己的周歲，才發現還未成年呢~

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

8、組合日期函數

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

舉例：小剛2022年10月28日入職新公司，兩個月試用期，新員工過了試用期后的每月第一天可以做轉正報告。那小剛什么時候可以做轉正報告？

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計算倆月后是哪一天，再用last_day計算那個月的最后一天，再+1代表下個月的第一天。

9、round 和 trunc

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

我們用一個日期減去另一個日期的時候經常會帶有小數，這是因為oracle中保留了時、分、秒。有的時候我們不需要時、分、秒，所以就需要用到round和trunc這兩個函數了。

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

trunc的函數作用：舍去，無論某天處于什么時間，使用trunc函數后都會將改天時間置為當日0點。

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點，因此round(sysdate)四舍五入為次日零點，即10月16日零點，因此距離17日相差1天。而trunc(sysdate)則是15日，因此相差2天。

10、使用to_date 和 to_char設置日期時間格式

基本使用

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

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

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

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

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

最常見的to_char錯誤

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

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

如上，即使mi與mm互換了位置也不會報錯的。如果分鐘的數值是1-12內，那這個錯就很難發現了。

to_date整形參數

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

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

11、new_time 切換時區

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

查詢一下‘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都是全球時區的縮寫，可以另行科普一下。

12、extract

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

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

其實，這個函數的功能幾乎可以用to_char取代，因為其功能完全可以替代，如下：

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

綜上，oracle當中的時間函數使用非常靈活，可以變換各種格式進行查詢處理。

總結

關于12類oracle日期函數超全面總結的文章就介紹至此，更多相關oracle日期函數內容請搜索碩編程以前的文章，希望以后支持碩編程！