Skip to Main Content
  • Questions
  • Retrieving date in different timezones from the database(by not using new_time function)

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Puneet.

Asked: December 18, 2002 - 1:21 pm UTC

Last updated: March 01, 2010 - 12:05 pm UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

How to get the time stored in the database in different country's timezone?
All the trasaction in the database are looged in against one timezone(CST) but users in Germany/UK/France/Australia wants to see all the times in their timezone.
Orcale has new_time function to convert a 'time' from "this" timezone to "other" timezone but this does not cover all the timezones in the world. German time zone is not supported by this function.
What is the good way to convert the time stored in the database to a different timezone that will take care of the daylight saving also(other than using new_time function)?

Thanks in advance!!..
Puneet.

and Tom said...

Well, easiest = Oracle9i where timezone support is builtin to the new TIMESTAMP datatype.

In 8i -- you can use java to do this easily and efficiently. I'm in "EST" (or EDT depending on the time of year) so my example will be based on that. I'll load in a tiny bit of java:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace and compile java source named "myTZ"
2 as
3 import java.util.*;
4 import java.text.*;
5 public class myTZ extends Object
6 {
7 static long getTimeZoneOffset(String sTzname)throws Exception
8 {
9 TimeZone oZone = TimeZone.getTimeZone(sTzname);
10 return oZone.getRawOffset()/1000;
11 }
12
13 public static long getServerTimeZoneOffset( String sTzname ) throws Exception
14 {
15 Date d = new Date();
16 DateFormat df2 = new SimpleDateFormat( "z" );
17
18 df2.setTimeZone( TimeZone.getDefault() );
19 return getTimeZoneOffset( df2.format(d) ) - getTimeZoneOffset( sTzname );
20 }
21 }
22 /

Java created.


Now, I'll use an application context -- this will have an OFFSET that I can add to a DATE to convert from the server TZ to any TZ I want

ops$tkyte@ORA817DEV.US.ORACLE.COM> CREATE OR REPLACE context tz using myTZ
2 /

Context created.

and now the package to wrap the java and set the context:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package myTz
2 as
3 FUNCTION GetServerTimeZoneOffset( "sTzname" in varchar2 )
4 RETURN NUMBER
5 AS LANGUAGE JAVA
6 NAME 'myTZ.getServerTimeZoneOffset( java.lang.String ) return long';
7
8 procedure init( p_client_tz in varchar2 );
9 end;
10 /

Package created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package body myTz
2 as
3 procedure init( p_client_tz in varchar2 )
4 is
5 begin
6 dbms_session.set_context( 'TZ', 'OFFSET', getServerTimeZoneOffset(p_client_tz)/(60*60*24) );
7 end;
8 end;
9 /

Package body created.



