Why is at time zone works not as was expected?

Question

I have a server that gives me some information grouped by day, so I tried to use date_trunc(), but because of the timezone I know that 2020-06-05 21:00:00 in my DB is actually 2020-06-06 00:00:00.

So if I just use date_trunc, I got 2020-06-05 00:00:00, but I need 2020-06-06 00:00:00.

I'm trying this:

  select tm ,  date_trunc('day', (tm) at time zone '+3')
   from scm.tbl
  where (tm BETWEEN '2020-06-05 15:00:00+00:00:00' AND '2020-06-08 20:59:00+00:00:00')
  order by tm

And I have this:

2020-06-05 17:59:59  | 2020-06-05 00:00:00
2020-06-05 18:00:10  | 2020-06-06 00:00:00

At 18:00 the date became 2020-06-06, but it shouldn't. Why does it? What am I doing wrong here?

Answer 1

The problem is that AT TIME ZONE will convert a timestamp without time zone into a timestamp with time zone, and that is again handled according to your current timezone setting.

So you need to use AT TIME ZONE twice, first to interpret the timestamp in the correct time zone, and then to extract what an UTC clock would show at that time:

SELECT tm, date_trunc('day', tm AT TIME ZONE '+3' AT TIME ZONE 'UTC')
FROM scm.tbl
WHERE (tm BETWEEN '2020-06-05 15:00:00+00:00:00' AND '2020-06-08 20:59:00+00:00:00')
ORDER BY tm;