Tuesday, January 11, 2011

Select X Days Back

As you all know I have been writing and rewriting scripts for my 6.x servers and have been experiencing interesting results when I use the timestampdiff option when determining how far back a script should look. So when running my summary report query I was getting inaccurate results when using the following script:

example 1:
select schedule_name, entity, start_time, end_time, timestampdiff(16, char(end_time-start_time)) || ' ' || timestampdiff(8, char(end_time-start_time)) || ':' || timestampdiff(8, char(end_time-start_time)) || ':' || substr(cast((end_time-start_time) as char(22)),13,2) AS ELAPSED_TIME, affected from summary where activity='STGPOOL BACKUP' and timestampdiff(8, char(current_timestamp-start_time))<=24


This script would not return all the STGPOOL BACKUP jobs from the summary table. Not sure why, but I have a couple ideas. So, I found that the following is easier to use and returned the correct results.


example 2:
select schedule_name, entity, start_time, end_time, timestampdiff(16, char(end_time-start_time)) || ' ' || timestampdiff(8, char(end_time-start_time)) || ':' || timestampdiff(8, char(end_time-start_time)) || ':' || substr(cast((end_time-start_time) as char(22)),13,2) AS ELAPSED_TIME, affected from summary where activity='STGPOOL BACKUP' and start_time>(CURRENT TIMESTAMP - 10 DAYS)


So whenever you would like to query X days back I suggest example 2 for ease of use.