Trickey Date Time SQL - There has to be a better way ?! 1

[Exie]

Hi,

I'm writing a program which needs to query a table, and determine if the current date/time falls inside a blackout period.

In my table, I created 2 columns as Date/Time stamps to hold my starting/ending values.

The catch is, I dont want these values to be a fixed point in time. I want them to be rolling each week. For example, I might want my blackout period to be from Wed@01:00 to Thur@13:00 ... so I scribbled the following messy SQL code:

SELECT 1
FROM dwh.ms_support_control 
WHERE sysdate BETWEEN TO_DATE(TO_CHAR(NEXT_DAY(sysdate,TO_CHAR(inactivity_1_start_dttm,'DAY')),'DD-MM-YYYY') || ' ' || TO_CHAR(inactivity_1_start_dttm,'HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS') 
                  AND TO_DATE(TO_CHAR(NEXT_DAY(sysdate,TO_CHAR(inactivity_1_interval_dttm,'DAY')),'DD-MM-YYYY') || ' ' || TO_CHAR(inactivity_1_interval_dttm,'HH24:MI:SS'),'DD-MM-YYYY HH24:MI:SS')

The catch is, If my program run on the given blackout day eg. Friday, the NEXT_DAY will return friday the following week!

Surely theres an easier way!

I think de-constructing and re-constructing dates will almost always be problematic. All I want to know is if I'm in a blackout or not.

Any hints/tips welcome.

 

[SantaMufasa]

Exie,

If "inactivity_1_start_dttm" and "inactivity_1_interval_dttm" are of data type "DATE" (as they appear to be), what is wrong with just saying?:

SELECT 1
FROM dwh.ms_support_control
WHERE sysdate between inactivity_1_start_dttm and inactivity_1_interval_dttm

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 01:13 (14Jan05) UTC (aka "GMT" and "Zulu"),
@ 18:13 (13Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[Exie]

Hi,

Good question! The key is, they refer to a specific day. Eg. 01/01/2005

If I wanted to blackout this weekend I could specify
inactivity_1_start_dttm
15/01/2005 00:00:00

inactivity_1_interval_dttm
17/01/2005 00:00:00

And that would work fine, but I want every weekend to be a blackout... or every Tuesday .... My table holds different rows for different people. So each member of our team has an entry, along with one blackout period (where they shouldnt be contacted).

I guess I could create another table and pre-populate it with every date for every staff member, but thats a bit of a hassle when someone changes their blackout times.

My table actually looks like this:

create table ms_support_control (
contact_id    varchar2(7),
contact_nm    varchar2(30),
email_tx    varchar2(40),
phone_nb    varchar2(12)
inactivity_1_interval_tm    date,
inactivity_1_start_tm    date,
inactivity_1_type_cd    varchar2(5),
inactivity_2_interval_tm    date,
inactivity_2_start_tm    date,
inactivity_2_type_cd    varchar2(5),
inactivity_3_interval_tm    date,
inactivity_3_start_tm    date,
inactivity_3_type_cd    varchar2(5),
)

As you can see, the folks in my team have actually asked for 3 possible blackout periods.

I'm open to any recomendations, if you folks think it's better to prepopulate another table or something then I'm happy to consider it.

 

[rtefft]

Exie,

I built a function that seems to work as you dewscribed:

--  DURING_BLACKOUT
--
--  this function returns a varchar TRUE or FALSE if the specified I_CHECK_DATE falls between
--  the I_BLACK_START_DATE and I_BLACK_END_DATE periods for the current week.  The blsckout
--  dates are used only to determine what day/hours of the current week are in the blackout
--  period, so they are "adjusted" to the current week.
--
create or replace function DURING_BLACKOUT (
         i_black_start_date in date,      -- start date of blackout period
         i_black_end_date   in date,      -- end date of blackout period
         i_check_date       in date)      -- date to check
         return VARCHAR2 as
  --
  black_start_day   varchar2(3);      -- Day of week blackout starts (1-7)
  black_start_time  number;           -- Time blackout starts (time since 00:00)
  black_duration    number(8,6);      -- Duration of blackout (end - start)
  prev_black_start  date;             -- Start date of prev blackout period
  prev_black_end    date;             -- End date of previous blackout period
  next_black_start  date;             -- Start date of next blackout period
  next_black_end    date;             -- end date of next blackout period
begin
  if (i_black_start_date is null) or (i_black_end_date is null) or (i_check_date is null) then
    return null;
  end if;
  --
  -- For the blackout start, determine day of the week (1-7) and time since midnight.
  -- Also calculate blackout duration.
  --
  black_start_day := to_char(i_black_start_date,'DY');   -- Get day of week for black start
  black_start_time := i_black_start_date - trunc(i_black_start_date);
  black_duration := i_black_end_date - i_black_start_date;
  --  
  --  Because blackout periods can wrap across weeks, determine the previous and next
  --  blackout periods.
  --
  prev_black_start := trunc(next_day(sysdate-7,black_start_day))+black_start_time;
  prev_black_end := prev_black_start + black_duration;
  --
  next_black_start := trunc(next_day(sysdate,black_start_day))+black_start_time;
  next_black_end := next_black_start + black_duration;
  --
  --  If the specified date is within the previous or next blackout periods, say so.
  --
  if (i_check_date between prev_black_start and prev_black_end) or 
     (i_check_date between next_black_start and next_black_end) then
     return 'TRUE';
  else
    return 'FALSE';
  end if;
end DURING_BLACKOUT;

It will return a VARCHAR2 'TRUE' or 'FALSE' instead of boolean however. If you use it, you should add NULL checks as well.

This is an overly verbose solution I am sure, but sometimes I still think like an old 3-GL FORTRAN programmer. Hope this helps.

____________________________
Rich Tefft
PL/SQL Programmer

 

[Exie]

WOW Thanks Rich! That works a treat!

I'm looking forward to learning som PL/SQL in my training, but I've only done SQL so far.

If you were in Melbourne, Australia, I'd buy you a beer!

 

[rtefft]

Exie,

Glad it helps. One thing I forgot to mention: because it is a function, you can use it in SQL statements directly. If I understood your columns right, INACTIVITY_1_START_DTTM is the start of blackout period 1, and INACTIVITY_1_INTERVAL_DTTM is the end of the blackout period. If so, this SELECT will tell you if you are a blackout period:

select DURING_BLACKOUT(inactivity_1_start_dttm, inactivity_1_interval_dttm, sysdate) 
from ms_support_control
where...

You can also use it in a WHERE clause, but it will be slow if other parts of the WHERE clause cannot narrow down the number of rows. PL/SQL functions are great, but they do eat up some resources.

Thanks for the beer offer... if I ever got to Australia, I'd be so happy I would buy you a beer.

Best wishes,

____________________________
Rich Tefft
PL/SQL Programmer

 

[ddrillich]

As we know, Oracle 9i introduced new date/time datatypes, such as INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.
I wonder if these two datatypes and their associated functions can help here.

Regards,
Dan