0

I am trying to perform a request for a Grafana dashboard from my Raspberry SQL database. The goal is to look for the most recent timestamp in another table (this performs as unique-ID) and get the requested data (TimeAxis and WeightAxis) from a second table.

SELECT
  (SELECT @stamp := unix_timestamp(coffeeTimestamp)
    FROM CoffeeData
    ORDER BY timestamp DESC LIMIT 1
  ),
  TimeAxis,
  WeightAxis
FROM ArrayLog
WHERE unix_timestamp(coffeeTimestamp) = @stamp

I tried several variation of this query. What hurdles me: I can get this query to do what I want, if I don't write "= @stamp" but "= 1614251117" (which is the result from the query in the brackets, if run separately). With "= @stamp" however, the query returns null.

Do you have any idea on how to solve this? Thanks!

Shadow
  • 33,525
  • 10
  • 51
  • 64
Macropodux
  • 23
  • 3
  • can you explain the `ORDER BY timestamp`? do you mean by ordering by a different column? – ysth Jan 05 '23 at 23:09
  • 2
    what are your actual values in CoffeeData and ArrayLog? what does calling unix_timestamp on both of them add? – ysth Jan 05 '23 at 23:09
  • Ah yes, I order by this timestamp because graphana needs it anyway (for WHERE __timeFilter(timestamp)) to filter by selected time-range. One could use coffeeTimestamp there as well, it is basically the same (one is created by MCU, other from NodeRed). – Macropodux Jan 06 '23 at 10:24
  • In CoffeeData there are 30 different columns with data about each coffee shot. ArrayLog is a huge table (by now) with time-frame data about weight, pressure, flow etc. To find a specific shot, one needs to filter by the unique-ID (which would be coffeeTimestamp). Grafana doesn't let me compare the two timestamps without converting them to a number first, because only "timestamp" be in a date-time format... hence my use of unix_timestamp – Macropodux Jan 06 '23 at 10:27
  • The value of the timestamps in the database are mysql format of "YYYY-MM-DD hh:mm:ss", in case this was your question – Macropodux Jan 06 '23 at 10:47
  • The select clause is executed after the where clause, so I don't think @stamp has any value in the where clause – Shadow Jan 06 '23 at 11:21
  • trivially, you can just do `SELECT TimeAxis, WeightAxis FROM ArrayLog WHERE unix_timestamp(coffeeTimestamp)=(SELECT unix_timestamp(coffeeTimestamp) FROM CoffeeData ORDER BY timestamp DESC LIMIT 1 )` – ysth Jan 06 '23 at 17:30

0 Answers0