SYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = dbtimezone; Session altered. SYS@cqoraxt0>select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 29-DEC-17 10.50.13.546588 PM +08:00 ->当前数据库所在时区的时间 SYS@cqoraxt0>select localtimestamp from dual; LOCALTIMESTAMP --------------------------------------------------------------------------- 29-DEC-17 02.50.38.464141 PM SYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = dbtimezone; Session altered. SYS@cqoraxt0>select localtimestamp from dual; LOCALTIMESTAMP --------------------------------------------------------------------------- 29-DEC-17 02.50.49.337510 PM SYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = '-06:00'; Session altered. SYS@cqoraxt0>select localtimestamp from dual; LOCALTIMESTAMP --------------------------------------------------------------------------- 29-DEC-17 08.51.00.307601 AM->绝对偏移量的时区 SYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = local; Session altered. SYS@cqoraxt0>select localtimestamp from dual; LOCALTIMESTAMP --------------------------------------------------------------------------- 29-DEC-17 10.51.11.989536 PM->系统本地时间时区 SYS@cqoraxt0>ALTER SESSION SET TIME_ZONE = 'America/New_York'; Session altered. SYS@cqoraxt0>select localtimestamp from dual; LOCALTIMESTAMP --------------------------------------------------------------------------- 29-DEC-17 09.51.21.599491 AM ->指定时间区域名称CURRENT_DATE,CURRENT_TIMESTAMP,LOCALTIMESTAMP -CURRENT_DATE 当前时间同sysdate表达一样 -CURRENT_TIMESTAMP 含时区 -LOCALTIMESTAMP 不含时区
我要评论col CURRENT_DATE format a30
col SESSIONTIMEZONE format a20
col CURRENT_TIMESTAMP format a40
col LOCALTIMESTAMP format a40
col sysdate format a30
set linesize 300
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
--调整时间输出的格式
select sessiontimezone,current_date,current_timestamp,localtimestamp,sysdate from dual;
SESSIONTIMEZONE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP SYSDATE
-------------------- ------------------------------ ---------------------------------------- ---------------------------------------- ------------------------------
+08:00 2017-12-29 23:13:08 29-DEC-17 11.13.08.826336 PM +08:00 29-DEC-17 11.13.08.826336 PM 2017-12-29 23:13:08
DBTIMEZONE,SESSIONTIMEZONE -DBTIMEZONE 数据库时区，也就是0时区 -SESSIONTIMEZONE 会话的当前时区
col SESSIONTIMEZONE format a20
col sysdate format a30
set linesize 300
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
--调整时间输出的格式
select dbtimezone,sessiontimezone,sysdate from dual;
DBTIME SESSIONTIMEZONE SYSDATE
------ -------------------- ------------------------------
+00:00 +08:00 2017-12-29 23:16:04
TIMESTAMP 时间类型 -TIMESTAMP [(fractional_seconds_precision)] 包含：年，月，天，小时，分钟，秒，时区小时，时区分钟 -TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE -TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE
TIMESTAMP时间类型各个值的范围
DATE,TIMESTAMP的不同点
-TIMESTAMP相比DATE多小数秒，时区小时和时区分钟。
create table time_t01(
id number,
t_date date default sysdate,
t_timestamp timestamp default current_timestamp
)
/
insert into time_t01 values(1,default,default);
/
col t_date format a30
col t_timestamp format a40
set linesize 300
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
--调整会话输出时时间格式
select * from time_t01;
ID T_DATE T_TIMESTAMP
---------- ------------------------------ ----------------------------------------
1 2017-12-29 23:51:55 29-DEC-17 11.51.55.085972 PM
--添加一个栏位
alter table time_t01 add (t_timestamp_zone timestamp with time zone); insert into time_t01 values(2,default,default,timestamp'2017-12-29 23:57:00 +8:00'); commit; insert into time_t01 values(3,default,default,timestamp'2017-12-29 23:57:00 America/New_York'); commit; insert into time_t01 values(4,default,default,timestamp'2017-12-29 23:57:00 -8:00'); commit; col t_timestamp_zone format a50 select * from time_t01; ID T_DATE T_TIMESTAMP T_TIMESTAMP_ZONE ---------- ------------------------------ ---------------------------------------- -------------------------------------------------- 1 2017-12-29 23:51:55 29-DEC-17 11.51.55.085972 PM 2 2017-12-29 23:57:39 29-DEC-17 11.57.39.696414 PM 29-DEC-17 11.57.00.000000 PM +08:00 3 2017-12-30 00:02:51 30-DEC-17 12.02.51.376596 AM 29-DEC-17 11.57.00.000000 PM AMERICA/NEW_YORK 4 2017-12-30 00:02:51 30-DEC-17 12.02.51.385169 AM 29-DEC-17 11.57.00.000000 PM -08:00 --添加一个栏位 alter table time_t01 add (t_timestamp_local_zone timestamp with local time zone); insert into time_t01 values(5,default,default,current_timestamp,current_timestamp); col t_timestamp_local_zone format a50 select * from time_t01 order by id; ID T_DATE T_TIMESTAMP T_TIMESTAMP_ZONE T_TIMESTAMP_LOCAL_ZONE ---------- ------------------------------ ---------------------------------------- -------------------------------------------------- -------------------------------------------------- 1 2017-12-29 23:51:55 29-DEC-17 11.51.55.085972 PM 2 2017-12-29 23:57:39 29-DEC-17 11.57.39.696414 PM 29-DEC-17 11.57.00.000000 PM +08:00 3 2017-12-30 00:02:51 30-DEC-17 12.02.51.376596 AM 29-DEC-17 11.57.00.000000 PM AMERICA/NEW_YORK 4 2017-12-30 00:02:51 30-DEC-17 12.02.51.385169 AM 29-DEC-17 11.57.00.000000 PM -08:00 5 2017-12-30 00:07:05 30-DEC-17 12.07.05.688410 AM 30-DEC-17 12.07.05.688410 AM +08:00 30-DEC-17 12.07.05.688410 AM 改变会话当前时区，timestamp with local time zone 时间也会跟着变动 SYS@cqoraxt0>alter session set time_zone='-09:00'; Session altered. SYS@cqoraxt0> select * from time_t01 order by id; ID T_DATE T_TIMESTAMP T_TIMESTAMP_ZONE T_TIMESTAMP_LOCAL_ZONE ---------- ------------------------------ ---------------------------------------- -------------------------------------------------- -------------------------------------------------- 1 2017-12-29 23:51:55 29-DEC-17 11.51.55.085972 PM 2 2017-12-29 23:57:39 29-DEC-17 11.57.39.696414 PM 29-DEC-17 11.57.00.000000 PM +08:00 3 2017-12-30 00:02:51 30-DEC-17 12.02.51.376596 AM 29-DEC-17 11.57.00.000000 PM AMERICA/NEW_YORK 4 2017-12-30 00:02:51 30-DEC-17 12.02.51.385169 AM 29-DEC-17 11.57.00.000000 PM -08:00 5 2017-12-30 00:07:05 30-DEC-17 12.07.05.688410 AM 30-DEC-17 12.07.05.688410 AM +08:00 29-DEC-17 07.07.05.688410 AM
create table time_t02(
id number,
t_interval_y interval year to month,
t_interval_d interval day to second
)
/
insert into time_t02 values(1,'03-5' ,'05 12:00:00')
/
insert into time_t02 values(2,interval '04' year,interval '06 13:00:00' day to second )
/
insert into time_t02 values(3,interval '12' month,interval '07 14:00:00' day to second )
/
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
col t_interval_y format a30
col t_interval_d format a30
select * from time_t02;
ID T_INTERVAL_Y T_INTERVAL_D
---------- ------------------------------ ------------------------------
1 +03-05 +05 12:00:00.000000
2 +04-00 +06 13:00:00.000000
3 +01-00 +06 13:00:00.000000
EXTRACT 提取时间
-可提取时间类型
EXTRACT ([YEAR] [MONTH][DAY] [HOUR] [MINUTE][SECOND] [TIMEZONE_HOUR] [TIMEZONE_MINUTE] [TIMEZONE_REGION] [TIMEZONE_ABBR]
-年、月、天、小时、分钟、秒、时区小时、时区分钟、时区区域、时区缩写
set linezie 600
col zone_region format a20
col zone_abbr format a20
select
extract(year from t_timestamp_zone) year,
extract(month from t_timestamp_zone) month,
extract(day from t_timestamp_zone) day,
extract(hour from t_timestamp_zone) hour,
extract(minute from t_timestamp_zone) minute,
extract(second from t_timestamp_zone) second,
extract(timezone_hour from t_timestamp_zone) zone_hour,
extract(timezone_minute from t_timestamp_zone) zone_minute,
extract(timezone_region from t_timestamp_zone) zone_region,
extract(timezone_abbr from t_timestamp_zone) zone_abbr
from time_t01
where id=3
/
YEAR MONTH DAY HOUR MINUTE SECOND ZONE_HOUR ZONE_MINUTE
---------- ---------- ---------- ---------- ---------- ---------- ---------- -----------
ZONE_REGION ZONE_ABBR
-------------------- --------------------
2017 12 30 4 57 0 -5 0
America/New_York EST
时区转换函数
-TZ_OFFSET 时间区域转为为数值
TZ_OFFSET ( ['time_zone_name'] '[+ | -] hh:mm' ] [ SESSIONTIMEZONE] [DBTIMEZONE]
SYS@cqoraxt0>select tz_offset('America/New_York') from dual;
TZ_OFFS
-------
-05:00
V$TIMEZONE_NAMES所有时区区域名称视图
-FROM_TZ 将TIMESTAMP转换为TIMESTAMP WITH TIME ZONE
FROM_TZ(TIMESTAMP timestamp_value, time_zone_value)
SYS@cqoraxt0>select from_tz(timestamp '2017-12-30 23:50:00','+8:00') from dual; FROM_TZ(TIMESTAMP'2017-12-3023:50:00','+8:00') --------------------------------------------------------------------------- 30-DEC-17 11.50.00.000000000 PM +08:00-TO_TIMESTAMP 将字符串转为TIMESTAMP时间类型 TO_TIMESTAMP (char,[fmt],['nlsparam'])
SYS@cqoraxt0>select to_timestamp('2017-12-30 23:53:00','YYYY-MM-DD HH24:MI:SS') from dual;
TO_TIMESTAMP('2017-12-3023:53:00','YYYY-MM-DDHH24:MI:SS')
---------------------------------------------------------------------------
30-DEC-17 11.53.00.000000000 PM
-TO_TIMESTAMP_TZ 将字符串转为TIMESTAMP WITH TIME ZONE TO_TIMESTAMP_TZ (char,[fmt],['nlsparam'])
SYS@cqoraxt0>select to_timestamp_tz('2017-12-30 23:55:00 +8:00','YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual;
TO_TIMESTAMP_TZ('2017-12-3023:55:00+8:00','YYYY-MM-DDHH24:MI:SSTZH:TZM')
---------------------------------------------------------------------------
30-DEC-17 11.55.00.000000000 PM +08:00
-TO_YMINTERVAL 将字符串转为时间间隔类型INTERVAL YEAR TO MONTH
TO_YMINTERVAL(char)
SYS@cqoraxt0>select sysdate+to_yminterval('01-01') from dual;
SYSDATE+TO_YMINTERV
-------------------
2019-01-31 00:03:14
-TO_DSINTERVAL 将字符串转为时间间隔类型INTERVAL DAY TO SECOND
TO_DSINTERVAL(char) SYS@cqoraxt0>select sysdate+to_dsinterval('05 1:00:00') from dual;
SYSDATE+TO_DSINTERV
-------------------
2018-01-05 01:06:57
如对本文有疑问， 点击进行留言回复！！
荐 Oracle Data Guard（三）Oracle Data Guard补充技术
Windows10安装Oracle19c数据库详细记录(图文详解)
Oracle用decode函数或CASE-WHEN实现自定义排序
Oracle利用errorstack追踪tomcat报错ORA-00903 无效表名的问题
网友评论