Get data from 24 hours earlier

Grafana MySQL
1

Hi,

I query weather data from a local Mysql table like this:

SELECT
  UNIX_TIMESTAMP(datetime) AS "time",
  temperature
FROM weather
ORDER BY datetime

Now I would like to do the same query, but for 24 hours earlier, so the exact same time one day before.
How can I achieve this?

Thanks!

2

hello

might want to look at mysql help

DATE_SUB(CURDATE(), INTERVAL 1 DAY)
3

you want only one value ? or a complete shifting day time?
$__timeFilter(datetime) should help you
${__from:date}

4

I’m looking for only 1 value, so the value of the same timestamp, but 24 hours back.

5

I can’t get it to work. Could you give an example how to put this in my query/

6

maybe this?

SELECT
  UNIX_TIMESTAMP(datetime) AS "time",
  temperature
FROM weather
WHERE datetime = DATE_SUB(CURDATE(), INTERVAL 1 DAY)

ORDER BY datetime
7

First, thanks for your help!

The query you proposed doesn’t give any errors, but also no results at all…

image
image844×346 23.2 KB

I’ll experiment with this query some more tonight. Thanks so far!

1 Like
8

I was working on a similar problem with a different data source. I recommend validating the query separately from what Grafana displays. My understanding is that data points are filtered by Grafana after the query is run. If Grafana is displaying points for the last 24 hours and the result set includes a point with a timestamp 48 hours old you won’t see it. You need to manipulate the timestamps the query returns so they appear inside your display time window.

1 Like
9

Only if the display window time is used in the query

1 Like