How can I get records from yesterday's 7am to today's 7am

MysteriousDomo
edited July 2021 in Magic ETL

Hi

Can someone provide guidance on how I am able to extract records from a dataset based on a specific time set?

I need to schedule a daily report that will show all records from 7 am Yesterday until Today 7 am and hope to use the 'TimeStamp' column to extract this information.

Thanks

Answers

  • You can use a beast mode to calculate the times and filter on results which return values within that result:


    CASE WHEN `Timestamp` BETWEEN CURDATE() - INTERVAL (24 - 7) HOUR AND CURDATE() + INTERVAL 7 HOUR THEN 'Include' ELSE 'Exclude' END
    

    CURDATE defaults to midnight that day so we're just subtracting a certain number of hours from midnight (or adding for today). 24-7 gets you to 7AM yesterday (17 hours prior to midnight today)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • @GrantSmith and @MysteriousDomo , just be careful with timestamp conversion.


    date functions in Analyzer may or may not calculate current_date based on UTC (as opposed to whatever timezone you're in. do confirm that your assumption about what Interval (24-7) returns is correct.


    also i don't believe you can use the BETWEEN clause in Beast Modes so you'd have to break it up into two inequalities.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Thanks All


    I did try the above however this did not work. I then used BEAST mode to separate the timestamp into date and time to see if I was able to extract and filter from here but no luck.

    Would there be anything in ETL that I can do to help extract the data for this time portion?

  • @MysteriousDomo

    How did it now work? Did it return something you weren't expecting? Not return anything at all?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Unfortunately, nothing was returned.

  • @MysteriousDomo to troubleshoot, break down your beast mode into it's component parts in a table for a handful of records.

    if this is your beast mode,

    CASE WHEN `Timestamp` BETWEEN CURDATE() - INTERVAL (24 - 7) HOUR AND CURDATE() + INTERVAL 7 HOUR
    
    CURDATE() - INTERVAL (24 - 7) HOUR
    and
    CURDATE() + INTERVAL 7 HOUR
    

    Make sure the results are what you expect them to be.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • @MysteriousDomo try this:

    CASE WHEN `dt` >= SUBTIME(CURRENT_DATE(),7*60*60) AND `dt` <= ADDTIME(CURRENT_DATE(),7*60*60) THEN 'Include'

    ELSE 'Exclude'

    END

    SUBTIME and ADDTIME work in seconds so I am multiplying 60 twice and then by 7 to do 7 hours (I find that more readable than entering in the final number)

    This avoids the BETWEEN clause that can be problematic and leverages current_date() which defaults to midnight as stated earlier.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • @MarkSnodgrass

    Both CurrentDate and CurrentTime will default to midnight if you change their Type to DateTime. Think about it. If DateTime and Date are the number of seconds since Epoch, then Date would just be the number of seconds since the day started. It's the same in excell when you show a column that was previously as just Date formatted as DateTime it appears as midnight.

    Because you're using the same root functions (CURRENT_DATE or CURRENT_TIME) the root cause of the user's problem will be the same or at least similar, unless people's math is straight up wrong, OR as in this case, the user is trying to use BETWEEN in a beast mode which as aforementioned is not supported.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • @jaeW_at_Onyx I did notice an error in my statement. I should be subtracting 17 hours to get to 7am of the previous day and not 7 hours. If today's date is 7/13/2021, then SUBTIME will yield 7/12/2021 07:00:00 and ADDTIME will yield 7/13/2021 07:00:00. This would do the trick in my opinion.

    CASE WHEN `timestamp` >= SUBTIME(CURRENT_DATE(),17*60*60) AND `timestamp` <= ADDTIME(CURRENT_DATE(),7*60*60) THEN 'Include'

    ELSE 'Exclude'

    END

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Thanks @jaeW_at_Onyx

    That worked perfectly.

    😀