Oracle：通过 TZNAME 检索可能吗？

发布于 2024-12-10 12:39:22 字数 385 浏览 1 评论 0原文

我们如何才能找到数据库中我们所在的 TZNAME？ TZNAME 我的意思是（从列表中）

select * from V$TIMEZONE_NAMES;

如果我们执行一个

select dbtimezone from dual

I get dbtimezone = -04:00

我们检索 DBTIMEZONE 这是时区的数字表示形式，但由于它们有很多，但在不同的地方，我们无法做到 100% 准确。例如，当前我们有 -04:00，可以是 EST CANADA 和 EST NY（或不涉及夏令时的日期）

有没有办法检索数据库时区的实际 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;

If we do a

select dbtimezone from dual

I get dbtimezone = -04:00

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)

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

Thank you

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

风启觞 2024-12-17 12:39:22

我们可以从时间戳中提取 TIMEZONE_REGION，为其提供带有时区的 TIMESTAMP。就像这样：

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，因为多个时区名称映射到负四小时的偏移量。在会话级别设置时区名称的方法有多种；其中之一可能是解决此问题的最佳方法。了解更多信息。

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>

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.

回复收藏 0 原文

~没有更多了~