1

I'm using Firebird 2.5 through Delphi 10.2/FireDAC. I have a table of historical data from industrial devices, one row per device at one-minute intervals. I need to be able to retrieve an arbitrary time window of records on an arbitrary interval span (every minute, every 3 minutes, every 15 minutes, etc.) which then gets used to generate a trending grid display or a report. This example query, pulling every 3 minutes records for a two-day window for 2 devices, does most of what I need:

select * 
from avc_history 
where (avcid in (11,12)) 
and (tstamp >= '6/26/2022') and (tstamp < '6/27/2022') 
and (mod(extract(minute from tstamp), 3) = 0)
order by tstamp, avcid;

The problem with this is that, if device #11 has no rows for a part of the time span, then there is a mismatch in the number of rows returned for each device. I need to return a row for each device on each minutes period regardless of whether a row on that timestamp actually exists in the table - the other fields of the row can be null as long as the tstamp field has the timestamp and the avcid field has the device's ID.

I can fix this up in code, but that's a memory and time hog, so I want to avoid that if possible. Is there a way to structure a query (or if necessary, a stored procedure) to do what I need? Would some capability in the FireDAC library that I'm not aware of make this easier?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
SteveS
  • 335
  • 1
  • 13
  • I'll add: I was surprised by how efficient the `mod(extract())` clause turned out to be. The retrieval time for even very large datasets is very little different from the same query with the whole `mod(extract())` clause removed. – SteveS Jun 29 '22 at 13:15
  • A common way is a join with a table/query with the interval values you want on a condition that selects the data you want for each of those intervals. So a query/table containing all the datetimes every 3 min between the end dates joined with avc_history with a join condition that picks the value you want for that interval. – Brian Jun 29 '22 at 13:22
  • Brian - I get what you're saying, but how do I get the query to generate the rows for the timestamps with no backing data? Create a temp table with just timestamps at the appropriate intervals, and then join on that? – SteveS Jun 29 '22 at 13:32
  • 1
    Generating the series data has various solutions. One would be generate the data in Delphi and use Array DML (Firedac) to quickly load it into a Firebird temporary table. Then join on that. Or write a stored procedure that returns the table data. – Brian Jun 29 '22 at 13:54
  • 1
    @SteveS - You can easily create a stored procedure that returns the timestamps in the desired period. And then join the stored procedure with the table. – Rohit Gupta Jun 29 '22 at 14:02
  • OK, I can generate the GTT contents but the problem appears to be that the timestamps in the history table are written as `DEFAULT CURRENT_TIMESTAMP`, which includes some variable number of milliseconds, so `where tmptbl.tstamp = avctbl.tstamp` is always false. :( – SteveS Jun 29 '22 at 19:11
  • I can't see a reasonable way to trim the timestamps to the minute level so the equality test works. – SteveS Jun 29 '22 at 19:46
  • You can use dateadd / datediff to trim things or build a `between` condition, or consider changing to `CURRENT_TIMESTAMP(0)` instead of `CURRENT_TIMESTAMP` if you don't want millisecond precision. In any case, it would be helpful if you provide DDL and DML (inserts) with example data as a starting point for building an answer. – Mark Rotteveel Jun 30 '22 at 07:53
  • Can you make a table with time intervals (yes, two `TIMESTAMP` columns, start and end of each interval) - perhaps a `global temporary table` and then `LEFT JOIN` your devices to it. That way you might hope for a device with no data to have `NULL` row – Arioch 'The Jul 05 '22 at 18:49
  • Instead of a temporary table you can also use a stored procedure. See this answer https://stackoverflow.com/a/48992167/1037511 for an example of every day of a month (which you can re-code to do every 3 minutes per day). Using that procedure in a SELECT you can left join with your own table. – Rik Jul 06 '22 at 07:18
  • Rik's link helped me over the hump on this. Thanks! – SteveS Jul 06 '22 at 20:29

0 Answers0