• 1. Oracle9i 日期函数
  • 2. 目标通过本章学习,您将可以使用下列日期函数: TZ_OFFSET CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP DBTIMEZONE SESSIONTIMEZONE EXTRACT FROM_TZ TO_TIMESTAMP TO_TIMESTAMP_TZ TO_YMINTERVAL
  • 3. 时区-08:00上图显示了全球24个时区以及当格林威治时间是 12:00时各时区的时差-05:00+02:00+10:00+07:00
  • 4. Oracle9i 日期支持Oracle9i中, 可以将时区加入到日期和时间中而且可以将秒进行进一步的精确 日期中加入了三种新的数据类型: TIMESTAMP(时间撮) TIMESTAMP WITH TIME ZONE (TSTZ) (带时区的时间撮) TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) (带有本地时区的时间撮) Oracle9i 支持夏令时
  • 5. TZ_OFFSETSELECT TZ_OFFSET('US/Eastern') FROM DUAL;SELECT TZ_OFFSET('Canada/Yukon') FROM DUAL; SELECT TZ_OFFSET('Europe/London') FROM DUAL; 显示时区 ‘US/Eastern’的时差 显示时区 'Canada/Yukon'的时差 显示时区 'Europe/London'的时差
  • 6. CURRENT_DATECURRENT_DATE 对会话所在的时区是敏感的ALTER SESSION SET TIME_ZONE = '-8:0'; SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;ALTER SESSION SET TIME_ZONE = '-5:0'; SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;按照当前会话的时区显示当前会话的时间ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
  • 7. CURRENT_TIMESTAMPALTER SESSION SET TIME_ZONE = '-8:0'; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;ALTER SESSION SET TIME_ZONE = '-5:0'; SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;按照当前会话的时区显示当前会话的时间CURRENT_TIMESTAMP 对会话所在的时区是敏感的 返回值是 TIMESTAMP WITH TIME ZONE 数据类型
  • 8. LOCALTIMESTAMPALTER SESSION SET TIME_ZONE = '-5:0'; SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;ALTER SESSION SET TIME_ZONE = '-8:0'; SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;按照当前会话的时区显示当前会话的时间LOCALTIMESTAMP对会话所在的时区是敏感的 返回值是 TIMESTAMP 数据类型
  • 9. DBTIMEZONE 和 SESSIONTIMEZONE SELECT DBTIMEZONE FROM DUAL;SELECT SESSIONTIMEZONE FROM DUAL;显示数据库所在的时区显示会话所在的时区
  • 10. EXTRACTSELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;SELECT last_name, hire_date, EXTRACT (MONTH FROM HIRE_DATE) FROM employees WHERE manager_id = 100;从 SYSDATE 中抽出年从HIRE_DATE 中抽出月
  • 11. FROM_TZ 应用举例SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00','3:00') FROM DUAL;SELECT FROM_TZ(TIMESTAMP '2000-03-28 08:00:00', 'Australia/North') FROM DUAL;
  • 12. SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;TO_TIMESTAMP 和 TO_TIMESTAMP_TZ 应用举例SELECT TO_TIMESTAMP ('2000-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
  • 13. TO_YMINTERVAL 应用举例SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') AS HIRE_DATE_YMININTERVAL FROM EMPLOYEES WHERE department_id = 20;
  • 14. 总结TZ_OFFSET FROM_TZ TO_TIMESTAMP TO_TIMESTAMP_TZ TO_YMINTERVALCURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP DBTIMEZONE SESSIONTIMEZONE EXTRACT通过本章学习,您已经可以使用:
  • 15. Hidden Slide
  • 16. Hidden Slide
  • 17. Hidden Slide