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?

What is the data type of the column `tm`? – Laurenz Albe Jun 09 '20 at 12:58 — Laurenz Albe, Jun 09 '20 at 12:58

score 2 · Accepted Answer · answered Jun 09 '20 at 13:44

2

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;

answered Jun 09 '20 at 13:44

Laurenz Albe

209,280
17
206
263

It's working, thank you! But I also need to group by day, so I'm doing this: date_trunc('day', min(tm) at time zone '3' at time zone 'UTC') and it's not working as expected - WHERE (tm BETWEEN '2020-06-06 21:00:00+00:00:00' AND '2020-06-08 20:59:00+00:00:00') must return 3 rows for 07 and 3 rows for 08, but it returns 6 rows for 07 and 3 for 08 like it includes rows for 06... Can you help to solve this? – Mzia Jun 10 '20 at 13:44
Sounds like a new question, but you probably need similar conversions in the `WHERE` condition. – Laurenz Albe Jun 10 '20 at 13:54
Thank you)) Nothing I'm trying is working for me(except of subquery, but I'd like to do it without them) so I created new question – Mzia Jun 11 '20 at 09:50

score 0 · Answer 2 · answered Jun 09 '20 at 13:47

I found my answer here timezone aware date_trunc function

When I saw this in the answer:

    timestamp with time zone '2001-01-1 00:00:00+0100' at time zone '-02'

It's because tm is +03:00:00 and I'm adding it again...

So what I can do is:

   date_trunc('day', tm at time zone '0')

I guess there's must be better way to do it, without '0'

Why is at time zone works not as was expected?

2 Answers2