How to use IN timeRangeWithModifier with time zone in QuestDB

Question

How does IN timeRangeWithModifier handle a timezone which changes around UTC ? Say I wanted records at 4pm London every day for a year, it would need to give me 3pm UTC for part of the year and 4pm UTC for the other part. Is that possible?

Answer 1

Assuming simple table such as:

create table tst ( ts timestamp) timestamp(ts);

insert into tst 
select dateadd('h', x::int, '2024-01-01T00:00:00' )
from long_sequence(365*24);

we'd normally formulate the condition as:

select * from tst where ts in  '2024-01-01T15:00:00;1h;1d;365';

Of course it doesn't work because IN operator timeRangeWithModifier assumes UTC. If we rewrite it as:

select *
from tst 
where hour(to_timezone(ts, 'Europe/London') ) = 16

then it does produce the correct result but is slower as it doesn't use interval scan anymore (as can be checked with EXPLAIN command). I think the most reasonable approach for now is to combine broader IN condition with hour() check, e.g.

select ts, hour(ts), to_timezone(ts, 'Europe/London'), hour(to_timezone(ts, 'Europe/London') )
from tst 
where  ts  in  '2024-01-01T15:00:00;2h;1d;365'
and hour(to_timezone(ts, 'Europe/London') ) = 16

As we can see in the output, it produces the correct result around time of DST switch:

ts                         hour to_timezone                 hour1
2024-03-30T16:00:00.000000Z 16  2024-03-30T16:00:00.000000Z 16
2024-03-31T15:00:00.000000Z 15  2024-03-31T16:00:00.000000Z 16