Show daily data for last 30 days

  • What Grafana version and what operating system are you using?
    version 8.1.1, os-ubuntu 20.4 LTS

  • What are you trying to achieve?
    Hi, can anyone please let me know answers for this questions. I want to draw a graph. I need to show last 30 days statistics daywise and last 365 days statistics monthwise. how can I write the grafana code. this is my sample code and pictures. with this code I am not getting the correct answers as database shows. this is the code for last 365 days statistics monthwise.

  • How are you trying to achieve it?
    this is the code for last 365 days statistics monthwise.
    SELECT $__timeGroup(Date,‘1M’) AS’time’, SUM(patient_count) as ‘Patient Count’
    FROM hhims_patient
    where Hospital=7 and (Date < CURDATE() and Date >= DATE_SUB(CURDATE(),INTERVAL 365 DAY))
    GROUP BY time,Hospital

his is the code for last 30 days statistics daywise:
SELECT $__timeGroup(Date,‘1d’) AS’time’,SUM(OPD_Visit_count) as ‘OPD Visit Count’
FROM hhims_OPD_Visit
where Hospital=7 and (Date < CURDATE() and Date >= DATE_SUB(CURDATE(),INTERVAL 30 DAY))
GROUP BY time,Hospital

  • What happened?
    In last 30 days code, it gives the results of the previous date. For an example if I want to show the sum of today. under the date today, it gives last days’ totals.
    In last 365 days code, it gives incorrect sum of total visit count.

  • What did you expect to happen?
    I want to show the sum of visit count /day for last 30 days and sum of visit count/month for last 365 days.

  • Can you copy/paste the configuration(s) that you are having problems with?

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    image (Slack)

  • Did you follow any online instructions? If so, what is the URL?

I have someething similar to show monthly power usage:
Use:

  • Time Shift: 1d/y
  • Query: group by time(4w)

@ castillo92
Thank you so much for your quick response. I will try this and let you know.

1 Like