Home » SQL & PL/SQL » SQL & PL/SQL » Reading the current time zone
Reading the current time zone [message #49176] |
Mon, 24 January 2005 09:23  |
padma
Messages: 22 Registered: April 2001
|
Junior Member |
|
|
Hi All,
Is there any way I can read the current time zone in the DB/Session and use that information to convert to another time zone
I am looking for a function
Convert_timezone(p_date in date, p_newTimeZone in char(3)) returns date
None of the date fields in our DB store the time zones along with them.
Thanks in advance.
|
|
|
Re: Reading the current time zone [message #49178 is a reply to message #49176] |
Mon, 24 January 2005 15:07   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
If you know the dbtimezone or sessiontimezone, then you can use the Oracle new_time function. If not, then you can create your own function to use the dbtimezone or sessiontimezone. Depending on your settings, these may be displayed as characters, like PST or PDT or they may be displayed as offsets from GMT, like -07:00 or -08:00. In the demonstration below, I have created a function that does what you want and uses an additional parameter with a default to allow for dbtimezone or sessiontimezone and uses an exception to allow for character or offset formats. I have included a few queries using both the Oracle new_time function and the user-defined function to show that the results are the same.
-- user-defined function:
scott@ORA92> create or replace function convert_timezone
2 (p_date in date,
3 p_newTimeZone in varchar2,
4 p_db_or_session in varchar2 DEFAULT DBTIMEZONE)
5 return date
6 as
7 e_invalid_time_zone exception;
8 pragma exception_init (e_invalid_time_zone, -1857);
9 begin
10 return new_time (p_date, p_db_or_session, p_newTimeZone);
11 exception when e_invalid_time_zone then
12 return new_time (p_date, 'GMT', p_newTimeZone)
13 - (substr (p_db_or_session, 1, instr (p_db_or_session, ':') - 1) / 24);
14 end convert_timezone;
15 /
Function created.
scott@ORA92> show errors
No errors.
-- settings:
scott@ORA92> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'
2 /
Session altered.
scott@ORA92> select dbtimezone, sessiontimezone from dual
2 /
DBTIME SESSIONTIMEZONE
------ ---------------------------------------------------------------------------
-07:00 -08:00
-- with dbtimezone:
scott@ORA92> select sysdate,
2 new_time (sysdate, 'PDT', 'GMT') as new_tme,
3 convert_timezone (sysdate, 'GMT') as convert_timezone
4 from dual
5 /
SYSDATE NEW_TME CONVERT_TIMEZONE
-------------------- -------------------- --------------------
24-jan-2005 15:57:55 24-jan-2005 22:57:55 24-jan-2005 22:57:55
scott@ORA92> select sysdate,
2 new_time (sysdate, 'PDT', 'EDT') as new_time,
3 convert_timezone (sysdate, 'EDT') as convert_timezone
4 from dual
5 /
SYSDATE NEW_TIME CONVERT_TIMEZONE
-------------------- -------------------- --------------------
24-jan-2005 15:57:55 24-jan-2005 18:57:55 24-jan-2005 18:57:55
-- with sessiontimezone:
scott@ORA92> select sysdate,
2 new_time (sysdate, 'PST', 'GMT') as new_time,
3 convert_timezone (sysdate, 'GMT', sessiontimezone) as convert_timezone
4 from dual
5 /
SYSDATE NEW_TIME CONVERT_TIMEZONE
-------------------- -------------------- --------------------
24-jan-2005 15:57:55 24-jan-2005 23:57:55 24-jan-2005 23:57:55
scott@ORA92> select sysdate,
2 new_time (sysdate, 'PST', 'EDT') as new_time,
3 convert_timezone (sysdate, 'EDT', sessiontimezone) as convert_timezone
4 from dual
5 /
SYSDATE NEW_TIME CONVERT_TIMEZONE
-------------------- -------------------- --------------------
24-jan-2005 15:57:55 24-jan-2005 19:57:55 24-jan-2005 19:57:55
|
|
|
Re: Reading the current time zone [message #257223 is a reply to message #49176] |
Tue, 07 August 2007 16:01   |
tomstone_98
Messages: 44 Registered: May 2007 Location: Atlanta
|
Member |
|
|
We are running Oracle 10.2.0.1, so this question may not be valid.
Why is there a need to check for exceptions in the convert_timezone function provided by Barbara and then change the result by x hours?
|
|
|
|
Re: Reading the current time zone [message #258044 is a reply to message #257289] |
Thu, 09 August 2007 23:12   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I had to think about it for a while to remember why I did what I did 2-1/2 years ago. The original question specified the parameter for the new timezone as char(3) and that the db timezone or session timezone was to be read from the database. If you knew the timezone, you could just use new_time and would not need the function. Some databases have their timezone stored as three characters, while others have it stored as the number of hours plus or minus from GMT. The exception was to catch those that are in the latter format, convert the time to GMT time, extract the number of hours from GMT, then apply it. The example below demonstrates how, if your timezone is in the latter format, without the exception, it produces an error. With the exception, it extracts the "-07" from the "-07:00" timezone format and applies the offset to the GMT time.
SCOTT@10gXE> SELECT * FROM v$version
2 /
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SCOTT@10gXE> create or replace function convert_timezone
2 (p_date in date,
3 p_newtimezone in varchar2,
4 p_db_or_session in varchar2 DEFAULT DBtimezone)
5 return date
6 as
7 e_invalid_time_zone exception;
8 pragma exception_init (e_invalid_time_zone, -1857);
9 begin
10 return new_time (p_date, p_db_or_session, p_newtimezone);
11 --exception when e_invalid_time_zone then
12 -- return new_time (p_date, 'GMT', p_newtimezone)
13 -- - (substr (p_db_or_session, 1, instr (p_db_or_session, ':') - 1) / 24);
14 end convert_timezone;
15 /
Function created.
SCOTT@10gXE> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'
2 /
Session altered.
SCOTT@10gXE> select dbtimezone, sessiontimezone from dual
2 /
DBTIME SESSIONTIMEZONE
------ ---------------------------------------------------------------------------
+00:00 -07:00
SCOTT@10gXE> select sysdate,
2 new_time (sysdate, 'PDT', 'GMT') as new_tme,
3 convert_timezone (sysdate, 'GMT', sessiontimezone) as convert_timezone
4 from dual
5 /
convert_timezone (sysdate, 'GMT', sessiontimezone) as convert_timezone
*
ERROR at line 3:
ORA-01857: not a valid time zone
ORA-06512: at "SCOTT.CONVERT_TIMEZONE", line 10
ORA-06512: at line 1
SCOTT@10gXE> create or replace function convert_timezone
2 (p_date in date,
3 p_newtimezone in varchar2,
4 p_db_or_session in varchar2 DEFAULT DBtimezone)
5 return date
6 as
7 e_invalid_time_zone exception;
8 pragma exception_init (e_invalid_time_zone, -1857);
9 begin
10 return new_time (p_date, p_db_or_session, p_newtimezone);
11 exception when e_invalid_time_zone then
12 return new_time (p_date, 'GMT', p_newtimezone)
13 - (substr (p_db_or_session, 1, instr (p_db_or_session, ':') - 1) / 24);
14 end convert_timezone;
15 /
Function created.
SCOTT@10gXE> select sysdate,
2 new_time (sysdate, 'PDT', 'GMT') as new_tme,
3 convert_timezone (sysdate, 'GMT', sessiontimezone) as convert_timezone
4 from dual
5 /
SYSDATE NEW_TME CONVERT_TIMEZONE
-------------------- -------------------- --------------------
09-aug-2007 21:04:11 10-aug-2007 04:04:11 10-aug-2007 04:04:11
SCOTT@10gXE>
|
|
|
|
Re: Reading the current time zone [message #258139 is a reply to message #49176] |
Fri, 10 August 2007 06:48   |
himang
Messages: 282 Registered: March 2005 Location: Bangalore
|
Senior Member |

|
|
Hi,
As NEW_TIME only supports few timezones, so I have written a small function to make use of TZ_OFFSET to change to different timezones. Maybe this helps for some 
create or replace FUNCTION func_tz_convert
(pi_date IN TIMESTAMP,
pi_fr_timezone IN VARCHAR2 DEFAULT 'CST',
pi_to_timezone IN VARCHAR2)
RETURN DATE IS
fr_timezone VARCHAR2(50) := pi_fr_timezone;
to_timezone VARCHAR2(50) := pi_to_timezone;
lv_conv_time TIMESTAMP;
BEGIN
SELECT pi_date
+ ((TO_NUMBER(SUBSTR(TZ_OFFSET(to_timezone),1,1) ||
(SUBSTR(TZ_OFFSET(to_timezone),2,2) * 60)
+ SUBSTR(TZ_OFFSET(to_timezone),5,2))
- TO_NUMBER(SUBSTR(TZ_OFFSET(fr_timezone),1,1) ||
(SUBSTR(TZ_OFFSET(fr_timezone),2,2) * 60)
+ SUBSTR(TZ_OFFSET(fr_timezone),5,2)))/(24*60))
INTO lv_conv_time
FROM dual;
RETURN lv_conv_time;
END func_tz_convert;
SQL> select sysdate,
2 func_tz_convert(sysdate,'CST','Japan') from dual;
SYSDATE FUNC_TZ_CONVERT(SYSD
-------------------- --------------------
10-aug-2007 06:45:32 10-aug-2007 20:45:32
[Updated on: Fri, 10 August 2007 07:12] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: Reading the current time zone [message #327187 is a reply to message #327156] |
Sat, 14 June 2008 10:22  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
The application and model was built for server time zone and now you want to use it for several client ones.
Time zone, and above all client time zone, is not known from sysdate and your application.
This implies application upgradation.
There is no way to do it without changing nothing (or just parameter).
You already know the solution: using current_date.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Fri Feb 14 18:27:28 CST 2025
|