Write query to get current month total

[lochanie1987]

• What Grafana version and what operating system are you using?
  Grafana version 8.1.1

• What are you trying to achieve?
  I want to write a query similar to this for the current month. This query I have written to get totals of last 30 days. But How can I write a query to get totals of current month, for an example totals from 2021-09-01 to 2021-09-21, from 2021-09-01 to 2021-09-22 likewise.

select $__timeFilter(TX_Date) as “time”,sum(Accepted_Count) as “Total” from transactions
where dept_id=1 and (TX_Date < CURDATE() and TX_Date >= DATE_SUB(CURDATE(),INTERVAL 30 DAY))
group by dept_id;

• How are you trying to achieve it?
  I write the query in this manner and change the time range to “This Month” from the drop down in upper right corner.
  select $__timeFilter(TX_Date) as “time”,sum(Accepted_Count) as “Total” from transactions
  where dept_id=1
  group by dept_id;
  

• What happened?
  But when there is no value in database, it gives the totals for whole period.

• What did you expect to happen?
  If there is no value for particular metrics in Data base, show the value 0. I set the calculation option as this.
  

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

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

[laurielounge]

@lochanie1987

select $__timeFilter(TX_Date) as “time”,sum(Accepted_Count) as “Total” from transactions
where dept_id=1 and (TX_Date < CURDATE() and TX_Date >= DATE_SUB(CURDATE(),INTERVAL 30 DAY))
group by dept_id;

Depends on your database flavour really. This works for MySQL, for example:

SELECT $__timeFilter(TX_Date) AS “time”, SUM(Accepted_Count) AS “Total”
FROM transactions
WHERE dept_id = 1
AND TX_Date BETWEEN TX_Date - INTERVAL DAY(TX_Date) - 1 DAY TX_Date
GROUP BY dept_id;

This bit:
TX_Date - INTERVAL DAY(TX_Date) - 1 DAY
Calculates the date at the start of this month.

If you want to return a zero if there’s no data, you might try a coalesce, along the lines of
COALESCE(SUM(Accepted_Count),0) AS “Total”
What that does is, if SUM(Accepted_Count) is undefined (null), replace it with a zero.

[lochanie1987]

Hi Laurie,

Thank you so much for your solution. But its not worked for me. I put the coalesce option as well. but there is no change in the statistics. I need to get the total from 2021-09-01 to current date. Please help me to resolve this.

[laurielounge]

Can you give me a clue as to what database type you’re using? Postgres?MariaDB? MSSQL?

[lochanie1987]

I am using mysql database. I want to get the totals of “This month so far”.

[laurielounge]

What happens if you just run the query
SELECT SUM(Accepted_Count) AS “Total”
FROM transactions
WHERE dept_id = 1
AND TX_Date BETWEEN ‘2021-09-1’ AND ‘2021-09-24’
GROUP BY dept_id;

What about if you run

SELECT SUM(Accepted_Count) AS “Total”
FROM transactions
WHERE TX_Date BETWEEN ‘2021-09-1’ AND ‘2021-09-24’;

What about

SELECT *
FROM transactions
WHERE dept_id = 1
AND TX_Date BETWEEN ‘2021-09-1’ AND ‘2021-09-24’;

What about
SELECT *
FROM transactions
WHERE TX_Date BETWEEN ‘2021-09-1’ AND ‘2021-09-24’;

What about
SELECT *
FROM transactions

Which ones return records?

[lochanie1987]

Hi Laurie,
SELECT SUM(Accepted_Count) AS “Total”
FROM transactions
WHERE dept_id = 1
AND TX_Date BETWEEN ‘2021-09-1’ AND ‘2021-09-24’
GROUP BY dept_id;
This code gives me the results. But the problem is then in next day (2021-09-25) I need to show the sum between 2021-09-01 and ‘2021-09-25’ days. Then I need to manually change the code again. I try to find the way to automatically get the totals between current date and first day of the current month.

[laurielounge]

Okay. Then try this

SELECT SUM(Accepted_Count) AS “Total”
FROM transactions
WHERE dept_id = 1
AND TX_Date BETWEEN CURDATE() - INTERVAL DAY(CURDATE()) - 1 DAY AND CURDATE()
GROUP BY dept_id;

