Dynamic Date and Time

LJ · June 2023

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 · June 2023

@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 · June 2023

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 · June 2023

@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 · June 2023

Thank You Michelle!

GrantSmith · June 2023

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

Beast Mode

Beast Mode

Dynamic Date and Time

Welcome!

Best Answers

Answers

Welcome!

Welcome!

Quick Links

Categories