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

MysteriousDomo

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

GrantSmith

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

@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

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

@MysteriousDomo

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

MysteriousDomo

Unfortunately, nothing was returned.

jaeW_at_Onyx

@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

@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

@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

@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

Thanks @jaeW_at_Onyx

That worked perfectly.

😀