oracle中常用的时间日期相关操作

1、常用日期格式化

select to_date('2019-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') as datetime from dual;
-----------输出结果-----------
2019/1/1 13:14:20
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') as datetime from dual;
-----------输出结果-----------
2019-04-13 10:22:13

2、获取一整年的日期以及对应的星期

select to_char(TO_DATE('2019-12-31','yyyy-MM-dd') - level, 'd') as week,
decode(to_char(TO_DATE('2019-12-31','yyyy-MM-dd') - level, 'd'),1,'星期一',2,'星期二',3,'星期三',4,'星期四',5,'星期五',6,'星期六',7,'星期日') as  china_week,
TO_CHAR((TO_DATE('2019-12-31', 'yyyy-MM-dd') - (ROWNUM - 1)), 'yyyy-MM-dd') as datetime
from dual a 
connect by level <= trunc(TO_DATE('2019-12-31','yyyy-MM-dd') - TO_DATE('2019-01-01','yyyy-MM-dd')+1)
order by datetime

-----------输出结果-----------
WEEK    CHINA_WEEK    DATETIME
  2       星期二     2019-01-01
  3       星期三     2019-01-02
  4       星期四     2019-01-03
  5       星期五     2019-01-04
  6       星期六     2019-01-05
  7       星期日     2019-01-06
  1       星期一     2019-01-07
-----------中间省略-------------    
  1       星期一     2019-12-30
  2       星期二     2019-12-31

3、从2个时间段随机生成一个日期

create or replace function F_Random_Date(V_start_date IN date,V_end_date IN date) return date
is
V_reslut date;
V_random_range number(10,0);
begin
  select (V_end_date - V_start_date) * 24 * 60 * 60 into V_random_range from dual;
  select V_start_date + trunc(dbms_random.value(0,V_random_range))/(24 * 60 * 60) into V_reslut from dual;
  return V_reslut;
end;
/*示例*/
select F_Random_Date(to_date('2019-04-16 08:00','yyyy-MM-dd HH24:mi:ss'),to_date('2019-04-16 08:30','yyyy-MM-dd HH24:mi:ss')) as date_time from dual；
------输出--------
2019/4/16 8:12:39