Intervalmatch between two time functions or Timestamps

[Anonymous]

Hi,

I have the below dates and need to define shifts .  We have a dayshift and a nightshift.

Timestamp format for shifts:

dd.mm.yyyyhh:mm:ss

What I need to do is create a field that is called Shiftdate.

Shiftdate will be calculated by checking whether the hour of the timestamp > 00:00:00 (12 at night) and < 06:00:00 (6 in the morning)

and would then need to -1 from the timestamp (Day -1).

Meaning that all early morning work still forms part of the previous day's shift.

The reason I need to do this, is that past 12 at night the date changes to the next day, however we still see it as the same shiftdate as before 12 at night.

Is there a way I could use Interval match to resolve this issue?

Thanks in advance

[stabben23]

Hi,

try With something like this.

if(time(frac(timestamp(CreationDate,'hh:mm:ss')))>=time('06:00:00'),timestamp(CreationDate),timestamp(timestamp(CreationDate)-1)) as %DateKey,

[Anonymous]

Hi Staffan

My solution is the below.  however , i was just curious to see if interval match could be used.

IF(HOUR(CONFIRMED_AT)<'06' AND HOUR(CONFIRMED_AT)>'00',TRIM(DATE(INTERVAL(DATE(CONFIRMED_AT) - 1,'DD'),'M/D/YYYY')),TRIM(DATE(CONFIRMED_AT))) AS SHIFTDATE,

[stabben23]

ok Ruan,

I have never used intervallmatch in a case like this. Feels like a "overkill" solution to fix the problem.

I use my solution to step back one day when the skift is working to less then 06:00:00