Les16-时区.ppt

TO_TIMESTAMP 将字符串’2007-03-06 11:00:00’转换成TIMESTAMP类型: SELECT TO_TIMESTAMP (2007-03-06 11:00:00, YYYY-MM-DD HH:MI:SS) FROM DUAL; TO_TIMESTAMP_TZ TO_TIMESTAMP_TZ ：将字符串转换为TIMESTAMP WITH TIME ZONE数据类型 语法如下： TO_TIMESTAMP_TZ (char,[fmt],[nlsparam]) fmt：指定字符串的格式 ，如果忽略，字符串必须是TIMESTAMP WITH TIME ZONE的默认格式 nlsparam :指定语言，这个参数使用如下的格式: NLS_DATE_LANGUAGE = language 如果忽略，使用会话的默认语言 TO_TIMESTAMP_TZ示例 SELECT TO_TIMESTAMP_TZ(1999-12-01 11:00:00 -8:00, YYYY-MM-DD HH:MI:SS TZH:TZM)FROM DUAL; 将字符串‘1999-12-01 11:00:00 -8:00’ 转换为TIMESTAMP WITH TIME ZONE TO_YMINTERVAL TO_YMINTERVAL 将字符串转换为：INTERVAL YEAR TO MONTH 语法如下： TO_YMINTERVAL (char) 示例：显示1年零两个月后的日期 SELECT hire_date, hire_date + TO_YMINTERVAL(01-02) AS HIRE_DATE_YMININTERVAL FROM employeesWHERE department_id = 20; TO_DSINTERVAL TO_DSINTERVAL ：将字符串转换为 INTERVAL DAY TO SECOND 语法如下： TO_YDSINTERVAL(char) 示例： 显示所有员工从入职日期算起，100天零10个小时后的日期： SELECT last_name, TO_CHAR(hire_date, mm-dd-yy:hh:mi:ss) hire_date, TO_CHAR(hire_date + TO_DSINTERVAL(100 10:00:00), mm-dd-yy:hh:mi:ss) hiredate2 FROM employees; … 总结 在这节课中要学会以下函数的使用: CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP DBTIMEZONE SESSIONTIMEZONE EXTRACT TZ_OFFSET FROM_TZ TO_TIMESTAMP TO_YMINTERVAL TO_DSINTERVAL Oracle Database 11g: SQL Fundamentals II 5 - * Oracle Database 11g: SQL Fundamentals II 5 - * Oracle Database 11g: SQL Fundamentals II 5 - * Oracle Database 11g: SQL Fundamentals II 5 - * Oracle Database 11g: SQL Fundamentals II 5 - * Oracle Database 11g: SQL Fundamentals II 5 - * Comparing Date and Time in a Session’s Time Zone (continued) In this case, the CURRENT_DATE function returns the current date in the session’s time zone, the CURRENT_TIMESTAMP function returns the current date and time in the session’s time zone as a value of the data type TIMESTAMP WITH TIME ZONE, and the LOCALTIMESTAMP function returns the current date and time in the session’