Oracle计算距离当前时间几天、几年、几个月的方法

╰半橙微兮° 2022-05-21 12:46 1298阅读 0赞

**（作者：陈玓玏）**

在建模造变量的过程中，经常需要进行时间判断，比如取近三个月的某基础数据的统计特征，此时就需要用Oracle来进行取数，但是SQL的时间格式个人认为处理起来是非常麻烦的，下面就看一下如何取吧。

本文主要涉及两个功能：1、获得距离当前时间三天、三个月、三年的时间，方便进行时间大小的比较；2、获取时间距离当前时间有多少天、多少个月、多少年。

## 一、获得距离当前时间三个月、三年的时间 ##

--sysdate是系统时间关键字，可以取出系统时间
    --add_months是Oracle中专用于计算几个月前几个月后这样时间的函数，参数-3表示计算三个月前的那一天
    select to_date(to_char(add_months(sysdate, -3), 'yyyy-mm-dd'),'yyyy-mm-dd') from table_name ---36表示计算三年前那一天的时间 select to_date(to_char(add_months(sysdate, -36), 'yyyy-mm-dd'),'yyyy-mm-dd') from table_name

## 二、计算时间距离当前时间几个月、几年、几天 ##

--方法一：转换成数字后再进行计算，可能是转换成了时间戳？
    --转换成数字后，除以30表示计算的是相差多少个月
    select floor(to_number(sysdate-to_date(to_char(time_col_name,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'))/30)  from table_name
    
    --转换成数字后，除以30表示计算的是相差多少年
    select floor(to_number(sysdate-to_date(to_char(time_col_name,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'))/365)  from table_name
    
    --方法一还是会有些计算偏差，因为毕竟不是每个月都是30天，不是每年都是365天
    --方法二：直接使用Oracle中的MONTHS_BETWEEN函数
    months_between(sysdate,to_date(time_col_name,'yyyy-mm-dd'))

## 三、时间转换中需要注意的问题 ##

时间转换是目前发现的觉得SQL最复杂的数据类型，而且在使用各类时间函数、运算符的过程中容易出现很多问题，主要有这么两个：

**1、时间格式显示一样，但实际的类型不一致**：  
比如有的时间格式，看起来是正常的时间格式，但可能是经过各类函数运算产生的列，所以失去了原有的Date格式，变为了字符串，此时如果用to\_char这样的函数去处理，就会报错。对于这个问题，我一般是两个处理方案，不过并不是冲突的方案。一是不管时间格式怎么样，始终用to\_date函数先行转换，避免后续出现问题。二是可以用decode函数，先对时间变量的数据类型做一个判断，然后再根据类型的判断结果确定到底用什么样的函数来处理时间变量。如下面的代码所示：

--下面的代码就是先通过判断user_name用户下的table_name表中的time_col_name列的类型
    --是否为字符串型，决定该用什么样的函数来处理这个时间字段
    --decode中间获取字段类型的代码，没有打引号的变量都是固定写法，不是根据自己的情况更改的，
    --后面会写一篇文章专门整理这个
    select floor(to_number(sysdate-to_date(to_char( decode(to_char((select data_type from all_tab_column WHERE TABLE_name=upper('table_name') and owner='user_name' and column_name='time_col_name' and rownum=1)),'VARCHAR2',to_date(time_col_name,'yyyy-mm-dd hh24:mi:ss'),time_col_name) ,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'))/30) from table_name

**2、转换的时间格式与时间本身不一致**：  
比如有的格式是年月日时分秒都有的，此时你直接转换为’yyyy-mm-dd’，就会报错，这个时候最好就是统一转换成’yyyy-mm-dd hh24:mi:ss’格式，至少都还是能顺利转换的。