Check if a time is in the interval

[Just6]

Hello all,

I would like to check whether a time is between 5.30 and 6.30. How can I do this? This should be checked for each day. The time is preceded by the day, can I solve this with Time()?
Thank you! 

[TauseefKhan]

Hi @Just6,

To check if a time is within a specific interval:

// Load initial data
TimeTable:
LOAD *,
Time#(MyTime, 'DD/MM/YYYY hh.mm') as ParsedTime
FROM [YourDataSource];

// Create an interval table that defines your time range for each day
IntervalTable:
LOAD * INLINE [
StartTime, EndTime
05:30, 06:30
];

// Use IntervalMatch to link your time field with the interval defined by StartTime and EndTime
IntervalMatch (ParsedTime)
LOAD StartTime, EndTime
RESIDENT IntervalTable;

// Now you can use the matched intervals in your expressions to check if times fall within the range

// Check if time falls within the interval
LOAD *,
IF(
Time(Timestamp#(TimestampField, 'DD.MM.YYYY hh:mm')) >= Time(MakeTime(5, 30))
AND
Time(Timestamp#(TimestampField, 'DD.MM.YYYY hh:mm')) <= Time(MakeTime(6, 30)),
'Within Interval',
'Outside Interval'
) AS TimeInIntervalFlag
RESIDENT YourDataSource;

***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***

[maxgro]

// some test data, random dates in 2024 and random times

D1:

LOAD

timestamp(makedate(2024) + floor(rand()*365) + rand()*24) as ts // date and time

AutoGenerate 1000;

// flag if the time of ts >= 5:30 and <= 6:30

D2:

LOAD

ts, // date and time

time(frac(ts)) as tm, // time of ts (date and time)

date(floor(ts)) as d, // date of ts (date and time)

if(frac(ts) >= Time#('5:30', 'h:mm') and frac(ts) <= Time#('6:30', 'h:mm'), 1, 0) as flag_in_530_630

Resident D1;

drop table D1;