Oracle全球化 —— 时间类型、时区与时间函数

最新推荐文章于 2024-08-22 15:36:33 发布
最新推荐文章于 2024-08-22 15:36:33 发布
阅读量3.9k 收藏 6
点赞数 2
分类专栏： Oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Hehuyi_In/article/details/104883708
版权

最近遇到一个表中数据时区不对的问题，排查思路一般是看表字段类型、看时区以及看插入时间的方法。Oracle官方文档《Database Globalization Support Guide》里有很详细的介绍，归纳学习一下。

 

一、 时间类型

Oracle里的时间类型分两大类 —— Datetime 和 Interval Data Types，本文主要关注第一类Datetime。

Datetime又可以分为四类，其中与时区有关的是后两类：

  • DATE
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE
  • TIMESTAMP WITH LOCAL TIME ZONE

 

1. DATE

存储日期+时间，精确到秒，不存储时区和地区信息。输出格式和语言由 NLS_DATE_FORMAT和NLS_DATE_LANGUAGE 两个初始化参数决定。如果查询时不指定这两个参数也不进行类型转换，会按默认格式输出。

SQL> select sysdate from dual;

SYSDATE
-------------------
2014-02-12 01:12:18

--oracle 修改默认日期格式
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

 

2. TIMESTAMP

DATE类型的扩展，存储日期+时间，可精确到秒后0~9位小数点（默认是6），也不存储时区和地区信息。输出格式和语言由 NLS_TIMESTAMP_FORMAT和NLS_DATE_LANGUAGE 两个初始化参数决定。如果查询时不指定这两个参数也不进行类型转换，会按默认格式输出。

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
12-FEB-14 01.14.12.945256 AM

SQL> alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SSXFF';
Session altered.

SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
2014-02-12 01:28:31.652888

也可以改对应环境变量

 

3. TIMESTAMP WITH TIME ZONE

TIMESTAMP类型的扩展，存储日期+时间，可精确到秒后0~9位小数点（默认是6），存储时区（或时区和地区）信息。此类型的数据在保存到数据库时带有当前客户端的session timezone，无论在什么时区查看这些数据，数据都不会随时区而变化。

create table t1 (id number,time timestamp with time zone);  --创建t1表，其中time 列的数据类型是timestamp with time zone
Table created.

select sessiontimezone from dual; --当前客户端的session timezone 是 -8:00
SESSIONTIMEZONE
---------------------------------------------------------------------------
-08:00

insert into t1 values(1,timestamp '2014-02-12 02:00:00');  --向t1表中插入一条数据  
1 row created.

select * from t1;  --查看t1表，其中time列带时区显示，并且时区为数据被插入时的session timezone
        ID     TIME
----------   ---------------------------------------------------------------------------
         1     2014-02-12 02:00:00.000000 -08:00

alter session set time_zone='-6:00';  --修改当前客户端的session timezone为 -6:00
Session altered.

select * from t1;    --再次查看t1表，其中time列数据无变化
        ID     TIME
----------   ---------------------------------------------------------------------------
         1     2014-02-12 02:00:00.000000 -08:00

 

4. TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP类型的另一种扩展，存储日期+时间，可精确到秒后0~9位小数点（默认是6），不存储时区信息，而是将客户端输入的时间基于database timezone转换后存入数据库（这也就是database tmiezone设置的意义所在，作为TIMESTAMP WITH LOCAL TIME ZONE类型的计算标尺)。当用户查询此类型数据时，Oracle会把数据再转为用户session的时区时间返回给用户。

客户端A时区时间 -> 数据库database tmiezone设置的时区时间 -> 客户端B时区时间

create table t2(id number,time timestamp with local time zone);  -- 创建t2表，其中time列为TIMESTAMP WITH LOCAL TIME ZONE
Table created.

insert into t2 values(1,timestamp '2014-02-12 02:10:00 -8:00');  --在t2表插入数据指定时区为-8:00，实际在保存到数据库时转化为基于database timezone的时间保存    
1 row created. 

select sessiontimezone from dual;  --当前客户端的session timezone 为 -6:00
SESSIONTIMEZONE
---------------------------------------------------------------------------
-06:00

