Dynamic Date and Time

LJ

Hello All,

I'm working currently on a report that would need data for just a specific date and time. Would you give any advice on how to create a beastmode script that will show the time between 6am on the previous day to 6am on the current day. Here is what my column looks like,

MichelleH

@LJ You can use a beast mode like this to filter to the times you want:

case 
  when DATE(`Alarm Received Store Time`) = CURRENT_DATE() - 1 and HOUR(`Alarm Received Store Time`) >= 6 
    then 'Include'
  when DATE(`Alarm Received Store Time`) = CURRENT_DATE() and HOUR(`Alarm Received Store Time`) < 6 
    then 'Include'
  else 'Exclude'
end

GrantSmith

Alternatively you can simplify this with something like the following:

case 
  when DATE(`Alarm Received Store Time` - INTERVAL 6 HOUR) = CURRENT_DATE() - 1
    then 'Include'
  else 'Exclude'
end

MichelleH

@LJ You can use a beast mode like this to filter to the times you want:

case 
  when DATE(`Alarm Received Store Time`) = CURRENT_DATE() - 1 and HOUR(`Alarm Received Store Time`) >= 6 
    then 'Include'
  when DATE(`Alarm Received Store Time`) = CURRENT_DATE() and HOUR(`Alarm Received Store Time`) < 6 
    then 'Include'
  else 'Exclude'
end

LJ

Thank You Michelle!

GrantSmith

Alternatively you can simplify this with something like the following:

case 
  when DATE(`Alarm Received Store Time` - INTERVAL 6 HOUR) = CURRENT_DATE() - 1
    then 'Include'
  else 'Exclude'
end