so, all your app has to do is upon connection called "myTz.init" and pass the client timezone, here I'll test CST and PST:

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec myTz.init( 'CST' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select sysdate, sysdate+sys_context( 'TZ', 'OFFSET' )
2 from dual;

SYSDATE SYSDATE+SYS_CONTEXT(
-------------------- --------------------
18-dec-2002 15:09:48 18-dec-2002 16:09:48

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec myTz.init( 'PST' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select sysdate, sysdate+sys_context( 'TZ', 'OFFSET' )
2 from dual;

SYSDATE SYSDATE+SYS_CONTEXT(
-------------------- --------------------
18-dec-2002 15:09:48 18-dec-2002 18:09:48



That shows how to convert my EST date/time into CST or PST by adding that sys_context value to it. Since sys_context is treated just like a bind variable -- it'll be fast

Just make sure to convert on the LHS of equations -- eg don't


select * from t where database_column + sys_context() = to_date( '1pm', 'hham' )

Do this instead

select * from t where database_column = to_date( '1pm', 'hham')-sys_context()

instead! (so indexes continue to function as before)





Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Have a doubt...

Puneet Gupta, December 18, 2002 - 6:04 pm UTC

Thanks for such a quick response.
It worked absolutely fine.
But one thing is not clear to me.
Will it supprot all the timezones for all the country or is there any restriction to this?
I tried different timezones and found out that if the time zone is not recognized then it will default to GMT.So the right thing to do will be to get the list of all the timezone which is acceped by TimeZone.getTimeZone method.

My timezone is 'CST'.

DB> exec myTz.init( 'GMT');
PL/SQL procedure successfully completed.
DB> select trunc(sys_context( 'TZ', 'OFFSET' ),2) as "Diffrence" from dual;
Diffrence
---------
-.25
*** above is correct

DB> exec myTz.init( 'EST');
PL/SQL procedure successfully completed.
DB> select trunc(sys_context( 'TZ', 'OFFSET' ),2) as "Diffrence" from dual;
Diffrence
---------
-.04
*** above is correct

When I wanted to get the difference with Germany I tried
MEZ(thought it would be the timezone code for Germany)
DB> exec myTz.init( 'MEZ');
PL/SQL procedure successfully completed.
DB> select trunc(sys_context( 'TZ', 'OFFSET' ),2) as "Diffrence" from dual;
Diffrence
---------
-.25
***above is the diff with GMT and not with German time or MEZ

Then I tried a junk time zone 'XXX'
DB> exec myTz.init( 'XXX');
PL/SQL procedure successfully completed.
DB> select trunc(sys_context( 'TZ', 'OFFSET' ),2) as "Diffrence" from dual;
Diffrence
---------
-.25
***It gave the diff with GMT (default)

Therefore, the problem for me is to find the codes supported in this method for different timezones(with or without daylight).

Thanks,
Puneet.

Tom Kyte
December 19, 2002 - 7:21 am UTC

Well, it is whatever Java supports. (and you could obviously add some code to make it a little more bullet proof, so it doesn't default to GMT)...

see
</code> http://www.javaworld.com/javaworld/jw-02-1999/jw-02-internationalize.html <code>

for some notes on this.

Also -- you might consider loading a table of your own with a list of timezones and offsets from GMT. Then you could just drop the java except to find the server timezone (you want to do that for CST CDT changes) and do the rest in plsql. Or even add a table with start/stop dates for CST/CDT for a couple of years and drop the java all together.

DayLight Savings

Santhanam, July 30, 2003 - 10:13 am UTC

Good Example




Q on timezones

Anil Pant, May 03, 2004 - 6:57 am UTC

Is it possible to configure multiple db's on same machine but with different time zones ? If so how do we do ?

Tom Kyte
May 03, 2004 - 7:34 am UTC

yes, the TZ is picked up from the environment in 8i and before -- just "export TZ=..." when you set the Oracle_home/Oracle_sid, before you startup the database.

In 9i, there is a dbtimezone setting as well -- see alter database.

If you are on windows -- no idea how you might do the first one as simple things are not always so easy on that platform.

Will Sysdate return as per timezone

A reader, September 29, 2009 - 7:21 am UTC

Thanks.
After changing the Db Timezone through TZ variable in Unix. Will sysdate return as per the timezone changed for the database or will it still continue to refer the operating system time.

i.e select sysdate from dual; will return the sysdate with respect to the timezone with which database was started or will refer the operating system time.
Tom Kyte
October 02, 2009 - 9:14 am UTC

sysdate will return what

$ date

would return for the user environment that started the dedicated or shared server.

eg: if you connect to a shared server, you will get the environment that was in place for the account that started the database.

if you connect to a dedicated server over the network, you will get the environment that was in place for the account that started the listener

if you connect to a dedicated server directly on the server - without using a listener - you will get YOUR environment.



they are ALL the operating system 'time', the timezone however is a factor of the environment.


I demonstrate case 3 below:

[tkyte@dellpe ~]$ <b>date
Fri Oct  2 10:13:05 EDT 2009</b>
[tkyte@dellpe ~]$ plus

SQL*Plus: Release 9.2.0.8.0 - Production on Fri Oct 2 10:13:09 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

ops$tkyte%ORA9IR2> select to_char( sysdate, 'hh24:mi:ss' ) from dual;
<b>
TO_CHAR(
--------
10:13:18
</b>
ops$tkyte%ORA9IR2> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
[tkyte@dellpe ~]$ <b>export TZ=PDT
[tkyte@dellpe ~]$ date
Fri Oct  2 14:13:27 PDT 2009</b>
[tkyte@dellpe ~]$ plus

SQL*Plus: Release 9.2.0.8.0 - Production on Fri Oct 2 14:13:35 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

ops$tkyte%ORA9IR2> select to_char( sysdate, 'hh24:mi:ss' ) from dual;
<b>
TO_CHAR(
--------
14:13:37
</b>

Inputs on setting through listener

A reader, March 01, 2010 - 5:50 am UTC

Hi Tom,

From the thread i understand when there is a need to show the date/time of the client timezone. We had a similar requirement for one of our middle east customers. We achieved it by a small setting in listener.ora file

LISTENERA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = HAL)(PORT = 1521))
)

SID_LIST_LISTENERA =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/Software/Oracle/64/10g)
(SID_NAME = v10g)
(ENVS='TZ=CST6CDT')-- line which changes timezone
)
)

Drawback is that its not supported in windows platform. Here i would like to know how to interpret "CST6CDT" though i tried and queried V$TIMEZONE_NAMES i was able to make some little meaning out of it. Request you to explain about the usage. What does CST Denotes and CDT denotes and what is the purpose of that literal 6 in between both.

Thanks in advance.
Tom Kyte
March 01, 2010 - 12:05 pm UTC

cst/cdt is central standard/daylight savings time.

it is -6 from GMT.


http://www.travelmath.com/time-zone/CST6CDT

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.