select * from t2;  --查看时oracle将数据转换成当前客户端session timezone的时间
        ID      TIME
----------   ---------------------------------------------------------------------------
         1       2014-02-12 04:10:00.000000

 

5. 时间类型的选择

  • DATE：需要的时间精度不高，不需要保存时区/地区信息
  • TIMESTAMP：需要的时间精度高，不需要保存时区/地区信息
  • TIMESTAMP WITH TIME ZONE：需要保存时区/地区信息。比如需要精确记录每一笔交易的时间和地点（时区），看它是在当地几点发生的
  • TIMESTAMP WITH LOCAL TIME ZONE：不关心操作发生的地点，只关心操作是在你当前所在时区几点发生的。比如有一部电视剧在日本时间十点开播，但其实我只关心在中国时间几点能一起追直播，对我来说最方便的就是一查数据库直接告诉我它在中国时间九点开播。

 

二、 时区

其实根据前面一节已经知道了，Oracle时区分两种 —— 数据库时区和会话时区

1. 数据库时区

作为TIMESTAMP WITH LOCAL TIME ZONE类型的计算标尺。

查询方法

SELECT dbtimezone FROM DUAL;

设置方法

  • 可以在CREATE DATABASE 时用 SET TIME_ZONE子句指定。
CREATE DATABASE db01
...
SET TIME_ZONE='Europe/London';
-- 或者
CREATE DATABASE db01
...
SET TIME_ZONE='-05:00';
  • 也可后期修改（重启DB生效）
ALTER DATABASE SET TIME_ZONE='Europe/London';
--或者
ALTER DATABASE SET TIME_ZONE='-05:00';

 

2. 会话时区

当前sql会话所在时区，默认是服务器操作系统所在时区。

查询方法

SELECT sessiontimezone FROM DUAL;

设置方法

  • 可以设置操作系统的 ORA_SDTZ 环境变量
setenv ORA_SDTZ 'OS_TZ'  #默认
setenv ORA_SDTZ 'DB_TZ'
setenv ORA_SDTZ 'Europe/London'
setenv ORA_SDTZ '-05:00'
  • 也可以用sql命令设置
ALTER SESSION SET TIME_ZONE=local; -- 相当于os
ALTER SESSION SET TIME_ZONE=dbtimezone;
ALTER SESSION SET TIME_ZONE='Asia/Hong_Kong';
ALTER SESSION SET TIME_ZONE='+10:00';

 

三、 时间相关函数

Datetime函数可操作 date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE) 及 interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) 类型的值。

 

1. Datetime Functions Designed for the DATE Data Type

FunctionDescription

ADD_MONTHS

Returns the date d plus n months

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20080818','YYYYMMDD'),2), 'YYYY-MM-DD') FROM DUAL;
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD') FROM DUAL

LAST_DAY

Returns the last day of the month that contains date

SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -2)) FROM DUAL;

MONTHS_BETWEEN

Returns the number of months between date1 and date2

返回date2与date1间相隔的月份数

SELECT MONTHS_BETWEEN(TO_DATE('2011-05-03','YYYY-MM-DD'), TO_DATE('2011-01-23','YYYY-MM-DD')) FROM DUAL;
SELECT MONTHS_BETWEEN('19-12月-1999','19-3月-1999') mon_between FROM DUAL;

NEW_TIME

Returns the date and time in zone2 time zone when the date and time in zone1 time zone are date

查询指定时区的日期时间在另一指定时区对应的日期时间（各时区时间转换查询）

SELECT TO_CHAR(SYSDATE,'YYYY.MM.DD HH24:MI:SS') BeiJing_Time,
TO_CHAR(NEW_TIME(SYSDATE,'PDT','GMT'),'YYYY.MM.DD HH24:MI:SS') LOS_ANGELS 
FROM DUAL;

NEXT_DAY

Returns the date of the first weekday named by char that is later than date

返回自输入日期（参数1）开始，参数2的指定星期几对应是几号。

参数2可以用全称如'monday'、可以用缩写如'wed'，也可以用数字（星期日 = 1  星期一 = 2  星期二 = 3  星期三 = 4  星期四 = 5  星期五 = 6  星期六 = 7 ）

