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

MysteriousDomo
MysteriousDomo βšͺ️
edited July 9 in Dataflows

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

  • GrantSmith
    GrantSmith Indiana 🟀

    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)

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀

    @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.

  • MysteriousDomo
    MysteriousDomo βšͺ️

    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?

  • GrantSmith
    GrantSmith Indiana 🟀

    @MysteriousDomo

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

  • MysteriousDomo
    MysteriousDomo βšͺ️

    Unfortunately, nothing was returned.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀

    @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.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟀

    @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.

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀

    @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.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟀

    @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

  • MysteriousDomo
    MysteriousDomo βšͺ️

    Thanks @jaeW_at_Onyx

    That worked perfectly.

    πŸ˜€

Sign In or Register to comment.