Select MySQL Date Range

[ederpardeiro1]

Hi guys!

I need query a date range (Last week, Last month, last six month, last year, etc) in MySQL Database. Have a dashboard that showes informations of calls survey in PBX.

Thinked use a “Interval” variable, but the interval’s is 1h, 1m, 1y,… and don’t know how convert this to date, to execute query.

Other idea is insert two variables box to insert start and end date, but grafana don’t have “mask” in fields. Is correctly this afirmation?

Can anywone helpe me to solve this?

PS: Sorry, I don’t speak fluently english

See below prints:

See my select returning “today” data:
SELECT DataHora, NumeroAvaliador, NotaAvaliacao FROM TBAvaliacoes where EmpresaAvaliacao = 1 and YEAR(DataHora) = year(now()) and Month(DataHora) = month(now()) and day(DataHora) = day(now()) and RamalAvaliado = $Ramal and NotaAvaliacao <= '5' order by DataHora desc

[ederpardeiro1]

Anyone can help me? Is possible that you don’t understand my question because my english does not fluently?

[pooh]

I need query a date range (Last week, Last month, last six month, last
year, etc) in MySQL Database. Have a dashboard that showes informations of
calls survey in PBX.

Thinked use a “Interval” variable, but the interval’s is 1h, 1m, 1y,… and
don’t know how convert this to date, to execute query.

1. Why do you need to convert it to a date? Grafana does this for you when
   you specify the interval you want - it creates $timeFilter as part of the
   query.

2. Why do you need a date at all? MySQL can quite happily understand
   intervals such as date_sub(curdate(), interval 1 month)

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

Other idea is insert two variables box to insert start and end date, but
grafana don’t have “mask” in fields. Is correctly this afirmation?

I’m not sure what you mean by a “mask”.

See my select returning “today” data:
SELECT DataHora, NumeroAvaliador, NotaAvaliacao FROM TBAvaliacoes where EmpresaAvaliacao = 1 and YEAR(DataHora) = year(now()) and Month(DataHora) = month(now()) and day(DataHora) = day(now()) and RamalAvaliado = $Ramal and NotaAvaliacao <= '5' order by DataHora desc

That section:

YEAR(DataHora) = year(now()) and Month(DataHora) = month(now()) and
day(DataHora) = day(now())

could be much more neatly expressed as date(DataHora)=curdate()

Antony.