select next_day(to_date('1999.11.24','yyyy.mm.dd'),'friday') from dual;
-- 返回 1999年11月26日

select next_day(to_date('1999.11.24','yyyy.mm.dd'),'wed') from dual;
-- 返回 1999年11月01日
-- 1999年11月24日是星期三，第二个参数是星期五，是两天后。第二个例子由于日期正好是星期三，只能用下一个星期三日期。

SELECT NEXT_DAY(SYSDATE, 2) FROM DUAL; -- 返回从当前时间开始，下个星期二对应是几号

ROUND(date)

Returns date rounded to the unit specified by the fmt format model

TRUNC(date)

Returns date with the time portion of the day truncated to the unit specified by the fmt format model

为指定日期按指定格式而截去后的日期值，语法格式为TRUNC(date[,fmt]) 

SELECT TRUNC(TO_DATE('24-Nov-1999 08:00 pm'),'dd-mon-yyyy hh:mi am') FROM DUAL;
-- '24-Nov-1999 12:00:00 am'

SELECT TRUNC(TO_DATE('24-Nov-1999 08:37 pm','dd-mon-yyyy hh:mi am'),'hh') FROM DUAL;
-- '24-Nov-1999 08:00:00 am'

SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL; --返回当年第一天.
SELECT TRUNC(SYSDATE,'MM') FROM DUAL; --返回当月第一天.
SELECT TRUNC(SYSDATE,'D') FROM DUAL; --返回当前星期的第一天.
SELECT TRUNC(SYSDATE,'DD') FROM DUAL; --返回当前年月日

 

2. Additional Datetime Functions

Datetime FunctionDescription

CURRENT_DATE

Returns the current date in the session time zone in a value in the Gregorian calendar, of the DATE data type

以date类型返回当前会话时区中的当前日期 

SELECT CURRENT_DATE FROM DUAL;

CURRENT_TIMESTAMP

Returns the current date and time in the session time zone as a TIMESTAMP WITH TIME ZONE value

以timestamp with time zone类型返回当前会话时区中的当前时间

SELECT CURRENT_TIMESTAMP FROM DUAL;

DBTIMEZONE

Returns the value of the database time zone. The value is a time zone offset or a time zone region name

返回数据库时区

SELECT DBTIMEZONE FROM DUAL;

EXTRACT (datetime)

Extracts and returns the value of a specified datetime field from a datetime or interval value expression

提取指定日期时间的指定部分，比如年、月、日、小时、分钟等

SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
EXTRACT(DAY FROM OrderDate) AS OrderDay
FROM Orders;
--返回
OrderYear OrderMonth OrderDay
2008	    12	        29

FROM_TZ

Converts a TIMESTAMP value at a time zone to a TIMESTAMP WITH TIME ZONE value

LOCALTIMESTAMP

Returns the current date and time in the session time zone in a value of the TIMESTAMP data type

以timestamp类型返回当前会话中的日期和时间 

SELECT LOCALTIMESTAMP FROM DUAL;

NUMTODSINTERVAL

Converts number n to an INTERVAL DAY TO SECOND literal

NUMTOYMINTERVAL

Converts number n to an INTERVAL YEAR TO MONTH literal

SESSIONTIMEZONE

Returns the value of the current session's time zone

返回当前会话时区，针对当前会话，可以在会话级改变

SELECT SESSIONTIMEZONE FROM DUAL;
ALTER SESSION SET TIME_ZONE = '8:00';
SELECT SESSIONTIMEZONE FROM DUAL;

SYS_EXTRACT_UTC

Extracts the UTC from a datetime with time zone offset

SYSDATE

Returns the date and time of the operating system on which the database resides, taking into account the time zone of the database server's operating system that was in effect when the database was started

SYSTIMESTAMP

Returns the system date, including fractional seconds and time zone of the system on which the database resides

TO_CHAR (datetime)

Converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE data type to a value of VARCHAR2 data type in the format specified by the fmt date format

TO_DSINTERVAL

Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of INTERVAL DAY TO SECOND data type

