0

I have following table:

CREATE TABLE entries (
  utc TEXT,
  entry INTEGER
);

with following data:

INSERT INTO entries
  (utc, entry)
VALUES
  ('2022-10-01 01:54', 23),
  ('2022-10-04 02:40', 46),
  ('2022-10-09 16:20', 51),
  ('2022-10-10 04:05', 81),
  ('2022-10-16 12:26', 78);

How can I write a query to get the daily value (linear interpolated) like this:

utc              | entry
-----------------|------
2022-10-01 00:00 | …
2022-10-02 00:00 | …
2022-10-03 00:00 | …
…                | …

Here is a pre-filled dbfiddle for playing around: https://dbfiddle.uk/TCX9Rd5t

5
  • 1
    Are you trying to fill the missing dates ? Why is the entry column in the expected result empty ? Oct 26 at 13:18
  • Some combination of this (sqlite.org/series.html) and this (sqlite.org/lang_datefunc.html) should get you in the right direction.
    – bbaird
    Oct 26 at 14:57
  • @ErgestBasha The entry column is not supposed to be empty, but I don't know the values since they are supposed to be calculated by the query I'm looking for (and I'm too lazy to calculate them by hand ^^).
    – adius
    Oct 26 at 15:13
  • @adius What do you mean by but I don't know the values since they are supposed to be calculated by the query I'm looking for ? If you have two entries for date 2022-10-01 let's say 23 and 55 , would the result be 2022-10-01 00:00 | 78 ? Oct 26 at 15:18
  • @ErgestBasha I basically want to this in SQLite: docs.influxdata.com/flux/v0.x/stdlib/interpolate/linear
    – adius
    Oct 26 at 17:19

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.