I have a given time range, here is a simple query example:
WITH
toDateTime('2022-08-31 18:00:00') AS start,
toDateTime('2022-08-31 21:00:00') AS end
SELECT
id,
timestamp,
value,
arrayMap(x -> toUnixTimestamp(x), range(toUInt32(start), toUInt32(end), 3600)) AS time_range
FROM
table
WHERE
has(time_range, timestamp)
AND
id IN (1, 2)
ORDER BY
id ASC,
timestamp ASC
As expected this returns:
1 1661958000 351 [1661958000,1661961600,1661965200]
2 1661958000 3000 [1661958000,1661961600,1661965200]
2 1661961600 2999 [1661958000,1661961600,1661965200]
2 1661965200 0 [1661958000,1661961600,1661965200]
I'm trying to find a solution for a similar result:
1 1661958000 351 [1661958000,1661961600,1661965200]
1 1661961600 null [1661958000,1661961600,1661965200]
1 1661965200 null [1661958000,1661961600,1661965200]
2 1661958000 3000 [1661958000,1661961600,1661965200]
2 1661961600 2999 [1661958000,1661961600,1661965200]
2 1661965200 0 [1661958000,1661961600,1661965200]
The point is that I need to replace the value column with null in non-existent records with specific timestamps.
As far as I understand, the WITH FILL construct is not quite suitable for my case.
How can I achieve this?