TO_NCHAR (datetime)

Converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL MONTH TO YEAR, or INTERVAL DAY TO SECOND data type from the database character set to the national character set

TO_TIMESTAMP

Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of TIMESTAMP data type

TO_TIMESTAMP_TZ

Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the TIMESTAMP WITH TIME ZONE data type

TO_YMINTERVAL

Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of the INTERVAL YEAR TO MONTH data type

TZ_OFFSET

Returns the time zone offset that corresponds to the entered value, based on the date that the statement is executed

 

3. Time Zone Conversion Functions

Time Zone FunctionDescription

ORA_DST_AFFECTED

Enables you to verify whether the data in a column is affected by upgrading the DST rules from one version to another version

ORA_DST_CONVERT

Enables you to upgrade your TSTZ column data from one version to another

ORA_DST_ERROR

Enables you to verify that there are no errors when upgrading a datetime value

 

参考

https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/datetime-data-types-and-time-zone-support.html#GUID-7A1BA319-767A-43CC-A579-4DAC7063B243

http://blog.itpub.net/29457434/viewspace-1080444/

https://www.cnblogs.com/kerrycode/archive/2011/04/27/2029906.html

Hehuyi_In
关注
专栏目录
Oracle日期时间(DateTime)操作
07-18
Oracle日期时间(DateTime)操作
datetime对应oracle什么类型,oracle数据类型之DATETIME
weixin_39766014的博客
04-11 1945
Datetime and Interval在007题库中有一道关于DATETIME数据类型的题目：Which three are DATETIME data types that can be used when specifying column definitions? (Choose three)A. TIMESTAMPB. INTERVAL MONTH TO DAYC. INTERV...
Oracle RAC 修改系统时区避坑指南（深挖篇）
最新发布
LuciferLiu_DBA
08-22 1266
昨天遇到一个问题，Oracle RAC 安装完之后，客户反馈数据库查询的时间不对，经分析原来是系统时区与客户所属时区不一致（看来是安装操作系统时选错了时区），需要修改系统时区。## 使用 TNS 连接数据库查询时间时区为：上海 +0800 $ sqlplus system/oracle@lucifer。
oracle datetime精度,ORACLE 日期时间数据类型与时间间隔数据类型讲解
weixin_35702498的博客
04-03 4872
23.1 概述数据类型说明DATE日期时间型，精确到秒TIMESTAMP(fractional_seconds_precision)时间精确到秒后面fractional_seconds_precision位，默认6位，最多9位TIMESTAMP(fractional_seconds_precision)WITH TIME ZONE带时区信息，时间精确到秒后面fractional_seconds_p...
Mysql/Oracle的DATE、DATETIME 和 TIMESTAMP 类型区别
yuangit的博客
11-27 2194
包含小数部分后，这些值的格式为'YYYY-MM-DD hh:mm:ss[.fraction]'，值的范围为 DATETIME'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.499999' ，值的范围为 TIMESTAMP'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.499999'。支持的范围是 '1000-01-01 00:00:00'到'9999-12-31 23:59:59'。
oracle数据类型之DATETIME
mosaic的专栏
07-28 1万+
Datetime and Interval         在007题库中有一道关于DATETIME数据类型的题目：         Which three are DATETIME data types that can be used when specifying column definitions? (Choose three)         A.  TIMESTAMP
oracle的datetime函数,日期和时间函数
weixin_35204634的博客
04-16 2693
本文主要介绍一些日期和时间函数的类型。时区转换运算符：AT TIME ZONE，用于设置一个时间戳的时区。SELECT timestamp ‘2012-10-31 01:00 UTC’; 2012-10-31 01:00:00.000 UTCSELECT timestamp ‘2012-10-31 01:00 UTC’ AT TIME ZONE ‘America/Los_Angeles’; 201...
Oracle：时间类型
热门推荐
不怕猫的耗子A
07-30 3万+
Oracle中的时间类型 1、Oracle中的时间类型只有date和TIMESTAMP，TIMESTAMP是比date更精确的类型 ⑴DATE类型：ORACLE最常用的日期类型，它可以保存日期和时间，常用日期处理都可以采用这种类型 ⑵TIMESTAMP类型：ORACLE常用的日期类型，它与date的区别是TIMESTAMP类型不仅可以保存日期和时间，还能保存小数秒，小数位数可以指定为0-9，默认为6位 2、日期时间函数用于处理时间类型的数据，Oracle中提供了很多和日期相关的函数。并且...
如何在Oracle 9i中正确转换时区
03-03
在Oracle 9i数据库系统中，处理时区转换是一个重要的任务，特别是在全球化的环境中，不同地区的数据交流需要准确地处理时间信息。Oracle 9i引入了一些新的特性来增强时区管理，以解决在早期版本中遇到的问题。在...
Oracle数据库10g支持全球化特性的平台Powe.pptx
10-07
Oracle数据库10g在全球化特性方面的支持主要体现在对多种语言、字符集和文化习惯的兼容性，以及为开发者提供的便捷工具。Oracle致力于提供一个全面的全球化解决方案，使得用户可以使用本地语言存储、处理和检索信息...
les16oracle9i日期函数.pptx
10-11
综上所述，Oracle 9i的日期函数提供了一整套工具来处理日期、时间以及时区相关的操作，确保了在全球范围内的数据一致性与准确性。这些功能对于构建全球化、分布式的企业级应用至关重要。通过熟练掌握这些函数，...
les16_oracle9i日期函数.pptx
10-11
通过这些增强的功能，Oracle9i能够更灵活地处理全球不同地区的日期和时间，满足了全球化应用的需求。 总结来说，Oracle9i的日期函数提供了强大的日期和时间处理能力，包括时区转换、当前日期和时间的获取、以及对...
Oracle数据库10g-支持全球化特性的平台-Powe.pptx
09-22
Oracle数据库10g在全球化支持方面展现了强大的能力，旨在满足多语言环境下的数据存储、处理和检索需求。全球化支持的核心目标是让用户能够以他们的本机语言进行交互，并遵循当地的习惯和标准。 首先，Oracle 10g...
在oracle中， date、datetime的区别
努力工作是为了更美好的生活，平凡的生活不应充斥着繁琐的工作！
01-26 2242
在oracle中， date、datetime的区别
Oracle 时区详解
Ruishine的博客
08-25 2864
由于地球经纬度及地球自转引起的经度方向，不同的经度的地方，所感受到的昼夜是不同 的。有关国际会议决定将地球表面按经线从东到西,每隔经度15度划分一个时区,并且规定 相邻区域的时间相差1小时。这就是时区的由来。而实际使用中，往往不是严格按照这个标准来做的。国家的存在,为了行政工作的方便,一 个国家都有一个本国的标准时，大部分国家以首都所在的时区作为国家的标准时。比如, 我大中国以北京所在时区(东八区)作为中国的标准时。在IT领域,很多时候我们都会涉及 到时区的问题。
oracle11g 日期型,Oracle11g: datetime
weixin_32866237的博客
04-09 369
--上一月，上一年select add_months(sysdate,-1) last_month,add_months(sysdate,-12) last_year from dual;--下一月，下一年select add_months(sysdate,1) last_month,add_months(sysdate,12) last_year from dual;--当月最后一天select...
Date和Datetime的区别
Demo1000001的博客
08-15 364
Datetimw包含日期和时间部分默认时间是1900-01-01 00:00:00。datetime格式：YYYY-MM-DD HH:MM:SS。Date只包含日期，不包含小数部分。date格式：YYYY-MM-DD。timestamp可以精确到纳秒。但是在oracle数据库中。date为年月日时分秒。
oracle 数据类型详解---日期型
MKing's Blog
05-13 1万+
oracle 数据类型详解---日期型      oracle数据类型看起来非常简单，但用起来会发现有许多知识点，本文是我对ORACLE日期数据类型的一些整理，都是开发入门资料，与大家分享：注：由于INTERVAL及TIME ZONE实际用得比较少，所以本文内容未涉及这两个方面。1、常用日期型数据类型1.1、DATE这是ORACLE最常用的日期类型，它可以保存日期和时间，常用日期处理都
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Hehuyi_In

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值