[lochanie1987]

@laurielounge

SELECT SUM(Accepted_Count) AS “Total”
FROM transactions
WHERE dept_id = 1
AND TX_Date BETWEEN CURDATE() - INTERVAL DAY(CURDATE()) - 1 DAY AND CURDATE()
GROUP BY dept_id;

Hi Laurie,
Thank You for your response. But I am getting db_query error:1064.once I typed this query.

[lochanie1987]

When I am changing the “Total” to another name in this line most of the time it gives the error 1064. "SELECT SUM(Accepted_Count) AS “Total”. What’s the reason for this?
And the other question is how I apply the query to other time ranges like

• This year so far
• This week so far
• Today so far
  and other time ranges in other quick ranges field.
  
  

[lochanie1987]

Hi laurielounge,

Thank you so much. Now your query works perfectly. I have put the alias with out quotes. Now its working. Thank You so much once again. Could you please let me know how can I adjust this to this year so far (like 2021-01-01 to 2021-09-26) and this week so far etc.
Thank you again.

[expoenergy]

The best way to do this is to use the GRAFANA inbuilt time variables so that whatever date or time period you select from the GRAFANA drop-down menu gets parsed to your query.

In your case, you will have a query that looks like

where (time >= startdate and time <= enddate) where you have hardcoded the start (2021-09-01) and end date ( 2021-09-21)

If you use the inbuilt GRAFANA variables you simply change this to

