Dynamic Date and Time

LJ
LJ Member
in Beast Mode

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,

Capture.PNG

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    GrantSmith Coach
    Answer ✓

    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
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    LJ Member

    Thank You Michelle!

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories