Why does adding a query condition affect the results?

TimescaleDB and PostgreSQL
1 
with full_data as (select time_bucket_gapfill('1 day':: interval, "recode_time") full_time,
                          meter_code,
                          interpolate(max(max_reading))                          max_reading
                   from water_downsampling_day
                   where meter_code = 'METXJJSZXNSB1'
                     and (recode_time >= '2024-05-01 00:00:00'::timestamp - interval '1 day' and
                          recode_time <= '2024-05-31 23:59:59'::timestamp)
                   group by full_time, meter_code)
select to_char(full_time, 'YYYY-MM-DD'),
       full_time,
       coalesce(max_reading - lag(max_reading) over (partition by meter_code order by full_time), 0) row_diff
from full_data;

2024-05-01, the reading is: 1

ce8c6fe96419047c8f0c2feba40c882
ce8c6fe96419047c8f0c2feba40c882785×712 12.2 KB

When SQL adds: where to_char (full_time,'YYYY-MM-DD ')>=‘2024-05-01’;

with full_data as (select time_bucket_gapfill('1 day':: interval, "recode_time") full_time,
                          meter_code,
                          interpolate(max(max_reading))                          max_reading
                   from water_downsampling_day
                   where meter_code = 'METXJJSZXNSB1'
                     and (recode_time >= '2024-05-01 00:00:00'::timestamp - interval '1 day' and
                          recode_time <= '2024-05-31 23:59:59'::timestamp)
                   group by full_time, meter_code)
select to_char(full_time, 'YYYY-MM-DD'),
       coalesce(max_reading - lag(max_reading) over (partition by meter_code order by full_time), 0) row_diff
from full_data
where to_char(full_time, 'YYYY-MM-DD') >= '2024-05-01';

2024-05-01, the reading changed to: 0

265a0450f4b87f0d153536f63ba061a
265a0450f4b87f0d153536f63ba061a793×694 12.6 KB

This is really strange. May I ask, have any of you encountered this kind of problem?

2

Exploring, my guess is that it’s a time zone issue:

postgres=# select to_char(now() at time  zone 'UTC', 'YYYY-MM-DD HH:mm:SS');
       to_char
---------------------
 2024-06-17 02:06:14
(1 row)

postgres=# select to_char(now() at time  zone 'GMT+13', 'YYYY-MM-DD HH:mm:SS');
       to_char
---------------------
 2024-06-17 01:06:30
(1 row)

postgres=# select to_char(now() at time  zone 'GMT-13', 'YYYY-MM-DD HH:mm:SS');
       to_char
---------------------
 2024-06-18 03:06:39
(1 row)

postgres=# select to_char(now() at time  zone 'GMT-13', 'YYYY-MM-DD');
  to_char
------------
 2024-06-18
(1 row)
3

Yes, you are right. After I modified it to:

where to_char(full_time at time zone 'PRC', 'YYYY-MM-DD') >= '2024-05-01'

it ran normally. Thank you.