Oracle Application Developer
Dawar, March 11, 2003 - 10:52 am UTC
I like to add version number with time stamp or just version number. we are implementing oracle application globally. Request has come to display version number on it.
Regards,
Dawar
Hollywood, CA
Oracle Application Developer
Dawar, March 11, 2003 - 12:24 pm UTC
appears to return the OS timezone value,is it correct?
Regards,
Dawar
Hollywood, USA
March 11, 2003 - 12:34 pm UTC
it returns the tz of the server.
Student
Dave, March 11, 2003 - 9:15 pm UTC
I like to write log procedures such as
procedure z_log
begin
null;
/* this log file shall track all changes to the form
Version Date Who Description */
end;
could you please help me out to complete above procedure.
Background (Probably will help you to understand what I am doing? )
I also have this procedure in the template form.
On form level I wrote hard code in the pre form. I can get version number and time but not the name. code is as follows:
FND_STANDARD.FORM_INFO('1.0.0' -- version number
, '<--actual form name>'
, 'LOFT'
,'96/08/06 16:20:52' , '<--user Name>');
Regards,
Dave
March 11, 2003 - 9:30 pm UTC
Dawar,
not getting it. do not understand the question.
student
Dave, March 11, 2003 - 11:33 pm UTC
Tom,
I like to write procedure to keep track on all changes in production environment.Assume they are more than one users. This procedure will dispaly following varaibles:
form_version, date , who(user) ,description.
Thanks. sorry for the last email.
Dave
Dave
March 12, 2003 - 7:47 am UTC
ok, so write said procedure? still not getting it.
student
Dave, March 12, 2003 - 3:33 pm UTC
Its done. Thanks for output.
Dave
Developer, April 30, 2004 - 9:52 am UTC
I need to get my current time zone and convert it to GMT time zone. How can I get the current time zone and use it to New_Time function?
Thank you for your advice!
April 30, 2004 - 10:43 am UTC
in 8i -- you cannot (does not exist)
in 9i, select dbtimezone from dual;
Works in 8i as well...
Kashif, April 30, 2004 - 12:41 pm UTC
Though I haven't checked if it is supported:
kkashif@DEV> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.2.1 Production
TNS for 32-bit Windows: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
kkashif@DEV> select dbtimezone from dual;
DBTIMEZ
-------
-04:00
kkashif@DEV>
April 30, 2004 - 5:41 pm UTC
not supported, many known and unknown "issues"
Tz name instead abrev.
Houman, October 17, 2005 - 4:01 pm UTC
hi,
after running get_timezone procdure that you explained above, I get the abreviation of the time zone. Is it possible to change the Java code in order to get the timezone name and not abrev? for example to return
'Canada/Atlantic' instead of ADT?
houman@devc:SQL>ALTER SESSION SET TIME_ZONE ='Canada/Atlantic';
Session altered.
houman@devc:SQL> declare
2 v varchar2(30);
3 begin
4 get_timezone(v);
5 dbms_output.put_line(v)
6 end;
7 /
ADT
Canada/Atlantic <-- I want this to be returnes
October 17, 2005 - 9:06 pm UTC
what db version - the database does this natively in 9i and above now.
else, break out java doc ;)
houman, October 18, 2005 - 9:28 am UTC
The database is 10g but we can't have the new data types
TIMSTAMP WITH .... before almost rewriting the application.
Nobody around here knows Java. We just need a function like sessiontimezone that returns us the session timezone name.
sessiontimezone can return abriv or offset.
October 18, 2005 - 9:35 am UTC
if your clients have a sessiontimezone set that is the 3 character abbreviation, just query v$timezone_names
if they have an offset from UTC, then the timezone they are in is "ambigous"
did not say you'd have to use the timestamp type - just that 9i and above have this information natively. You don't need a java stored procedure.
Houman, October 18, 2005 - 4:28 pm UTC
Ok, How am I going to know find my
client's timezone when sessiontimezone returns 'EST' considering the following query?
houman@devc:SQL> l
1 select tzname from v$timezone_names
2* where TZABBREV='EST'
LEGO@devc:SQL> /
America/Cambridge_Bay
America/Cancun
America/Cayman
America/Chicago
America/Detroit
America/Fort_Wayne
America/Grand_Turk
America/Indiana/Indianapolis
America/Indiana/Knox
America/Indiana/Marengo
America/Indiana/Vevay
America/Indianapolis
America/Iqaluit
America/Jamaica
America/Knox_IN
America/Louisville
America/Managua
America/Montreal
America/New_York
America/Panama
America/Rankin_Inlet
America/Thunder_Bay
Australia/ACT
Australia/Brisbane
Australia/Broken_Hill
Australia/Canberra
Australia/Hobart
Australia/LHI
Australia/Lindeman
Australia/Lord_Howe
Australia/Melbourne
Australia/NSW
Australia/Queensland
Australia/Sydney
Australia/Tasmania
Australia/Victoria
Australia/Yancowinna
Canada/Eastern
CST
CST6CDT
EST
EST5EDT
Jamaica
US/Central
US/East-Indiana
US/Eastern
US/Indiana-Starke
US/Michigan
48 rows selected.
The TZABBREV column is almost worthless in this regard.
Please help if you can
October 18, 2005 - 5:32 pm UTC
You tell me - which one do you want - they are all legitimate
they are not WORTHLESS, they are "the same", they all have the same abbreviation (i didn't make them up)
perhaps you need to build your own version of what you want - this is just what timezones are....
How find the database timezone (oracle 9.2.0.5)
A reader, September 25, 2006 - 8:48 pm UTC
Tom,
I do not have access to database server. How do i find the database timezone from remote sqlplus session.
I tried this from my local machine :
date
Tue Sep 26 00:18:10 UTC 2006
sqlplus <-- connect to sqlplus
SQL*Plus: Release 9.2.0.6.0 - Production on Tue Sep 26 00:18:14 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL> set lines 200 pages 200
SQL> select current_timestamp, systimestamp from dual;
CURRENT_TIMESTAMP SYSTIMESTAMP
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
26-SEP-06 12.18.35.936785 AM +00:00 25-SEP-06 08.18.35.936572 PM -04:00
SQL> select to_char(sysdate,'mm/dd/yy hh24:mi:ss') from dual
2 /
TO_CHAR(SYSDATE,'
-----------------
09/25/06 20:36:13 <-- this time matches EST
SQL> -- also tried creating a table and populate the date in it
SQL> create table test(testdate date) ;
Table created.
SQL> insert into test values(sysdate) ;
1 row created.
SQL> select to_char(testdate,'mm/dd/yy hh24:mi:ss') from test ;
TO_CHAR(TESTDATE,
-----------------
09/25/06 20:20:40
SQL>
I am not quite sure what my db timezone is. How to confirm this.
2) From the output below :
sessiontimezone returns -04:00
dbtimezone returms -04:00
How to interpret the actual timezone from this ? GMT/EST ?
1* select sessiontimezone,dbtimezone from dual
SQL> /
SESSIONTIMEZONE DBTIME
--------------------------------------------------------------------------- ------
-04:00 -04:00
September 26, 2006 - 2:29 am UTC
you cannot interpret it as "est" or anything like that all -4:00 means is you are four hours off of UTC.
It may or may not be a timezone that does daylight savings or not.
The dbtimezone in this case is right now simply "4 hours off UTC"
Thanks Tom
A reader, September 26, 2006 - 1:45 pm UTC
Why my time is not correct?
Umesh Kasturi, December 15, 2006 - 9:00 am UTC
Tom,
I have a java program which accepts a date and converts from one timezone to another -- I am writing this to take daylight savings also into consideration
I am not getting the correct o/p please HELP
create or replace and compile JAVA SOURCE named ABCD
as
import java.sql.Time;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.sql.Date;
import java.util.TimeZone;
public class ABCD{
/*
* Converts time from sourceTZ TimeZone to destTZ TimeZone.
* @return converted time, or the original time,
* in case the datetime could not be parsed
**/
public static String convTimeZone(java.sql.Date date, String sourceTZ, String destTZ)
{
final String DATE_TIME_FORMAT = "yyyy-MM-dd-HH:mm:ss";
// Suppose this is a date and time in the EST timezoneString value = "2006-11-28 09:45:12";
SimpleDateFormat srcTZdf = new SimpleDateFormat(DATE_TIME_FORMAT);
if (sourceTZ != null)
srcTZdf.setTimeZone(TimeZone.getTimeZone(sourceTZ));
else
srcTZdf.setTimeZone(TimeZone.getDefault());
java.util.Date sourceDate =null;
try {
sourceDate =srcTZdf.parse(srcTZdf.format(date));
} catch (ParseException e) {
e.printStackTrace();
}
SimpleDateFormat destTZDF = new SimpleDateFormat(DATE_TIME_FORMAT);
if (destTZ != null)
destTZDF.setTimeZone(TimeZone.getTimeZone(destTZ));
else
destTZDF.setTimeZone(TimeZone.getDefault());
destTZDF.setTimeZone(TimeZone.getTimeZone(destTZ));
System.out.println(destTZDF.format(sourceDate));
return destTZDF.format(sourceDate);
}
}
/
create or replace function Get_ConvertTimeZone (p_date date ,p_srctimezone varchar2, p_destimezone varchar2) return Varchar2 as
language java
name 'ABCD.convTimeZone(java.sql.Date, java.lang.String ,java.lang.String ) return java.lang.String';
/
Function created.
SQL> select Get_ConvertTimeZone(sysdate,'IST','PST') from dual;
GET_CONVERTTIMEZONE(SYSDATE,'IST','PST')
--------------------------------------------------------------------------------
2006-12-14-10:30:00
December 15, 2006 - 11:40 am UTC
well, seeing as how the database sort of does this with timestamps, I'm at a loss why you want to do it in such a hard fashion?
Time Zone related time
Umesh Kasturi, December 18, 2006 - 1:51 am UTC
My requirement is given a date + time I want that to be converted to a different timezone inclusive of daylightsaving
Please let me know how can I handle that with a timestamp
December 18, 2006 - 8:20 am UTC
Timezone differences between online and job sessions
Andy Hardy, September 20, 2007 - 4:54 am UTC
I'm running 8.1.7.4 database on a Windows server and need to determine timezone offset of the server (sending emails, need to fudge the 'send' date so that they recipient sees a correct date).
Using your Java snippet works fine from an online session, but when run through a job I get a different timezone returned i.e. from SQL*PLUS I get GMT+01:00 returned and from the job I get GMT+00:00 returned - I'm expecting the former as we're running in BST at the moment.
I also get the same results on my 10.2.0.3.0 database.
Is this expected behaviour? Can I alter this to get the consistent results I expect to see?
Re: Timezone differences between online and job sessions
Andy Hardy, September 20, 2007 - 10:52 am UTC
Further checking shows that the routine was also working on a different date when running as a job to online session changed the format parameter of the SimpleDateFormat call to test this).
However, the combination of date and timezone always produced a consistent time i.e. the online session would produce 13:00 +01:00 as the time and timezone, whilst the job would show 12:00 +00:00.
In my case, I chose a format string of "EEE', 'dd' 'MMM' 'yyyy' 'HH:mm:ss' 'Z" as I wanted a date to match the RFC(2)822 format for email send dates.
I'm not quite sure why this is happening, but I'm guessing that the online sessions are using client details to determine the values to use whilst the job was using the server OS level information without client 'display' issues?
September 24, 2007 - 7:22 am UTC
sorry, I don't know.
Getting session time_zone of an other session
Loïc LEFEVRE, July 01, 2008 - 7:30 am UTC
Hi Tom,
I wonder if we could retrieve the timezone of an other session.
Say I'm connected as SYS and I wish to know which time_zone an other session with SID XXX is using, can I do that with Oracle?
Regards,
Loïc
CST vs CST6CDT
soos, September 06, 2011 - 4:34 pm UTC
Tom,
My database is at Oracle 10.2.0.5. Today while working on timezone problem, I noticed the below issue:
SQL> select tz_offset('CST') from dual;
TZ_OFFS
-------
-05:00
SQL> select tz_offset('CST6CDT') from dual;
TZ_OFFS
-------
-05:00
My question is: How can both CST and CST6CDT have the same offset value?
Shouldn't the offset for CST = '06:00' ?
I observed similar behaviour with PST and PST8PDT.They both have offset values of 07:00 .
Please help me understand this.
Thank you.
September 07, 2011 - 8:38 am UTC
I'm not a timezone expert, but googleing around shows the same pretty much. Not all of CST is on DST.
CST vs CST6CDT
A reader, September 07, 2011 - 9:57 am UTC
Tom,
If CST has offset of -05:00 (like DST), how can I see the offset for CST ?
To my understanding, CST is for standard time for the areas that dont have daylight savings.
And CST6CDT for areas using daylight savings .
Am I wrong in this assumption?
Regards
CST vs CST6CDT
Vikki, September 16, 2011 - 9:45 am UTC
Tom,
I have a similar issue posted by earlier reader.
1.If both CST and CST6CDT have same time zone offset, then why two different timezone names?
2.What timezone name should I use for a region that uses daylight time?
Thank you.
September 16, 2011 - 2:15 pm UTC
1) ask the people that make timezone abbreviations up.
2) ditto :)
In short, I don't know which one you want to use - not really a database question :)