Oracle:通过TZNAME检索可能吗? [英] Oracle: Retrieve by TZNAME Is it possible?

问题描述

我们如何在数据库中找出属于哪个TZNAME? TZNAME的意思是(从列表中)

How can we find out which TZNAME we are under in our Database? and by TZNAME I mean something like (from the list of)

select * from V$TIMEZONE_NAMES;

如果我们做

select dbtimezone from dual

我得到dbtimezone = -04:00

I get dbtimezone = -04:00

我们检索了DBTIMEZONE，它是时区的数字表示形式，但是由于其中有很多，但是在不同的地方，所以我们不能做到100％准确.例如，当前我们有-04:00，可以是EST CANADA和EST NY(或不涉及夏令时的日期)

we retrieve the DBTIMEZONE which is the number representation of the timezone, but since there are many of them but in different places, we won't be able to be 100 % accurate. Example, currently we have -04:00 which can be Both EST CANADA and EST NY (or a date that doesn't involve daylights savings)

有什么方法可以检索数据库时区的实际TZNAME而不是实际时间?

Is there any way to retrieve the actual TZNAME of the database timezone rather than the actual time?

谢谢

推荐答案

我们可以从时间戳中提取TIMEZONE_REGION，并为其提供TIMESTAMP WITH TIMEZONE.像这样:

We can extract the TIMEZONE_REGION from a timestamp, providing its a TIMESTAMP WITH TIMEZONE. Like so:

SQL> select extract(timezone_region from current_timestamp)
  2  from dual
  3  /

EXTRACT(TIMEZONE_REGIONFROMCURRENT_TIMESTAMP)
----------------------------------------------------------------
CET

SQL>  alter session set time_zone='UTC';

Session altered.

SQL> select extract(timezone_region from current_timestamp)
  2  from dual
  3  /

EXTRACT(TIMEZONE_REGIONFROMCURRENT_TIMESTAMP)
----------------------------------------------------------------
UTC

SQL> alter session set time_zone='-04:00';

Session altered.

SQL> select extract(timezone_region from current_timestamp)
  2  from dual
  3  /

EXTRACT(TIMEZONE_REGIONFROMCURRENT_TIMESTAMP)
----------------------------------------------------------------
UNKNOWN

SQL> 

最后一个结果返回UNKNOWN，因为多个时区名称映射到负四个小时的偏移量.有多种方法可以在会话级别设置时区名称.其中之一可能是解决此问题的最佳方法. 了解更多.

The last result returns UNKNOWN because more than one Timezone name maps to an offset of minus four hours. There are various ways of setting the timezone name at the session level; one of those is likely to be the best way of work around this problem. Find out more.

这篇关于Oracle:通过TZNAME检索可能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！