How to Get current time zone in SQL and Oracle

Question

5.00/5 (1 vote)

See more:

Hi All,
I have a server application in C++ which communicates between application and database. My server application communicates with both oracle and SQL. Now i want to get the time zone of my oracle and SQL database. I am able to get the current time zone in SQL now i want to get the current time zone in oracle. Can anyone please help me to find the time zone in oracle.

Thanks in advance.

Posted 7-May-14 2:42am

hari111r

Add a Solution

Comments

barneyman 7-May-14 23:20pm

why on earth would you want to know the sql/oracle servers' timezone?

2 solutions

Solution 2

Please, read my comment to the solution 1 by Kornfeld Eliyahu Peter.

I'm almost sure it's impossible to get time zone name in Oracle server, because it's not supported.
I believe you need to write custom function[^]. The basic idea is to get time offset and compare it with the list of time zones, than to return proper time zone name.

Have a look here:
A Time Zones[^]
Obtaining Time Zone Names[^]

Note: i don't have access to Oracle database/server, so i can be wrong.

BTW: It might be useful too: Converting Time Zones With the AT TIME ZONE Clause[^]

Posted 7-May-14 3:48am

Maciej Los

Updated 7-May-14 7:27am

v3

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Accepted Answer · 2014-05-07T02:46:00

Solution 1

You can use DBTIMEZONE or SESSIONTIMEZONE for that...
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions143.htm[^]
http://psoug.org/definition/DBTIMEZONE.htm[^]

Posted 7-May-14 2:46am

Kornfeld Eliyahu Peter

Comments

hari111r 7-May-14 8:52am

Hi Thanks for replay. I gives me only offset. I want time zone name. Is there any way to get the time zone name.

Kornfeld Eliyahu Peter 7-May-14 8:58am

Maybe this can help you http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions052.htm#SQLRF00639

hari111r 7-May-14 9:26am

I tried with EXTRACT(TIMEZONE_REGION) but in the link itself it says "The ambiguity arises because the time zone numerical offset is provided in the expression, and that numerical offset may map to more than one time zone region." Any other way to find it or any thing i am missing here.

Kornfeld Eliyahu Peter 7-May-14 9:32am

Create a variable of type TIMESTAMP WITH [LOCAL] TIMEZONE. Convert your date to it using CAST, than run EXTRACT on it...

"If TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_ABBR, TIMEZONE_REGION, or TIMEZONE_OFFSET is requested, then expr must evaluate to an expression of data type TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE."

Maciej Los 7-May-14 9:31am

Is there any problem to convert time offset to date/time? 
I'm afraid, the function to get time zone name on Oracle server doesn't exists! Have a look: Detecting System Time Zone

Kornfeld Eliyahu Peter 7-May-14 9:35am

Read here: http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions052.htm#SQLRF00639 
EXTRAXCT(TIMEZONE_ABBR from [timetamp-with-timezone-variable]) should give the answer