CODEWITHSUNDEEP
timescaledb
KRG
KRG

Reputation: 168

time_bucket_gapfill and interpolate

I'm using timescaledb version 2.4.1 on postgresql 13.4

I want to interpolate daily values of an electicity meter using interpolation (in this example I'm missing a lot of days). I wont to get the daily values at midnigth (00:00:00+00) and if no value exist I want the interpolated value. The input data is:

2019-11-04 09:00:02+00  10796491
2020-01-28 10:00:00+00  10807703
2020-01-28 11:00:00+00  10818215
2020-01-28 12:00:00+00  10839014
...
...
...
2020-01-31 22:00:00+00  12554884
2020-01-31 23:00:00+00  12574581
2020-02-01 00:00:00+00  12593773

Using the query:

select time_bucket_gapfill('1 day',dtstamp) as stamp,
    interpolate(min(value),
               (select (dtstamp,value) from timedata td where td.dtstamp < '2020-01-01' order by td.dtstamp desc limit 1),
               (select (dtstamp,value) from timedata td where td.dtstamp > '2020-02-01' order by td.dtstamp limit 1))
                as val
from timedata 
where dtstamp >= '2020-01-01' AND dtstamp <= '2020-02-01'
group by stamp

I get the values:

2020-01-01 00:00:00+00  10804125.758253396
2020-01-02 00:00:00+00  10804258.248688456
2020-01-03 00:00:00+00  10804390.739123514
...
...
...
2020-01-27 00:00:00+00  10807570.509564942
2020-01-28 00:00:00+00  10807703
2020-01-29 00:00:00+00  11072810
2020-01-30 00:00:00+00  11559716
2020-01-31 00:00:00+00  12062945
2020-02-01 00:00:00+00  12593773

The value at 2020-01-28 00:00:00+00 is the same as the value from 2020-01-23 10:00:00+00 indicating that no interpolation has been made. Maunally calculating the start value at 2020-01-01 00:00:00+00 from the date before and after also doesn't match the result from timescaledb. It seems like all values has been interpolated at the time 10:00 but showing as 00:00.

Is the query wrong? Is there a way to do what I need (daily values at 00:00 - interpolated if none exists)?

Upvotes: 3

Views: 475

Answers (1)

user1407764
user1407764

Reputation: 199

Three years late, but just on reading your question I'd assume you need to add timezones to the static dates you're providing in your query.

Upvotes: 0

Related Questions