Oracle SQL 学习笔记16 - 日期和时间

TIME_ZONE

这是一个会话参数，可以设置为

• 绝对偏移量

ALTER SESSION SET TIME_ZONE = '-05:00';

• 数据库时区

ALTER SESSION SET TIME_ZONE = dbtimezone;

• 操作系统本地时区

ALTER SESSION SET TIME_ZONE = local;

• 时区名称

ALTER SESSION SET TIME_ZONE = 'American/New_York';

CURRENT_DATE，CURRENT_TIMESTAMP 和 LOCALTIMESTAMP

CURRENT_DATE

返回会话时区当前日期和时间，数据类型是DATE。

修改

ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

ALTER SESSION SET TIME_ZONE = '-5:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

CURRENT_TIMESTAMP

返回会话时区下当前日期和时间戳，数据类型是TIMESTAMP WITH TIME ZONE。

修改

ALTER SESSION SET TIME_ZONE = '-5:0';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP
FROM DUAL;

LOCALTIMESTAMP

返回用户会话的当前时间戳，数据类型是TIMESTAMP。

DBTIMEZONE 和 SESSIONTIMEZONE

SELECT DBTIMEZONE FROM DUAL;

SELECT SESSIONTIMEZONE FROM DUAL;

TIMESTAMP 类型

TIMESTAMP 类型是DATE 类型的扩展，包括年、月、日、小时、分、秒、小数位秒。
TIMESTAMP 类型还有变种：

1. TIMESTAMP
   [(fractional_seconds_precision)]
2. TIMESTAMP
   [(fractional_seconds_precision)]
   WITH TIME ZONE
   包括时区偏移量。所谓时区偏移量是指本地时区和UTC的差值。
3. TIMESTAMP
   [(fractional_seconds_precision)]
   WITH LOCAL TIME ZONE
   按照数据库时区转化并存储，不包括时区列。返回值按照用户本地时区自动转化。
   Data Type|Fields
   ---|---
   TIMESTAMP|年、月、日、小时、分、秒、小数位秒
   TIMESTAMP WITH TIME ZONE | 和TIMESTAMP类型一样，还包括TIMEZONE_HOUR，TIMEZONE_MINUTE 和 TIMEZONE_REGION
   TIMESTAMP WITH LOCAL TIME ZONE| 和TIMESTAMP类型一样，还包括时区的偏移量

INTERVAL 类型

INTERVAL 类型用于存储时间段，有两种时间差间隔表达方式：

Date Type	Fields
INTERVAL Year-MONTH	Year，Month
INTERVAL DAY-SECOND	Days，Hours，Minutes，Seconds with fractional seconds

INTERVAL YEAR TO MONTH

存储了用YEAR和MONTH表达的时间段：
INTERVAL YEAR [(year_precision)] TO MONTH
例子：

'312-2' assigned to INTERVAL YEAR(3) TO MONTH
# 间隔：312年2个月

INTERVAL DAY TO SECOND

存储了用Days，Hours，Minutes，Seconds with fractional seconds表达的时间段：
INTERVAL DAY [(day_precision)] TO SECOND
例子：

INTERVAL '6 03:30:16' DAY TO SECOND
# 间隔：6天3小时30分16秒

EXTRACT 函数

从SYSDATE中抽取年

SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;

从hire_date中抽取月

SELECT last_name, hire_date
  EXTRACT (MONTH FROM HIRE_DATE)
FROM employees
WHERE manager_id = 100;

TZ_OFFSE

获取指定时区的偏移量。

SELECT TZ_OFFSET('US/Eastern') FROM DUAL;

用FROM_TZ 转化TIMESTAMP

SELECT FROM_ZT(TIMESTAMP, '2000-03-28 08:00:00', '3:00')
FROM DUAL;

SELECT FROM_ZT(TIMESTAMP, '2000-03-28 08:00:00', 'Australia/North')
FROM DUAL;

TO_DSINTERVAL

时间间隔：天+时+分+秒

SELECT e.last_name,
TO_CHAR(e.hire_date, 'mm-dd-yy:hh:mi:ss') hire_date,
TO_CHAR(e.hire_date + TO_DSINTERVAL('100 10:00:00'),
'mm-dd-yy:hh:mi:ss') hiredate2
FROM HR.EMPLOYEES e;