Hour-over-Hour for the past 24 hours

datadiva
datadiva Member
in Magic ETL

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.

Best Answer

  • Valiant
    Valiant Coach
    Answer ✓

    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.

Answers

  • Valiant
    Valiant Coach
    Answer ✓

    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
    datadiva Member

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

  • Valiant
    Valiant Coach

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

  • Unknown
    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? 

Categories