We need to access to tables from the BO repository. And query to the details of jobs submitted by Document Agent Server. And we need to read the dates store as numÈric to date in BO Reporter. We use Oracle.
Help
AndrÈguy DUBAR
+33(0)6.09.60.13.26
adubar@nordnet.fr
Listserv Archives (BOB member since 2002-06-25)
AndrÈguy DUBAR wrote…
And we need to read the dates store as numÈric to date in BO Reporter.
Here’s how to do the conversion:
This is Oracle syntax for converting the UTC dates to ‘real’ dates. create or replace
function BO_FU_REP_TRANS_DATE (F_INPUT_DATE_INT INTEGER) return date is
– input: BO repository date
– output: date
output_date_date date;
output_date_varchar varchar2(20);
BEGIN
output_date_varchar := to_char(to_date ‘15-12-1970’, ‘DD-MM-YYYY’) +
floor (f_input_date_int/86400), ‘DD-MM-YYYY’) || ’ ’ || lpad (floor(mod(f_input_date_int,86400)/3600,2,‘0’) ||‘:’ || lpad (floor(mod(mod(f_input_date_int,86400),3600) / 60), 2, ‘0’); output_date_date := to_date(output_date_varchar, ‘DD-MM-YYYY HH24:MI’); return (output_date_date);
EXCEPTION
when others return null;
end BO_FU_REP_TRANS_DATE;
Listserv Archives (BOB member since 2002-06-25)
In a message dated 98-08-18 11:04:27 EDT, you write:
AndrÈguy DUBAR wrote…
And we need to read the dates store as numÈric to date in BO Reporter.
Here’s how to do the conversion:
This is Oracle syntax for converting the UTC dates to ‘real’ dates. create or replace
function BO_FU_REP_TRANS_DATE (F_INPUT_DATE_INT INTEGER) return date
is
– input: BO repository date
– output: date
output_date_date date;
output_date_varchar varchar2(20);
BEGIN
output_date_varchar := to_char(to_date ‘15-12-1970’, ‘DD-MM-YYYY’) +
floor (f_input_date_int/86400), ‘DD-MM-YYYY’) || ’ ’ || lpad (floor(mod(f_input_date_int,86400)/3600,2,‘0’) ||‘:’ || lpad (floor(mod(mod(f_input_date_int,86400),3600) / 60), 2, ‘0’); output_date_date := to_date(output_date_varchar, ‘DD-MM-YYYY HH24:MI’); return (output_date_date);
EXCEPTION
when others return null;
end BO_FU_REP_TRANS_DATE;
How about just:
select to_char(to_date(‘15-DEC-70’)+(submit_datetime/(606024)),‘DD-MON-YY HH24:MI:SS’)
from ds_pending_job
The documentation for the repository says that dates are stored as date/time values in the form of the number of seconds since December 15, 1970. Since the number of seconds is a day is (606024), simply divide that number stored in the repository by the appropriate value and add to the date. Oracle automatically adds fractional days as hours, minutes, and seconds. If you just wanted the date, you could use:
select to_date(‘15-DEC-70’)+(submit_datetime/(606024)) from ds_pending_job
…instead.
Listserv Archives (BOB member since 2002-06-25)
In a message dated 98-08-18 11:04:27 EDT, you write:
Here’s how to do the conversion:
This is Oracle syntax for converting the UTC dates to ‘real’ dates. create or replace
function BO_FU_REP_TRANS_DATE (F_INPUT_DATE_INT INTEGER) return date
is
– input: BO repository date
– output: date
output_date_date date;
output_date_varchar varchar2(20);
BEGIN
output_date_varchar := to_char(to_date ‘15-12-1970’, ‘DD-MM-YYYY’) +
floor (f_input_date_int/86400), ‘DD-MM-YYYY’) || ’ ’ || lpad (floor(mod(f_input_date_int,86400)/3600,2,‘0’) ||‘:’ || lpad (floor(mod(mod(f_input_date_int,86400),3600) / 60), 2, ‘0’); output_date_date := to_date(output_date_varchar, ‘DD-MM-YYYY HH24:MI’); return (output_date_date);
EXCEPTION
when others return null;
end BO_FU_REP_TRANS_DATE;
I discovered something interesting when doing some further research into the dates stored in the doc agent tables. They seem to be stored in Greenwich Mean Time!
I should say that the information in this post is preliminary; I have not fully verified my work yet.
I was doing some testing against the doc agent tables. At a client they are having problems with the Scan / Repair feature identifying “invalid submission dates” in their repository. Investigation reveals that the submission dates are in the future by either six or seven hours! Neat trick, I say… submit a document to DAS at 9:00 in the morning, and using a date calculation similar to that provided above will show that the document was submitted at 3:00 (or 4:00) in the afternoon. Scan / Repair complains about these dates until 4:01 pm, at which time the dates are no longer “invalid”.
Now for more interesting information: reviewing the dates shown in the DAS console shows the proper 9:00 am submission time!
And - as Alice would say - curiouser and curiouser… change the Time Zone specified on your work station (can easily be done by double-clicking on the clock display in the task bar) and DAS reports a different submission time. I submitted a document at 3:00 pm, and the data in the DAS tables showed 10:00 pm (note the 7 hour difference). I then submitted a document on a different workstation at 3:00 pm and the data in DAS showed a submit date of 9:00 pm (note the 6 hour difference). What was different between the two systems? The 7 hour difference was generated on NT Workstation 4.0, while the 6 hour difference came from a Windows 95 workstation.
I suspect the difference between NT and 95 came from the way the Daylight Savings time was processed. However, I can’t substantiate this. It’s just a guess. But since this involves a one hour difference in reported time, it is probably a good guess.
(For anyone in an area of the world where this deception is not practiced, Daylight Savings time is a change in the reported time (by one hour) to allow more activity outside in the evening during the summer time when days are longer. In the Spring, clocks are set forward by one hour. In the fall they are set back. And to make matters more confusing, this is not done everywhere in the US… just most places. )
More interesting items: I changed the Time Zone on the NT workstation to Greenwich Mean Time (0 hours of adjustment). When I opened the DAS console, the time showed for submit date was 10:00 pm; the same time shown by my Oracle SQL code against the repository tables. Switching to different time zones changed the DAS console’s interpretation of the raw data in the repository.
All of this investigation was done with BusObj 4.1.2 or 4.1.3, so I am relatively current in that area. After all of this, I am wondering if perhaps the problem is that the DAS console (and perhaps other parts of the program) assume Greenwich Mean Time data in the repository, and adjust via time zones accordingly… but that the Scan / Repair feature does not. I have to assume that this is a new issue, as I have not heard anybody complain about this before. I would be interesting in hearing if others are having the same problem.
Here are a few steps to test to see if you get the same response:
Obviously, if you are in a time zone East of Greenwich, this should not ever happen. Your system clock should always be ahead of Greenwich Mean Time, and so your submission dates will be in the “past”. If you are West of this time zone, then you have a window of several hours to do this check.
I wonder, also, if there is something here that can help explain some of the other issues that people are having in their Y2K testing of the application.
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)