oracle 在sql中使用Oracle“at time zone”函数——问题及解决方法

Using the Oracle "at time zone" function in sql - problem and solution

提问by Thomas Gnade

I have a table with a date column that I know is stored in GMT. I have a procedure that accepts a date input and an account ID. The procedure: 1) gets the account ID timezone (stored in table account) 2) determines the start and end range in GMT as follows: v_start_time := cast( from_tz( cast( i_date as timestamp ), v_tz ) at time zone c_gmt as date ); -- where i_date is input, v_tz is 'US/Eastern' or any other tzname from v$timezone_names, and c_gmt is the string 'GMT' v_end_time := v_start_time + 1; -- Add exactly one day to start date 3) return sys_refcursor to caller as:

我有一个带有日期列的表，我知道它存储在 GMT 中。我有一个接受日期输入和帐户 ID 的程序。过程：1）获取账户ID时区（存储在表account中）2）确定GMT的开始和结束范围如下： v_start_time := cast( from_tz( cast( i_date as timestamp ), v_tz ) at time zone c_gmt as日期 ）; -- 其中 i_date 是输入，v_tz 是 'US/Eastern' 或来自 v$timezone_names 的任何其他 tzname，而 c_gmt 是字符串 'GMT' v_end_time := v_start_time + 1; -- 将一天添加到开始日期 3) 将 sys_refcursor 返回给调用者：

open o_cur for
select gmt_col, some_value
from my_table
where account_id = i_account_id
    and gmt_col between v_start_time and v_end_time;

However, the developer would like both the gmt_date and the local time in the cursor. First, I attempted to use the exact same conversion method as I had to determine v_start_time, that is:

但是，开发人员希望游标中同时包含 gmt_date 和本地时间。首先，我尝试使用与必须确定 v_start_time 完全相同的转换方法，即：

open o_cur for 
select gmt_col,
    cast( from_tz( cast( gmt_col as timestamp ), c_gmt ) at time zone v_tz as date ) as local_time, some_value
from my_table
where account_id = i_account_id
    and gmt_col between v_start_time and v_end_time;

However, when compiled, this results in ORA-00905: missing keyword. I attempted to add the single quotes around the "v_tz" like: chr( 39 ) || v_tz || chr( 39 ), but that doesn't work - the proc compiles, but when I open the cursor, I get ORA-01882: timezone region not found. After a bit of experimentation, here are two solutions that allow "at time zone" to work smoothly in sql:

但是，在编译时，这会导致 ORA-00905：缺少关键字。我试图在“v_tz”周围添加单引号，例如： chr( 39 ) || v_tz || chr( 39 )，但这不起作用 - proc 编译，但是当我打开游标时，我得到 ORA-01882: timezone region not found。经过一些实验，这里有两个解决方案，可以让“在时区”在 sql 中顺利工作：

SOLUTION 1:

解决方案1：

open o_cur for
select gmt_col,
    cast( from_tz( cast( gmt_col as timestamp ), c_gmt ) at time zone ( select v_tz from dual ) as date ) as local_time, some_value
from my_table
where account_id = i_account_id
    and gmt_col between v_start_time and v_end_time;

SOLUTION 2:

解决方案2：

in package spec:

在包装规格中：

function echo( i_sound in varchar2 ) return varchar2;
pragma restrict_references( echo, wnps, rnps, wnds, rnds );

in package body:

在包体中：

function echo( i_sound in varchar2 ) return varchar2 is begin return i_sound; end;

in procedure:

在程序中：

open o_cur for
select gmt_col,
    cast( from_tz( cast( gmt_col as timestamp ), c_gmt ) at time zone echo( v_tz ) as date ) as local_time, some_value
from my_table
where account_id = i_account_id
   and gmt_col between v_start_time and v_end_time;

Performance appears to be comparable for each. The second solution hints at something I've started to do recently, which is to use functions to return "constants" with pragma restrict_references, so I can use the constant values flexibly between pl/sql and sql. For example:

两者的性能似乎相当。第二个解决方案暗示了我最近开始做的事情，即使用函数返回带有 pragma restrict_references 的“常量”，这样我就可以在 pl/sql 和 sql 之间灵活地使用常量值。例如：

function c_gmt return varchar2; pragma restrict_references( c_gmt, wnds, rnds, wnps, rnps );

函数 c_gmt 返回 varchar2；pragma restrict_references( c_gmt, wnds, rnds, wnps, rnps );

select * from v$timezone_names where tzabbrev = c_gmt; select c_gmt from dual; v_start_time := blah blah blah || c_gmt; etc...

select * from v$timezone_names where tzabbrev = c_gmt; 从双中选择 c_gmt；v_start_time := blah blah blah || c_gmt; 等等...

回答by Craig

You shouldn't need the extra select from dual. Just putting the variable in parenthesis should do the trick (don't ask me why though):

您不应该需要从 Dual 中进行额外的选择。只需将变量放在括号中就可以解决问题（不要问我为什么）：

open o_cur for  
select gmt_col, 
    cast( from_tz( cast( gmt_col as timestamp ), c_gmt ) at time zone (v_tz) as date ) as local_time, some_value 
from my_table 
where account_id = i_account_id 
    and gmt_col between v_start_time and v_end_time;