Filter by timestamp in query with postgresql

I would like to select the number of rows with a count function but it should only count the rows from yesterday.

It should look something like this:

SELECT COUNT(signal.value)
FROM signal
WHERE signal.timestamp >= now() -1

NOW()-1 and NOW() - INTERVAL 1 DAY and CURDATE()-1 don’t work.
Can somebody help?

Hi,

What data source are you using, influxdb?

Marcus

Hi,

I am using postgresql

Hi,

Have you read the postgresql grafana documentation?

Maybe you can try and add a timeshift of 1h, see documentation.

Marcus

Thank you very much for your help!

I have read the documentaion and pretty much all Grafana support topics I could find.
With the timeshift there are two problems. As far as I understand it the shift is always relative but I want to get the same answer whether I look at 10:00 in the morning or 17:00 in the evening. So only selcect the data with the timestamp from the last day not the last 24h.
Secondly the timeshift doesn’t affect the COUNT() function.

Lukas

Hi,

Reason for the timeshift not affects your query is because you’re not using the $__timeFilter macro function which you can read about in the documentation.

How are you planning to visualize this, graph, table or singlestat panel?

Marcus

Hi Marcus

Thank you for still helping me.
I would like to visualize it with a table. The first column lists the different sources (places) of the signals and the second column should give the number of signals received from the day prior.

It works with a query like this:

SELECT source.name, COUNT(signal.value)
FROM signal
INNER JOIN source ON signal.source_id = source.source_id
WHERE timestamp < now()
GROUP BY source.name

But in the second column, this returns me the number of all signals received from the sources.
So the WHERE part should say “timestamp > CURDATE()-1”. In SQL this works but not in Grafana.
Is there any way to make this work?

Hey,

Maybe you can do something like this:

SELECT
  $__timeGroup(timestamp, '24h') as time_sec,
  source.name,
  COUNT(signal.value) as count
FROM signal 
  INNER JOIN source ON signal.source_id = source.source_id
WHERE 
  $__timeFilter(timestamp) AND 
  timestamp > ((CURDATE() - 1) + ' 00:00:00') AND
  timestamp < (CURDATE() + ' 00:00:00')
GROUP BY time_sec, source.name
ORDER BY time_sec

This should give you one row per source with a count for yesterday. Maybe I messed up the timestamp logic, but it should be timestamp > yesterday 00:00 and timestamp < today 00:00. You shouldn’t need to timeshift with this.

Please try it out and let me know if that works

Marcus

1 Like

Hey
Thank you for the idea. Using the macro functions should help.
But there is still a Problem. In Grafana the CURDATE() function doesn’t exist (pq: function curdate() does not exist). I tried achieving the same thing with the NOW() function but NOW()-1 doesn’t work either (pq: operator does not exist: timestamp with time zone - integer).

Ah sorry. Thought you we’re using mysql :slight_smile:

So after some postgres research maybe something like this could work?

SELECT
  $__timeGroup(timestamp, '24h'),
  source.name,
  COUNT(signal.value) as count
FROM signal 
  INNER JOIN source ON signal.source_id = source.source_id
WHERE 
  $__timeFilter(timestamp) AND 
  "timestamp" > (date_trunc('day', CURRENT_DATE - INTERVAL '1 day')) AND
  "timestamp" < (date_trunc('day', now()))
GROUP BY time, source.name
ORDER BY time

Marcus

2 Likes

Thank you!
Now it worked. For my use it’s actually easier to not use the macro functions most of the time (for some tables I will use your solution). It ends up like this:

SELECT source.name, source.serial, COUNT(signal.value)
FROM signal
INNER JOIN source ON signal.source_id = source.source_id
INNER JOIN parameter ON signal.parameter_id = parameter.parameter_id
WHERE parameter.name = ‘counter’ and timestamp > (date_trunc(‘day’, CURRENT_DATE - INTERVAL ‘1 day’)) and timestamp < (date_trunc(‘day’, CURRENT_DATE))
GROUP BY source.nam, source.serial

My main mistake was as well using mysql functions instead of postgresql (CURRENTDATE() instead of CURRENT_DATE).

Greetings Lukas

1 Like