Oracle 时间类型详解

2023-12-18 16:11:50

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

文章来源:https://blog.csdn.net/chanyue123/article/details/135062296
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。