Filter based on the weekday and time

Database
Enthusiast

Filter based on the weekday and time

Hi,

 

Have a table which as weekday and time in one column and i want to filter based on the same .

 

Table DDL

CREATE MULTISET TABLE LD_QUERIES 

     (

      APP_NAME VARCHAR(30) ,

      TABLE_NAME VARCHAR(30) ,

      START_DT VARCHAR(100) ,

      END_DT VARCHAR(100) 

      )

PRIMARY INDEX  ( APP_NAME ,TABLE_NAME );

DATA:-

APP_NAME TABLE_NAME START_DT END_DT

App1Table1sat-19:00sat-23:00
App1Table2fri-06:00fri-08:00

 

   SEL * from LD_QUERIES

   WHERE 'fri-01:01' not between START_DT and END_DT;

APP_NAME TABLE_NAME START_DT END_DT

App1Table1sat-19:00sat-23:00
App1Table2fri-06:00fri-08:00

   SEL * from LD_QUERIES

   WHERE 'fri-07:01' not between START_DT and END_DT;

APP_NAME TABLE_NAME START_DT END_DT

App1Table1sat-19:00sat-23:00

   SEL * from LD_QUERIES

   WHERE 'sat-21:01' not between START_DT and END_DT;

APP_NAME TABLE_NAME START_DT END_DT

App1Table2fri-06:00fri-08:00

 

Will the above queires work for all the dates? , for now its results are correct but wanted to make sure it will work for all the combination of weekday and time 

 

Also please help me know how to check between condition if i have data as below

DATA:-

APP_NAME TABLE_NAME START_DT END_DT

App1Table1sat-19:00#sun-09:00#wed-01:00sat-23:00#sun-11:00#wed-08:00
App1Table2fri-06:00#sat-01:30fri-08:00#sat-03:30

 

Thanks

Muzammil 

3 REPLIES
Senior Supporter

Re: Filter based on the weekday and time

There might be a bit of misconception on your START_DT and END_DT columns.

They are defined as varchar values. Varchars can contain dates but values like sat-19:00 are no dates all.

 

between on chars work on the order of characters and will be different for weekday aprivations like mon, tue, wed, thu, fri, sat, sun...

if your start period and end period will always end at sun 23:59 latest you could use a SQL UDF to convert the string into a numeric value which could than filtered with between conditions.

but if your periods could span from the end of a week to the beginning of a week like 'sat-17:00' - 'wed-19:00' this solution would not work.

Might still be possible but you need to have a more complicated udf which is considering at least the start and the end period value.

Enthusiast

Re: Filter based on the weekday and time

Thanks ulrich for reply

 

 

My time may expand over to different week ( Ex: Sat-09:00 to Mon-10:00) . Any suggestion how can we do this 

 

Thanks

Muzammil 

 

Senior Supporter

Re: Filter based on the weekday and time

the below code uses two sql udf's

 

the frist one cacluates the minutes since mon-00:00 as a week start ( yes, in europe the week starts on monday :-))

the second one checks if a ref time in between the period or not ( the trick is to add 10080 minutes if the start is <= the end...

 

have fun!

 

CREATE MULTISET TABLE LD_QUERIES 

     (

      APP_NAME VARCHAR(30) ,

      TABLE_NAME VARCHAR(30) ,

      START_DT VARCHAR(100) ,

      END_DT VARCHAR(100) 

      )

PRIMARY INDEX  ( APP_NAME ,TABLE_NAME );

delete from LD_QUERIES;

insert into LD_QUERIES values ('App1','Table1','sat-19:00','sat-23:00');
insert into LD_QUERIES values ('App1','Table2','fri-06:00','mon-08:00');
insert into LD_QUERIES values ('App1','Table2','mon-06:00','mon-04:00');


select * from LD_QUERIES;

REPLACE FUNCTION "MinutesSinceMondayNight" (
given_time VARCHAR(100)
)
RETURNS integer
SPECIFIC "MinutesSinceMondayNight"
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER INLINE TYPE 1
RETURN substr(given_time,8,2) + 
       substr(given_time,5,2)*60 + 
       case substr(given_time,1,3) 
          when 'mon' then 0
          when 'tue' then 1
          when 'wed' then 2
          when 'thu' then 3
          when 'fri' then 4
          when 'sat' then 5
          when 'sun' then 6
        end * 1440;

select MinutesSinceMondayNight(null);
select MinutesSinceMondayNight('mon-00:00');
select MinutesSinceMondayNight('sun-23:59');
select MinutesSinceMondayNight('tue-00:00');
select MinutesSinceMondayNight('wed-10:00');


REPLACE FUNCTION "compareToPeriod" (
given_time VARCHAR(100),
period_start varchar(100),
period_end varchar(100) 
)
RETURNS byteint
SPECIFIC "compareToPeriod"
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER INLINE TYPE 1
RETURN case when 
         case when MinutesSinceMondayNight(period_start) >= MinutesSinceMondayNight(given_time) then MinutesSinceMondayNight(given_time) + 100080 else MinutesSinceMondayNight(given_time) end  -- calcualted given time
         between 
         MinutesSinceMondayNight(period_start)
         and 
         case when MinutesSinceMondayNight(period_start) >= MinutesSinceMondayNight(period_end) then MinutesSinceMondayNight(period_end) + 100080 else MinutesSinceMondayNight(period_end) end -- calcualted end time
       then 1 else 0 end
       ;
       
select compareToPeriod(null,'mon-00:00','tue-00:00');
select compareToPeriod('mon-00:00',null,'tue-00:00');
select compareToPeriod('mon-00:00','tue-00:00',null);
select compareToPeriod('mon-02:00','mon-08:00','mon-04:00');
select compareToPeriod('mon-06:00','mon-08:00','mon-04:00');
select compareToPeriod('mon-02:00','fri-08:00','mon-04:00');
select compareToPeriod('mon-06:00','fri-08:00','mon-04:00');
select compareToPeriod('sun-06:00','fri-08:00','mon-04:00');
select compareToPeriod('thu-06:00','fri-08:00','mon-04:00');

select *
from LD_QUERIES
where compareToPeriod('thu-06:00',START_DT,end_dt) = 1
;
select *
from LD_QUERIES
where compareToPeriod('mon-06:00',START_DT,end_dt) = 1
;
select *
from LD_QUERIES
where compareToPeriod('mon-04:00',START_DT,end_dt) = 1
;
select *
from LD_QUERIES
where compareToPeriod('mon-05:00',START_DT,end_dt) = 1
;
select *
from LD_QUERIES
where compareToPeriod('mon-05:00',START_DT,end_dt) = 0
;