1

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 ? Commented Oct 26, 2022 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.
    – user212533
    Commented Oct 26, 2022 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
    Commented Oct 26, 2022 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 ? Commented Oct 26, 2022 at 15:18
  • @ErgestBasha I basically want to this in SQLite: docs.influxdata.com/flux/v0.x/stdlib/interpolate/linear
    – adius
    Commented Oct 26, 2022 at 17:19

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.