Oracle 日期时间函数详解

1 日期函数

1. 常用日期函数
   (1) add_months(date, n)	        : 'n' 个月前，'-n' 个月后
   (2) months_between(date1, date2) : 月份差 'date1 - date2'

2. 时区（了解即可）
   (1) select dbtimezone from dual;
   (2) select sessiontimezone from dual;
   (3) select TZ_OFFSET('PST') from dual;
   (4) select * from v$timezone_names; -- 时区名
   (5) select new_time(current_date,'PST','EST') from dual; -- 转换

示例：

select sysdate 当前时间,
       
       trunc(sysdate) 当前日期, -- 返回 date 类型
       trunc(sysdate, 'Q') 当前季度第一天,
       trunc(sysdate, 'W') 当前周第一天, -- 周日 
       
       add_months(sysdate, 2) 两个月后,
       add_months(sysdate, -1) 两个月前,
       
       sysdate + 3 三天后,
       sysdate + 10 / (24 * 60 * 10) 十秒后,
       
       months_between(date '2020-10-01', date '2020-01-01') 日期月份差,
       
       next_day(sysdate, 2) 下周第二天, -- 下周一
       last_day(sysdate) 本月最后一天,
       
       round(sysdate) 舍入到最接近的日期, -- 精确到天
       round(sysdate, 'YEAR') 舍入到最接近的年,
       
       extract(year from sysdate) 当前年, -- 返回 number 类型， 同理，月：month，日：day
       extract(hour from timestamp '2020-11-17 11:22:33') 当前小时, -- 同理，分：minute，秒：second
       
       least(date '2020-01-01', date '2020-10-01') 返回日期列表最早的日期,
       greatest(date '2020-01-01', date '2020-10-01') 返回日期列表最晚的日期
  from dual;

2 日期格式

1. 常用日期格式 -- 不区分大小写
   select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') 当前时间 from dual;
   如: 2020-11-17 20:30:40
   
   (1) YYYY-MM-DD	'年'-'月'-'日' -- 四位数-两位数-两位数
   (2) HH24:MI:SS	'时':'分':'秒' -- HH12、HH : 均为 12 小时制
   
2. 特别说明
   (1) 一周的第一天是 "周日"

3. 参数设置
   (1) select * from nls_session_parameters; -- nls_date_language： simplified chinese
   (2) alter session set nls_date_language = 'AMERICAN'; -- 设置英文语言格式

示例：

select sysdate 当前时间,
       
       to_char(sysdate, 'YYYY') 四位年, -- 时、分、秒 同理
       to_char(sysdate, 'YY') 两位年, -- 同理还有1位和3位，用的较少（容易混淆）
       
       to_char(sysdate, 'MM') 月份,
       to_char(sysdate, 'MONTH') 月份带月份名, -- 若是英文语言格式，显示 november
       to_char(sysdate, 'MON') 月份带月份名简称, -- 若是英文语言格式，显示 nov （11月）
       to_char(sysdate, 'RM') 月份罗马数字,
       
       to_char(sysdate, 'DDD') 当年第n天,
       to_char(sysdate, 'DD') 当月第n天,
       to_char(sysdate, 'D') 当周第n天, -- 周日是第一天。day 的简称
       to_char(sysdate, 'DY') 星期几,
       
       to_char(sysdate, 'Q') 当年第n季度,
       
       to_char(sysdate, 'WW') 当年第n周,
       to_char(sysdate, 'W') 本月第n周,
       
       to_char(sysdate, 'HH24') "24小时制",
       to_char(sysdate, 'HH12') "12小时制" -- 默认 hh = hh12
  from dual;

3 类型转换

基础数据准备：

create table date_conversion_test (
   date_char      varchar2(20),
   date_date      date,
   date_timestamp timestamp
);

insert into date_conversion_test
  (date_char, date_date, date_timestamp)
values
  (to_char(sysdate, 'YYYY-MM-DD'), sysdate, sysdate);

示例：

select to_char(t.date_date, 'YYYY-MM-DD') "date -> char",
       to_char(t.date_timestamp, 'YYYY-MM-DD') "timestamp -> char",
       to_date(t.date_char, 'YYYY-MM-DD') "char -> date",
       to_timestamp(t.date_char, 'YYYY-MM-DD') "char -> date"
  from date_conversion_test t;