Oracle 时间类型详解

1. 时区

先说下时区，oracle时区分两种：数据库时区和会话时区。

查看数据库的时区：select dbtimezone from dual;
设置数据库时区：创建时指定：create database db1... set time_zone='+6:00'; 或后期修改：alter database set time_zone='+6:00';
查看当前会话的时区：select sessiontimezone from dual;
设置当前会话时区：alter session set time_zone='+06:00';

2. 时间类型

再说时间类型：这里介绍oracle的date、timestamp和timestamp_tz。

date：保存了日期和时间，没有时区
timestamp：保存了日期和时间，可以精确到秒后0~9位小数位（默认是6），不存储时区信息
timestamp with time zone：保存了日期和时间，可以精确到秒后0~9位小数位（默认是6），存储了时区信息，此类型的数据在保存到数据库时带有当前客户端的session timezone，无论
在什么时区查看这些数据，数据都不会随时区而变化。
timestamp with local time zone：保存了日期和时间，可以精确到秒后0~9位小数位（默认是6），不存储时区信息，而是将客户端输入的时间基于sessiontimezone转换后存入数据库，
当用户查询此类数据时，oracle会将数据再转化为用户session的时区时间返回给用户。
所以timestamp with time zone不受客户端时区影响，返回当时写入库的时间，而timestamp with local time zone受客户端时区影响。
比如：
select sessiontimezone from dual;
显示：+8:00
create table test_timezone(t1 timestamp with time zone, t2 timestamp with local time zone);
插入一条数据:
insert into test_timezone values(timestamp '2022-12-11 14:21:11', timestamp '2022-12-11 14:21:11');
查看：select * from test_timezone;
（注意dbeaver在查询时没显示时区，用sqlplus或plsql等可以）
显示：
11-DEC-22 02.21.11.000000 PM +08:00
11-DEC-22 02.21.11.000000 PM
alter session set time_zone='+06:00';
再查看：select * from test_timezone;
显示：
11-DEC-22 02.21.11.000000 PM +08:00
11-DEC-22 12.21.11.000000 PM

3. 插入date注意事项

Oracle date默认的时间格式是"DD-MON-YY"，所以我们想要将字符串类型，比如"2022-01-01 23:22:10" 直接插入到date类型字段就会报错：
ORA-01861: literal does not match format string，
这时我们得用to_date('2022-01-01 23:22:10', 'yyyy-mm-dd hh24:mi:ss')来插入即可，或者假如不想用to_date函数，还是想直接插入字符串，则可以修改日期时间格式，
alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
这个改变只对当前的会话(session)有用。然后就可以直接插入上面格式的字符串时间了。

4. 常用的时间相关函数

先说下时间类型的format定义如下：
date类型："yyyy-mm-dd hh24:mi:ss" 表示了年月日时分秒
timestamp类型："yyyy-mm-dd hh24:mi:ss.ff[1...9]" 表示了年月日时分秒秒后9位数 比如yyyy-mm-dd hh24:mi:ss.ff3
timestamp_tz类型："yyyy-mm-dd hh24:mi:ss.ff[1...9]TZH:TZM" 表示了年月日时分秒秒后9位数+时区小时+时区分钟
SYSDATE：得到系统的当前日期，不受时区影响
CURRENT_DATE：得到系统的当前日期，受时区影响
CURRENT_TIMESTAMP：以timestamp with time zone数据类型返回当前会话时区中的当前日期
to_char(date/timestamp,fmt)：返回给定时间的指定格式的字符串
to_date(str,fmt)：将字符串转化为date类型
to_timestamp(str,fmt)：将字符串转化为timestamp类型
to_timestamp_tz(str,fmt)：将字符串转化为timestamp with time zone类型
from_tz(timestamp,time_zone)：将timestamp值转化为timestamp with time zone类型
last_day(date)：返回给定日期当前月的最后一天
next_day(date,char)：返回比date日期晚的第一个工作日的日期，该日期由char命名。无论DATE的数据类型是什么，返回类型始终是DATE。参数字符必须是会话日期语言中的星期几，可以是全名，也可以是缩写。
? 比如：SELECT NEXT_DAY('15-OCT-2009','TUESDAY') FROM DUAL;
? 返回的就是 20-OCT-2009 00:00:00

所有内置函数的官网说明：

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Functions.html#GUID-D079EFD3-C683-441F-977E-2C9503089982