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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #257289 is a reply to message #257223] Wed, 08 August 2007 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Not all timezone has a name.
Barbara here assumes (correct me if I'm wrong) that when the time zone given in the last parameter is not valid it is in the form SHH:MI (S=sign) as in the return of *timezone function in her example.
In this case, she only keep the hour part ignoring the minutes.
You can enhance the function taking into account this last part.

Regards
Michel
Re: Reading the current time zone [message #258044 is a reply to message #257289] Thu, 09 August 2007 23:12 Go to previous messageGo to next message
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 #258048 is a reply to message #258044] Thu, 09 August 2007 23:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Thanks for this useful function.

Regards
Michel
Re: Reading the current time zone [message #258139 is a reply to message #49176] Fri, 10 August 2007 06:48 Go to previous messageGo to next message
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 Smile

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 #258153 is a reply to message #258139] Fri, 10 August 2007 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Do you really need to select?
Try without it.

Please make sure that lines of code do not exceed 80 or 100 characters when you format.

Regards
Michel
Re: Reading the current time zone [message #258163 is a reply to message #49176] Fri, 10 August 2007 07:26 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Yeah it would work without select, just I was trying something more in that function...

Well, just for curosity, will a SELECT from DUAL will be heavy which I should avoid?
Re: Reading the current time zone [message #258168 is a reply to message #258163] Fri, 10 August 2007 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Yes it is.
PL/SQL and SQL are 2 different engines.
When you use one into the other you have to switch of context.
Any context switch is expensive, especially when it is useless.

Regards
Michel
Re: Reading the current time zone [message #326995 is a reply to message #258044] Fri, 13 June 2008 05:53 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi Barbara,
I looked into your function for converting date to the timezone not listed for new_time().
I am stuck at such a problem.
Our application was earlier in India and now the server is migrated to US. Now its taking US operating System's time with SYSDATE function. We have used sysdate everywhere in more than 75 procedures. Now its giving the time of US but we need the Indian time whose SESSIONTIMEZONE is +05:30.
I have looked into some articles and know that if I need to change the session time then current_date can help by ALTER SESSION. But SYSDATE is used everywhere and would be very difficult to change things everywhere.

Please suggest me something which can help get the Indian time when the database is on US server. Sysdate gives the Operating System date and can't be changed for a session. Is there a way to get the date of Indian timezone with less changes to the codes when Sysdate is used extensively.

Waiting eagerly for your reply.

Thanks ,
Mona
Re: Reading the current time zone [message #326996 is a reply to message #326995] Fri, 13 June 2008 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As I said you in your other topic, yuo can't do it with no change in your code.
http://www.orafaq.com/forum/m/326618/102589/#msg_326618
The best solution is, as you will to change your code, to use a more appropriate datatype.

Regards
Michel
Re: Reading the current time zone [message #327156 is a reply to message #49176] Sat, 14 June 2008 06:35 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

The portal is live since last one year so can't change the datatype. There are over 75 procedures so making changes would be a big thing everywhere.

Please suggest something or a way where even the change needs to be done as a last solution what would be the best way to make least nmber of changes to the code.

Thanks again for looking into this,

Mona
Re: Reading the current time zone [message #327187 is a reply to message #327156] Sat, 14 June 2008 10:22 Go to previous message
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
Previous Topic: Partition on table
Next Topic: Catch new update , new insert into a table and write to a file!
Goto Forum:
  


Current Time: Fri Feb 14 18:27:28 CST 2025