Dynamic Date and Time

LJ · 2023-06-16T15:02:08+00:00

There was an error rendering this rich post.

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,

Find more posts tagged with

Accepted answers

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

All comments

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

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