Oracle与时间戳有关的函数

lxl0121 发表于 2011-12-23 01:12

有很多函数可以用来查询和处理时间戳，表中列出了这些函数。<table border="0" width="208" style="height: 24px; "><tbody><tr></tr></tbody></table><table border="1" cellspacing="0" cellpadding="0" width="625" frame="all" style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(227, 95, 27); border-right-color: rgb(227, 95, 27); border-bottom-color: rgb(227, 95, 27); border-left-color: rgb(227, 95, 27); background-color: rgb(205, 222, 164); "><tbody><tr height="18" style="height: 13.5pt; "><td class="xl66" width="241" height="18" style="font-size: 1em; height: 13.5pt; width: 181pt; ">函    数</td><td class="xl67" width="384" style="font-size: 1em; border-left-width: medium; border-left-style: none; border-left-color: initial; width: 288pt; ">说    明</td></tr><tr height="36" style="height: 27pt; "><td class="xl68" width="241" height="36" style="font-size: 1em; height: 27pt; border-top-width: medium; border-top-style: none; border-top-color: initial; width: 181pt; ">CURRENT_TIMESTAMP()</td><td class="xl69" width="384" style="font-size: 1em; border-top-width: medium; border-top-style: none; border-top-color: initial; border-left-width: medium; border-left-style: none; border-left-color: initial; width: 288pt; ">返回一个 TIMESTAMP WITH TIME ZONE 类型的值，其中包括当前会话的日期和时间以及会话的时区</td></tr><tr height="270" style="height: 202.5pt; "><td class="xl68" width="241" height="270" style="font-size: 1em; height: 202.5pt; border-top-width: medium; border-top-style: none; border-top-color: initial; width: 181pt; ">EXTRACT( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | } TIMEZONE_ABBR } FROM x)</td><td class="xl69" width="384" style="font-size: 1em; border-top-width: medium; border-top-style: none; border-top-color: initial; border-left-width: medium; border-left-style: none; border-left-color: initial; width: 288pt; ">从x中提取并返回年、月、日、时、分、秒或时区，其中x可以是时间戳类型或DATE类型</td></tr><tr height="72" style="height: 54pt; "><td class="xl68" width="241" height="72" style="font-size: 1em; height: 54pt; border-top-width: medium; border-top-style: none; border-top-color: initial; width: 181pt; ">FROM_TZ( x, time_zone)</td><td class="xl69" width="384" style="font-size: 1em; border-top-width: medium; border-top-style: none; border-top-color: initial; border-left-width: medium; border-left-style: none; border-left-color: initial; width: 288pt; ">将TIMESTAMP类型的x转换为由time_zone指定的时区，并返回TIMESTAMP WITH TIMEZONE类型。time_zone必须被指定为+|- HH:MI格式的字符串。此函数一般将x和time_zone合并成一个值</td></tr><tr height="36" style="height: 27pt; "><td class="xl68" width="241" height="36" style="font-size: 1em; height: 27pt; border-top-width: medium; border-top-style: none; border-top-color: initial; width: 181pt; ">LOCALTIMESTAMP</td><td class="xl69" width="384" style="font-size: 1em; border-top-width: medium; border-top-style: none; border-top-color: initial; border-left-width: medium; border-left-style: none; border-left-color: initial; width: 288pt; ">返回一个TIMESTAMP 类型，其中包含会话的当前日期和时间</td></tr><tr height="36" style="height: 27pt; "><td class="xl68" width="241" height="36" style="font-size: 1em; height: 27pt; border-top-width: medium; border-top-style: none; border-top-color: initial; width: 181pt; ">SYSTIMESTAMP</td><td class="xl69" width="384" style="font-size: 1em; border-top-width: medium; border-top-style: none; border-top-color: initial; border-left-width: medium; border-left-style: none; border-left-color: initial; width: 288pt; ">返回一个TIMESTAMP WITH TIME ZONE类型，其中包括数据库的当前日期、时间，以及数据库时区</td></tr><tr height="32" style="height: 24pt; "><td class="xl68" width="241" height="32" style="font-size: 1em; height: 24pt; border-top-width: medium; border-top-style: none; border-top-color: initial; width: 181pt; ">SYS_EXTRACT_UTC(x)</td><td class="xl70" width="384" style="font-size: 1em; border-top-width: medium; border-top-style: none; border-top-color: initial; border-left-width: medium; border-left-style: none; border-left-color: initial; width: 288pt; ">将TIMESTAMP WITH TIMEZONE类型的x转换为一个TIMESTAMP类型，其中包含了UTC时区中的日期和时间</td></tr><tr height="36" style="height: 27pt; "><td class="xl68" width="241" height="36" style="font-size: 1em; height: 27pt; border-top-width: medium; border-top-style: none; border-top-color: initial; width: 181pt; ">TO_TIMESTAMP(x, )</td><td class="xl69" width="384" style="font-size: 1em; border-top-width: medium; border-top-style: none; border-top-color: initial; border-left-width: medium; border-left-style: none; border-left-color: initial; width: 288pt; ">将字符串x转换为一个TIMESTAMP类型，还可以为x指定一个可选的参数format</td></tr><tr height="37" style="height: 27.75pt; "><td class="xl71" width="241" height="37" style="font-size: 1em; height: 27.75pt; border-top-width: medium; border-top-style: none; border-top-color: initial; width: 181pt; ">TO_TIMESTAMP_TZ(x, )</td><td class="xl72" width="384" style="font-size: 1em; border-top-width: medium; border-top-style: none; border-top-color: initial; border-left-width: medium; border-left-style: none; border-left-color: initial; width: 288pt; ">将字符串x转换为一个TIMESTAMP WITH TIMEZONE类型，还可以为x指定一个可选的参数format</td></tr></tbody></table>  1. CURRENT_TIMESTAMP、LOCALTIMESTAMP和SYSTIMESTAMP下面这个查询同时调用了CURRENT_TIMESTAMP、LOCALTIMESTAMP和SYSTIMESTAMP函数(我的会话时区和数据库时区都是PST，它比UTC晚8小时)：<div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=SELECT%20CURRENT_TIMESTAMP%2C%20LOCALTIMESTAMP%2C%20SYSTIMESTAMP%0AFROM%20dual%3B%0ACURRENT_TIMESTAMP%0A-----------------------------------%0ALOCALTIMESTAMP%0A-----------------------------------%0ASYSTIMESTAMP%0A-----------------------------------%0A05-NOV-07%2012.15.32.734000%20PM%20PST%0A05-NOV-07%2012.15.32.734000%20PM%0A05-NOV-07%2012.15.32.734000%20PM%20-08%3A00" quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM dual;  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">CURRENT_TIMESTAMP  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">-----------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">LOCALTIMESTAMP  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">-----------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">SYSTIMESTAMP  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">-----------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">05-NOV-07 12.15.32.734000 PM PST  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">05-NOV-07 12.15.32.734000 PM  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">05-NOV-07 12.15.32.734000 PM -08:00  </li></ol></div> 如果将TIME_ZONE设置为EST，并重新执行上面这个查询，就会得到如下输出结果：<div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=ALTER%20SESSION%20SET%20TIME_ZONE%20%3D%20'EST'%3B%0A%0ASession%20altered.%0A%0ASELECT%20CURRENT_TIMESTAMP%2C%20LOCALTIMESTAMP%2C%20SYSTIMESTAMP%0AFROM%20dual%3B%0A%0ACURRENT_TIMESTAMP%0A-----------------------------------------------------------%0ALOCALTIMESTAMP%0A-----------------------------------------------------------%0ASYSTIMESTAMP%0A-----------------------------------------------------------%0A05-NOV-07%2003.19.57.562000%20PM%20EST%0A05-NOV-07%2003.19.57.562000%20PM%0A05-NOV-07%2012.19.57.562000%20PM%20-08%3A00" quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">ALTER SESSION SET TIME_ZONE = 'EST';  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">Session altered.  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM dual;  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">CURRENT_TIMESTAMP  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">-----------------------------------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">LOCALTIMESTAMP  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">-----------------------------------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">SYSTIMESTAMP  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">-----------------------------------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">05-NOV-07 03.19.57.562000 PM EST  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">05-NOV-07 03.19.57.562000 PM  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">05-NOV-07 12.19.57.562000 PM -08:00  </li></ol></div> 下面这条语句将会话时区设置会PST： <div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=ALTER%20SESSION%20SET%20TIME_ZONE%20%3D%20'PST'%3B%0A%0ASession%20altered." quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">ALTER SESSION SET TIME_ZONE = 'PST';  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">Session altered.  </li></ol></div> 2. EXTRACT函数     EXTRACT函数用于从x中提取并返回年、月、日、时、分、秒或时区，其中x可以是时间戳类型或DATE类型。下面这个查询使用EXTRACT函数从由TO_DATE()返回的DATE类型中提取年、月、日：<div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=%20SELECT%0AEXTRACT(YEAR%20FROM%20TO_DATE('01-JAN-2008%2019%3A15%3A26'%2C%0A'DD-MON-YYYY%20HH24%3AMI%3ASS'))%20AS%20YEAR%2C%0AEXTRACT(MONTH%20FROM%20TO_DATE('01-JAN-2008%2019%3A15%3A26'%2C%0A'DD-MON-YYYY%20HH24%3AMI%3ASS'))%20AS%20MONTH%2C%0AEXTRACT(DAY%20FROM%20TO_DATE('01-JAN-2008%2019%3A15%3A26'%2C%0A'DD-MON-YYYY%20HH24%3AMI%3ASS'))%20AS%20DAY%0AFROM%20dual%3B%0A%0A%20%20%20%20%20%20%20YEAR%20%20%20%20%20%20%20MONTH%20%20%20%20%20%20%20%20%20%20%20DAY%0A----------%20----------%20----------%0A%202008%20%20%20%20%20%20%20%20%20%20%20%201%20%20%20%20%20%20%20%20%20%20%20%20%201" quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; "> SELECT  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">EXTRACT(YEAR FROM TO_DATE('01-JAN-2008 19:15:26',  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">'DD-MON-YYYY HH24:MI:SS')) AS YEAR,  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">EXTRACT(MONTH FROM TO_DATE('01-JAN-2008 19:15:26',  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">'DD-MON-YYYY HH24:MI:SS')) AS MONTH,  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">EXTRACT(DAY FROM TO_DATE('01-JAN-2008 19:15:26',  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">'DD-MON-YYYY HH24:MI:SS')) AS DAY  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM dual;  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">       YEAR       MONTH           DAY  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">---------- ---------- ----------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; "> 2008            1             1  </li></ol></div>      下面这个查询使用EXTRACT函数从由TO_TIMESTAMP()返回的TIMESTAMP类型中提取时、分、秒：<div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=%20SELECT%0AEXTRACT(HOUR%20FROM%20TO_TIMESTAMP('01-JAN-2008%2019%3A15%3A26'%2C%0A'DD-MON-YYYY%20HH24%3AMI%3ASS'))%20AS%20HOUR%2C%0AEXTRACT(MINUTE%20FROM%20TO_TIMESTAMP('01-JAN-2008%2019%3A15%3A26'%2C%0A'DD-MON-YYYY%20HH24%3AMI%3ASS'))%20AS%20MINUTE%2C%0AEXTRACT(SECOND%20FROM%20TO_TIMESTAMP('01-JAN-2008%2019%3A15%3A26'%2C%0A'DD-MON-YYYY%20HH24%3AMI%3ASS'))%20AS%20SECOND%0AFROM%20dual%3B%0A%0A%20%20%20%20%20%20HOUR%20%20%20%20%20%20%20MINUTE%20%20%20%20%20%20%20SECOND%0A----------%20----------%20----------%0A19%20%20%20%20%20%20%20%20%20%20%20%2015%20%20%20%20%20%20%20%20%20%20%20%2026" quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; "> SELECT  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">EXTRACT(HOUR FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">'DD-MON-YYYY HH24:MI:SS')) AS HOUR,  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">EXTRACT(MINUTE FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">'DD-MON-YYYY HH24:MI:SS')) AS MINUTE,  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">EXTRACT(SECOND FROM TO_TIMESTAMP('01-JAN-2008 19:15:26',  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">'DD-MON-YYYY HH24:MI:SS')) AS SECOND  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM dual;  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">      HOUR       MINUTE       SECOND  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">---------- ---------- ----------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">19            15            26  </li></ol></div>      最后这个查询使用EXTRACT函数从由TO_TIMESTAMP_TZ()返回的TIMESTAMP WITH TIMEZONE类型中提取时区的时、分、秒、区域和时区缩写：<div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=%20SELECT%0AEXTRACT(TIMEZONE_HOUR%20FROM%20TO_TIMESTAMP_TZ(%0A'01-JAN-2008%2019%3A15%3A26%20-7%3A15'%2C%20'DD-MON-YYYY%20HH24%3AMI%3ASS%20TZH%3ATZM'))%0AAS%20TZH%2C%0AEXTRACT(TIMEZONE_MINUTE%20FROM%20TO_TIMESTAMP_TZ(%0A'01-JAN-2008%2019%3A15%3A26%20-7%3A15'%2C%20'DD-MON-YYYY%20HH24%3AMI%3ASS%20TZH%3ATZM'))%0AAS%20TZM%2C%0AEXTRACT(TIMEZONE_REGION%20FROM%20TO_TIMESTAMP_TZ(%0A'01-JAN-2008%2019%3A15%3A26%20PST'%2C%20'DD-MON-YYYY%20HH24%3AMI%3ASS%20TZR'))%0AAS%20TZR%2C%0AEXTRACT(TIMEZONE_ABBR%20FROM%20TO_TIMESTAMP_TZ(%0A'01-JAN-2008%2019%3A15%3A26%20PST'%2C%20'DD-MON-YYYY%20HH24%3AMI%3ASS%20TZR'))%0AAS%20TZA%0AFROM%20dual%3B%0A%0A%20%20%20%20%20%20%20%20TZH%20%20%20%20%20%20%20%20%20%20TZM%20%20%20TZR%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20TZA%0A----------%20----------%20-----------%20----------%0A%20%20-7%20%20%20%20%20%20%20%20%20%20-15%20%20%20PST%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20PST" quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; "> SELECT  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ(  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">'01-JAN-2008 19:15:26 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM'))  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">AS TZH,  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ(  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">'01-JAN-2008 19:15:26 -7:15', 'DD-MON-YYYY HH24:MI:SS TZH:TZM'))  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">AS TZM,  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ(  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">'01-JAN-2008 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">AS TZR,  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ(  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">'01-JAN-2008 19:15:26 PST', 'DD-MON-YYYY HH24:MI:SS TZR'))  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">AS TZA  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM dual;  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">        TZH          TZM   TZR                   TZA  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">---------- ---------- ----------- ----------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  -7          -15   PST                    PST  </li></ol></div>  3. FROM_TZ()      FROM_TZ(x, time_zone)将TIMESTAMP类型的x转换为由time_zone指定的时区，并返回TIMESTAMP WITH TIMEZONE类型。time_zone必须被指定为+|- HH:MI格式的字符串。此函数一般将x和time_zone合并成一个值。     例如，下面这个查询将时间戳2008-05-13 07:15:31.1234和与UTC的时差-7:00合并起来：<div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=%20SELECT%20FROM_TZ(TIMESTAMP%20'2008-05-13%2007%3A15%3A31.1234'%2C%20'-7%3A00')%0AFROM%20dual%3B%0A%0AFROM_TZ(TIMESTAMP'2008-05-1307%3A15%3A31.1234'%2C'-7%3A00')%0A---------------------------------------------------%0A13-MAY-08%2007.15.31.123400000%20AM%20-07%3A00" quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; "> SELECT FROM_TZ(TIMESTAMP '2008-05-13 07:15:31.1234', '-7:00')  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM dual;  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM_TZ(TIMESTAMP'2008-05-1307:15:31.1234','-7:00')  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">---------------------------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">13-MAY-08 07.15.31.123400000 AM -07:00  </li></ol></div>  4. SYS_EXTRACT_UTC()      SYS_EXTRACT_UTC (x)用于将TIMESTAMP WITH TIMEZONE类型的x转换为一个包含UTC时区日期和时间的TIMESTAMP类型。     下面这个查询将2008-11-17 19:15:26 PST转换为UTC：<div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=%20SELECT%20SYS_EXTRACT_UTC(TIMESTAMP%20'2008-11-17%2019%3A15%3A26%20PST')%0AFROM%20dual%3B%0A%0ASYS_EXTRACT_UTC(TIMESTAMP'2008-11-1719%3A15%3A26PST')%0A-------------------------------------------------%0A18-NOV-08%2003.15.26.000000000%20AM" quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; "> SELECT SYS_EXTRACT_UTC(TIMESTAMP '2008-11-17 19:15:26 PST')  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM dual;  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">SYS_EXTRACT_UTC(TIMESTAMP'2008-11-1719:15:26PST')  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">-------------------------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">18-NOV-08 03.15.26.000000000 AM  </li></ol></div>      因为在冬季PST比UTC晚8个小时，因此这个查询返回的TIMESTAMP类型的值比2008-11-17 19:15:26 PST早了8个小时，即18-NOV-08 03.15.26 AM.。     如果是在夏季，则返回的TIMESTAMP类型的值只比UTC早7个小时。<div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=%20SELECT%20SYS_EXTRACT_UTC(TIMESTAMP%20'2008-05-17%2019%3A15%3A26%20PST')%0AFROM%20dual%3B%0A%0ASYS_EXTRACT_UTC(TIMESTAMP'2008-05-1719%3A15%3A26PST')%0A-------------------------------------------------%0A18-MAY-08%2002.15.26.000000000%20AM" quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; "> SELECT SYS_EXTRACT_UTC(TIMESTAMP '2008-05-17 19:15:26 PST')  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM dual;  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">SYS_EXTRACT_UTC(TIMESTAMP'2008-05-1719:15:26PST')  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">-------------------------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">18-MAY-08 02.15.26.000000000 AM  </li></ol></div> 5. TO_TIMESTAMP()      TO_TIMESTAMP(x,)用于将字符串x(可能是CHAR、VARCHAR2、NCHAR或NVARCHAR2)转换为一个TIMESTAMP类型，该函数中可以为x指定可选的参数format。     下面这个查询将字符串2005-05-13 07:15:31.1234转换为TIMESTAMP类型，格式为YYYY-MM-DD HH24:MI:SS.FF：<div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=%20SELECT%20TO_TIMESTAMP('2008-05-13%2007%3A15%3A31.1234'%2C%20'YYYY-MM-DD%20HH24%3AMI%3ASS.FF')%0AFROM%20dual%3B%0A%0ATO_TIMESTAMP('2008-05-1307%3A15%3A31.1234'%2C'YYYY-MM-DDHH24%3AMI%3ASS.FF')%0A-----------------------------------------------------------------%0A13-MAY-08%2007.15.31.123400000%20AM" quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; "> SELECT TO_TIMESTAMP('2008-05-13 07:15:31.1234', 'YYYY-MM-DD HH24:MI:SS.FF')  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM dual;  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">TO_TIMESTAMP('2008-05-1307:15:31.1234','YYYY-MM-DDHH24:MI:SS.FF')  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">-----------------------------------------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">13-MAY-08 07.15.31.123400000 AM  </li></ol></div>  6. TO_TIMESTAMP_TZ()      TO_TIMESTAMP_TZ(x,[ format ])用于将字符串x转换为一个TIMESTAMP WITH TIMEZONE类型，该函数中可以为x指定可选的参数format。     下面这个查询向TO_TIMESTAMP_TZ函数传递时区PST(在格式字符串中使用TZR来识别)：<div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=%20SELECT%20TO_TIMESTAMP_TZ('2008-05-13%2007%3A15%3A31.1234%20PST'%2C%0A'YYYY-MM-DD%20HH24%3AMI%3ASS.FF%20TZR')%0AFROM%20dual%3B%0A%0ATO_TIMESTAMP_TZ('2008-05-1307%3A15%3A31.1234PST'%2C'YYYY-MM-DDHH24%3AMI%3ASS.FFTZR')%0A--------------------------------------------------------------------------%0A13-MAY-08%2007.15.31.123400000%20AM%20PST" quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; "> SELECT TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 PST',  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">'YYYY-MM-DD HH24:MI:SS.FF TZR')  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM dual;  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234PST','YYYY-MM-DDHH24:MI:SS.FFTZR')  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">--------------------------------------------------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">13-MAY-08 07.15.31.123400000 AM PST  </li></ol></div>      下面这个查询使用与UTC的时差 -7:00(-7:00在格式字符串中用TZR和TZM来识别)：<div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=SELECT%20TO_TIMESTAMP_TZ('2008-05-13%2007%3A15%3A31.1234%20-7%3A00'%2C%0A%0A%20'YYYY-MM-DD%20HH24%3AMI%3ASS.FF%20TZH%3ATZM')%0AFROM%20dual%3B%0A%0ATO_TIMESTAMP_TZ('2008-05-1307%3A15%3A31.1234-7%3A00'%2C'YYYY-MM-DDHH24%3AMI%3ASS.FFTZH%0A--------------------------------------------------------------------------%0A13-MAY-08%2007.15.31.123400000%20AM%20-07%3A00" quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">SELECT TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 -7:00',  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; "> 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM')  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM dual;  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234-7:00','YYYY-MM-DDHH24:MI:SS.FFTZH  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">--------------------------------------------------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">13-MAY-08 07.15.31.123400000 AM -07:00  </li></ol></div>  7. 将字符串转换为TIMESTAMP WITH LOCAL TIME ZONE      使用CAST函数可以将一个字符串转换为TIMESTAMP WITH LOCAL TIME ZONE。第4章曾介绍过CAST()，回想一下，CAST(x AS type)将x转换为由type指定的兼容的数据库类型。     下面这个查询使用CAST()将字符串13-JUN-08转换为TIMESTAMP WITH LOCAL TIME ZONE：<div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=%20SELECT%20CAST('13-JUN-08'%20AS%20TIMESTAMP%20WITH%20LOCAL%20TIME%20ZONE)%0AFROM%20dual%3B%0A%0ACAST('13-JUN-08'ASTIMESTAMPWITHLOCALTIMEZONE)%0A---------------------------------------------%0A13-JUN-08%2012.00.00.000000%20AM" quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; "> SELECT CAST('13-JUN-08' AS TIMESTAMP WITH LOCAL TIME ZONE)  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM dual;  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">CAST('13-JUN-08'ASTIMESTAMPWITHLOCALTIMEZONE)  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">---------------------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">13-JUN-08 12.00.00.000000 AM  </li></ol></div>      此查询返回的时间戳包含日期2008年6月13日和时间12 A.M.。     下面这个查询使用CAST()将一个更复杂的字符串转换为TIMESTAMP WITH LOCAL TIME ZONE：<div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=%20SELECT%20CAST(TO_TIMESTAMP_TZ('2008-05-13%2007%3A15%3A31.1234%20PST'%2C%0A'YYYY-MM-DD%20HH24%3AMI%3ASS.FF%20TZR')%20AS%20TIMESTAMP%20WITH%20LOCAL%20TIME%20ZONE)%0AFROM%20dual%3B%0A%0ACAST(TO_TIMESTAMP_TZ('2008-05-1307%3A15%3A31.1234PST'%2C'YYYY-MM-DDHH24%3AMI%3ASS.FF%0A--------------------------------------------------------------------------%0A13-MAY-08%2006.15.31.123400%20AM" quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; "> SELECT CAST(TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 PST',  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">'YYYY-MM-DD HH24:MI:SS.FF TZR') AS TIMESTAMP WITH LOCAL TIME ZONE)  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM dual;  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">CAST(TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234PST','YYYY-MM-DDHH24:MI:SS.FF  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">--------------------------------------------------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">13-MAY-08 06.15.31.123400 AM  </li></ol></div>      此查询返回的时间戳包含日期2008年5月13日和时间6:15:31.1234 AM PST(PST是数据库时区和会话时区)。     下面这个查询与上面的查询做相同的事情，只是时区是EST：<div class="dp-highlighter" id="" style="font-family: Monaco, 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', Consolas, 'Courier New', monospace; font-size: 12px; background-color: transparent; width: 694px; overflow-x: auto; overflow-y: auto; margin-left: 9px; padding-top: 1px; padding-right: 1px; padding-bottom: 1px; padding-left: 1px; word-break: break-all; word-wrap: break-word; "><div class="bar"><div class="tools" style="padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; text-align: left; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; color: black; font-weight: bold; ">Sql代码 <embed src="http://tivan.iteye.com/.s/syntaxhighlighter/clipboard_new.swf" width="14" height="15" flashvars="clipboard=%20SELECT%20CAST(TO_TIMESTAMP_TZ('2008-05-13%2007%3A15%3A31.1234%20EST'%2C%0A'YYYY-MM-DD%20HH24%3AMI%3ASS.FF%20TZR')%20AS%20TIMESTAMP%20WITH%20LOCAL%20TIME%20ZONE)%0AFROM%20dual%3B%0A%0ACAST(TO_TIMESTAMP_TZ('2008-05-1307%3A15%3A31.1234EST'%2C'YYYY-MM-DDHH24%3AMI%3ASS.FF%0A--------------------------------------------------------------------------%0A13-MAY-08%2004.15.31.123400%20AM" quality="high" allow.access="always" type="application/x-shockwave-flash" pluginspage="http://www.macromedia.com/go/getflashplayer"> <a href="http://tivan.iteye.com/blog/772378" title="收藏这段代码" style="color: rgb(16, 138, 198); text-decoration: underline; " target="_blank"><img class="star" src="http://tivan.iteye.com/images/icon_star.png" alt="收藏代码" style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; "></a></div></div><ol start="1" class="dp-sql" style="font-size: 1em; line-height: 1.4em; margin-top: 0px; margin-right: 0px; margin-bottom: 1px; margin-left: 0px; padding-top: 2px; padding-right: 0px; padding-bottom: 2px; padding-left: 0px; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: solid; border-right-style: solid; border-bottom-style: solid; border-left-style: solid; border-top-color: rgb(209, 215, 220); border-right-color: rgb(209, 215, 220); border-bottom-color: rgb(209, 215, 220); border-left-color: rgb(209, 215, 220); list-style-type: decimal; list-style-position: initial; list-style-image: initial; background-color: rgb(255, 255, 255); color: rgb(43, 145, 175); "><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; "> SELECT CAST(TO_TIMESTAMP_TZ('2008-05-13 07:15:31.1234 EST',  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">'YYYY-MM-DD HH24:MI:SS.FF TZR') AS TIMESTAMP WITH LOCAL TIME ZONE)  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">FROM dual;  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">CAST(TO_TIMESTAMP_TZ('2008-05-1307:15:31.1234EST','YYYY-MM-DDHH24:MI:SS.FF  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">--------------------------------------------------------------------------  </li><li style="font-size: 1em; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 38px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 10px; border-left-width: 1px; border-left-style: solid; border-left-color: rgb(209, 215, 220); background-color: rgb(250, 250, 250); line-height: 18px; ">13-MAY-08 04.15.31.123400 AM  </li></ol></div>      此查询返回的时区包含日期2008年3月13日和时间4:15:31.1234 AM PST(因为PST比EST晚3小时，所以此时间戳返回的时间比实际查询的时间早3小时)。

页: [1]

Chinaunix's Archiver

Oracle与时间戳有关的函数