locf()

The locf (last observation carried forward) function allows you to carry the last seen value in an aggregation group forward. It can only be used in an aggregation query with time_bucket_gapfill. The locf function call cannot be nested inside other function calls.

For more information about gapfilling and interpolation functions, see the hyperfunctions documentation.

Required arguments

Optional arguments

Because the locf function relies on having values before each time bucket to carry forward, it might not have enough data to fill in a value for the first bucket if it does not contain a value. For example, the function needs to look before the first time bucket, but the query’s outer time predicate WHERE time > ... restricts the function to only evaluate values within this time range. This means that the prev expression tells the function how to look for values outside of the range specified by the time predicate. The prev expression is only evaluated when no previous value is returned by the outer query. For example, when the first bucket in the queried time range is empty.

Sample usage

Get the average temperature every day for each device over the last seven days, carrying forward the last value for missing readings:

2. SELECT

4. time_bucket_gapfill('1 day', time, now() - INTERVAL '1 week', now()) AS day,

6. device_id,

8. avg(temperature) AS value,

10. locf(avg(temperature))

12. FROM metrics

14. WHERE time > now () - INTERVAL '1 week'

16. GROUP BY day, device_id

18. ORDER BY day;

20. day          | device_id | value | locf

22. ------------------------+-----------+-------+------

24. 2019-01-10 01:00:00+01 |         1 |       |

26. 2019-01-11 01:00:00+01 |         1 |   5.0 |  5.0

28. 2019-01-12 01:00:00+01 |         1 |       |  5.0

30. 2019-01-13 01:00:00+01 |         1 |   7.0 |  7.0

32. 2019-01-14 01:00:00+01 |         1 |       |  7.0

34. 2019-01-15 01:00:00+01 |         1 |   8.0 |  8.0

36. 2019-01-16 01:00:00+01 |         1 |   9.0 |  9.0

38. (7 row)

Get the average temperature every day for each device over the last seven days, carrying forward the last value for missing readings with out-of-bounds lookup:

2. SELECT

4. time_bucket_gapfill('1 day', time, now() - INTERVAL '1 week', now()) AS day,

6. device_id,

8. avg(temperature) AS value,

10. locf(

12. avg(temperature),

14. (SELECT temperature FROM metrics m2 WHERE m2.time < now() - INTERVAL '2 week' AND m.device_id = m2.device_id ORDER BY time DESC LIMIT 1)

16. )

18. FROM metrics m

20. WHERE time > now () - INTERVAL '1 week'

22. GROUP BY day, device_id

24. ORDER BY day;

26. day          | device_id | value | locf

28. ------------------------+-----------+-------+------

30. 2019-01-10 01:00:00+01 |         1 |       |  1.0

32. 2019-01-11 01:00:00+01 |         1 |   5.0 |  5.0

34. 2019-01-12 01:00:00+01 |         1 |       |  5.0

36. 2019-01-13 01:00:00+01 |         1 |   7.0 |  7.0

38. 2019-01-14 01:00:00+01 |         1 |       |  7.0

40. 2019-01-15 01:00:00+01 |         1 |   8.0 |  8.0

42. 2019-01-16 01:00:00+01 |         1 |   9.0 |  9.0

44. (7 row)