oracle:时间数据的处理

刷题过程中,受时间数据所困.为此,整理出以下关于时间操作的基本知识点,受益匪浅.真香,原来还可以这么搞⛏!!!

---

---

日期函数

系统时间：sysdate 无参数 (默认格式:DD(日)-Mon(月)-RR(2位年))

date的加减

select sysdate -1 from dual 
----结果减一天,也就24小时 
select sysdate-(1/2) from dual 
-----结果减去半天，也就12小时 
select sysdate-(1/24) from dual 
-----结果减去1 小时 
select sysdate-((1/24)/12) from dual  
----结果减去5分钟 

 select sysdate from dual;--结果：26-3月 -20

其它:ORACLE时间函数(SYSDATE)深入理解
时间戳:systimestamp

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
26-3月 -20 09.20.03.403000 上午 +08:00

时间戳与date型数据之间的关系

-- 字符型转成time_stamp

select to_timestamp('01-10月-08 07.46.41.000000000 上午','dd-MON-yy hh:mi:ss.ff AM')  
from dual;  
-- timestamp转date
 
select cast(to_timestamp('01-10月-08 07.46.41.000000000 上午','dd-MON-yy hh:mi:ss.ff AM') as date) timestamp_to_date
from dual; 

-- date转成timestamp

select cast(sysdate as timestamp) date_to_timestamp  
from dual;  
-- 获取系统当前时间戳

select systimestamp from dual;  

-- 时间戳的差值：

select systimestamp-systimestamp from dual;  
select sysdate-sysdate from dual;  

SQL> select systimestamp-systimestamp from dual;

SYSTIMESTAMP-SYSTIMESTAMP
---------------------------------------------------------------------------
+000000000 00:00:00.000000

SQL> select sysdate-sysdate from dual;

SYSDATE-SYSDATE
---------------
              0

日期操作

1.add_months(date,i)函数：在指定日期上添加月份，i位任意整数；

2.next_day(date,char):若char的星期*,则返回date指定日期的下周*是哪天；

3. last_day(date) 返回月的最后一天；

4. months_between(date1,date2):表示两个日期之间相隔的月份；

5. 间隔的天数：两个日期直接相减

6. extract(date from datetime)提取date

转换函数

1.日期转换为字符的函数：to_char(date[,fmt[,params]])
params:YY YYYY year MM Month DD Day HH24 HH12 MI SS
如: select to_char(sysdate,'YYYY-MM-DD HH12:Mi:SS') from dual;
2. 字符转换成日期函数:to_date(char[,fmt[,params]]):
如:select to_date('2020-3-12','yyyy-mm-dd') from dual;
3.数字转换成字符函数to_char(number[,fmt]):
其中:fmt格式设置:
|

fmt	描述
9	类似于占位符,用来显示数字,并忽略前面0
0	显示数字,位数不足用0补齐
.或D	显示小数点
,或G	显示千位符
$	美元元符号
S	加正负号(前后都可以)

案例:

4.字符转数字:to_number(char[,fmt])

结论:***to_char支持date和timestamp数据***

to_char信息提取总结

fmt	信息
‘Y{num}’	年的最后num位
‘Q’	当前日期所在的季度
‘MM’	当前日期所在的月份,如2020-03-12,提取的结果为03
‘Month’	当前日期所在的月份,如2020-03-12,提取的结果为3月
‘RM’	当前日期所在的月份的罗马表示
‘WW’	当前日期属于当年的第几周
‘DDD’	当前日期属于当年的第几天
‘DD’	当前日期属于当月的第几天
‘D’	当前日期属于一周的第几天 ,注意其与星期的区别,其是从星期日开始算
‘DY’	获取中文的星期
‘HH’	获取12小时制的小时
‘HH24’	获取24小时制的小时

trunc函数

格式:trunc(date[,fmt]),仅支持date数据,不支持timestamp.

fmt	描述
‘year’	当前年的1月1日,无时分秒
month	返回当月的1日
day	返回当前星期的星期天
Q	当前季度的1日
‘D’	当前星期的星期天

round 获取到最近的日期

基本格式:round(d,[fmt])

fmt	描述
year	舍入最近的年
month	最近的月
day	最近的星期日

案例:

select sysdate today,
  round(sysdate,'year') year,
  round(sysdate,'month') month,
  round(sysdate,'day') day,
  round(sysdate,'D') day2,
  round(sysdate,'Q') quarter,
  round(sysdate,'WW') week 
from dual;

结果:

应用

案例:按时间分组讨论
Oracle数据库按时间进行分组统计数据的方法

---

参考:
Oracle日期函数，Oracle数据库开发利器之函数教程-慕课网
oracle 日期时间函数使用总结_oracle_脚本之家
Oracle计算时间差常用函数_oracle_脚本之家
ORACLE时间函数(SYSDATE)深入理解_oracle_脚本之家
oracle日期时间型timestamp的深入理解_oracle_脚本之家