CODEWITHSUNDEEP
postgresqldatetimetimezonetruncate
Mzia
Mzia

Reputation: 456

Why is at time zone works not as was expected?

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?

Upvotes: 0

Views: 111

Answers (2)

Mzia
Mzia

Reputation: 456

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'

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246083

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;

Upvotes: 2

Related Questions