Hour-over-Hour for the past 24 hours

Hi,

I have been trying to figure out how to graph performance of applications for each hour in a rolling 24-hour period (and once I get that then I need to do it hourly for a rolling 30 days). My database behind the scenes is a MySQL database where I added a calculated column so that I would have just the date and the hour of the timestamp (we use military time). This is what produces that output:

SELECT IF(HOUR(timestamp) < '10', (CONCAT_WS(":", DATE(timestamp), CONCAT('0', HOUR(timestamp)))),
(CONCAT_WS(":", DATE(timestamp), HOUR(timestamp))))
FROM tablename;

I can graph this and it makes for nice aggregation on this field, but I need to be able to limit it to just the past 24 hours, and there is no simple drop-down option for this like there is for the current day, so I'm trying to calculate this using Beastmode. Right now, I am trying to set 1's and 0's for quick easy filtering using this:

CASE WHEN ADDATE(CURRENT_TIMESTAMP(),-1) < `DeviceLocalTime` THEN 1 ELSE 0

But I keep getting a syntax error, and I'm not sure why as Beastmode doesn't really tell you where you have an error and clearly, it's not exactly the SQL I know. Has anyone else figured this out? Please, help.

Find more posts tagged with

Accepted answers

BlueRooster

Looks like you might be missing a D from ADDDATE and the END of the CASE.

Try this: CASE WHEN ADDDATE(CURRENT_TIMESTAMP(),-1) < `DeviceLocalTime` THEN 1 ELSE 0 END

If that's not it, let me know and I'll take another look.

Sincerely,

ValiantSpur

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

All comments

BlueRooster

Looks like you might be missing a D from ADDDATE and the END of the CASE.

Try this: CASE WHEN ADDDATE(CURRENT_TIMESTAMP(),-1) < `DeviceLocalTime` THEN 1 ELSE 0 END

If that's not it, let me know and I'll take another look.

Sincerely,

ValiantSpur

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

datadiva

Oh my gosh! One little 'D' is what was messing me up. Thanks a million. It works now.

BlueRooster

No worries! I've done it many times myself. Always good to have a second pair of eyes ?

unknown

Valiant- Have you figured out a way to filter data for a YoY chart based on the Max hour for the current day? For example, if I only have data through 9 am today then I want a period over period (today vs. same fiscal day last year) card with data only through the 9 am hour for last year as well. Make sense?