where (time>= ${_from:date} and time ${_to:date}

This will then use and parse the start and end date selected from the time/date dropdown menu in your query and will work for any period of time you wish to select from the dropdown instead of trying to hardcode the date/time for each query

see: Global variables | Grafana Labs

[lochanie1987]

Hi Expoenergy,

Thank you so much for your response. But if we change date range from the drop down menu, if there is empty records in the database for the selected period, dashboard will give incorrect results. But if we change the query it self, it will reflect correct statistics. That’s why I am trying to find a way to write correct query for this. I have sorted the issue in getting current month totals with the help of laurielounge. Now I am finding a way to get totals for the current year and current week. I would be grateful if you could help me.

[expoenergy]

Hi,

I cannot see the difference between hard coding the date and using the GRAFANA dropdown dates?

If data is missing in the database it should not be affected by the date range nor should any totals be affected either?

However, when using the GRAFANA drop-down dates and variables the format in which it parses time to the query can be affected but we can control that.

GRAFANA provides a $(_from) and $(_to) Global variables and one may use the colon: to set the desired time format, default is UNIX millisecs.

Also, hardcoding dates, removes any timezone errors and you need to check the actual time returned in your query as far as start and end and see if you perhaps have timezone offsets. A little nasty that GRAFANA overlooks and never documents and which is frustrating to the point of head banging!

Other than this there should be no difference and as you claim you are getting errors then the problems lies elsewhere.

I went through all this and found that it was timezone issues that created problems.

GRAFANA returns the date as 2020-07-13T20:19:09.254Z when using _to and _from.

To avoid this use $(_from:YYYY-MM-dd} the problem is using this format you cannot use
{_from:YYYY-MM-dd hh:mm:ss} as the [:] character is not allowed

So you have to format your query somewhat differently to avoid this and you have to do it piece by piece and concatenate the query text and then it works great.

startdate=${__from:date:YYYY-MM-DD} ${__from:date:HH}:${__from:date:mm}:${__from:date:ss}&enddate=${__to:date:YYYY-MM-DD} ${__from:date:HH}:${__from:date:mm}:${__from:date:ss}

So what this does is it takes or extracts the relevant date/time pieces from the GRAFANA Global variables and removes any time zone issues and I am sure this will help resolve your problem.

If you want, create a new dashboard, and a single panel with your existing quesry and give me edit rights and I will see what I can do?

[lochanie1987]

Hi expoenergy,

Thank you for your response. I have that experience which if there is no data in the database, it gives the totals from the very beginning date to last date which data has. But if we use the time range in the query it was not like that.

[expoenergy]

Exactly it means that using the Grafana Global variables you are parsing the incorrect time format back to your query.

It cannot be that hardcoding and using time variables yields a different result. It means the format of the timestamp being parsed back as a Global variable is incorrect and why you get a start from the beginning as I have explained.

I had similar problems.

Anyway good luck.

[lochanie1987]

Hi Expoenergy,
What I want to get is Total between ‘2021-01-01’ and ‘2021-09-29’ if the the is today. Total from the first date of this year and current date. I need to get the total for first date of the year to today, first date of the year to tomorrow, if the date changes to tomorrow’s date. if we are going to select it from drop down,

1. In the same dashboard I can’t put panels with different time ranges
2. If there is empty values for the query in the database, it will not reflect in correct manner.
   Because of these reasons I am writing a query to get totals.
   If you can please let me know, how can I write query to get totals from first date of this year to current date. I will give you the example of writing a query to get totals of first date of this month to current date.
   SELECT SUM(Accepted_Count) AS “Total”
   FROM transactions
   WHERE dept_id = 1
   AND TX_Date BETWEEN CURDATE() - INTERVAL DAY(CURDATE()) - 1 DAY AND CURDATE()
   GROUP BY dept_id;
   This is laurie’s answer for my question. It worked for me very well. Likewise now I need a query to get totals of first date of this year to current date. only need to change this part of the query
   “AND TX_Date BETWEEN CURDATE() - INTERVAL DAY(CURDATE()) - 1 DAY AND CURDATE()”
   That is my exact question and reason for asking the question. If there is any possibility please help me. Thanks in advanced.

[expoenergy]

As you know your requirements are not new and GRAFANA have created an array of Global variables to account for such needs, so you just need to find and understand how use them.

They are provided under Time range controls and operate independently of the drop-down and only rely on the current or today’s date.

In this way, one just has to use Today()-time period where time period could be 1 day, 1 week, 1 month or 1 year. Below are the inbuilt variables as shown below

So if you are using

“AND TX_Date BETWEEN CURDATE() - INTERVAL DAY(CURDATE()) - 1 DAY AND CURDATE()”

You can use

AND TX_Date >= now/d AND TX_DATE <= now This is for TODAY SO FAR

AND TX_DATE>= now/w AND TX_DATE <= now This is for THIS WEEK SO FAR

AND TX_DATE >= now/M AND TX_Date <= now This is for MONTH SO FAR

AND TX_DATE >= now/Y AND TX_date <= now This is for YEAR SO FAR

You can do the same using BETWEEN

If you then set your dashboard to automatically update then all your dashboards will update based on the refresh interval you have chosen.

The thing is using the inbuilt MYSQL datetime syntax always ensures the correct timestamp format.

So as you have found trying to use relative dates like week to date, month to date and year to date are tricky as one has to find the start day of the week, month, and year in question.

So you need

AND TX_Date BETWEEN " DATE OF START OF CURRENT WEEK" AND CURDATE()

So the problem is to find the date of start of week, month and year.

This may be done using the DATE-SUB() Function

DATE_SUB( date , INTERVAL value interval ))

which has a special trick that will return the start day of the period in question by parsing -1 DAY

START OF WEEK

DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 1 DAY)

START OF MONTH

DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY)

START OF YEAR

DATE_SUB(CURDATE(), INTERVAL DAYOFYEAR(CURDATE()) - 1 DAY)

So you could then use (for week to date)

AND TXDATE >= DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 1 DAY) and <= CURDATE()

or

BETWEEN DATE_SUB(CURDATE(), INTERVAL DAYOFWEEK(CURDATE()) - 1 DAY)) AND CURDATE()

Month and Year to date follow suite.

Now whether hardcoded in the query or whether using GRAFANA Global variables these should all work the only issue is the DateTime format that GRAFANA parses to mysql

Please note that even if hardcoded the week to date depends on whether your system assumes Sun or Monday as the start of the week so your PC or server environment has to be set up correctly to avoid the start of week and timezone offset issues.

These trivial matters can make you tear your hair out, even if you choose to use the simpler GRAFANA Global variables.

You know the old saying there is no such thing as a free lunch.

Hope this helps.

[lochanie1987]

Hi expoenergy,

Thankyou so much for your support. still I am new to grafana and this support is so valuable to me.
Thankyou once again.

[expoenergy]

No problem let me know how it pans out?