How can change/convert timezone when I query in PostgreSQL

Question

I need a query to convert timezone in PostgreSQL

I have already tried this query

Select users.last_update_time + interval '7 hours'

And it plus for my result 7 hours.

But I have a problem that if I choose results from 31/01/2022 to 06/02/2022. It will include the result on 07/02/2022. Example before I plus 7 hours, it's on around 17:00 06/02/2022 to 00:00 07/02/2022.

The correct result has to be after +7 hours. So it means that result after plus 7 hours from 17:00 06/02/2022 to 00:00 07/02/2022 shouldn't be in there.

Answer 1

Then you should include the time zone in the query like this:

SELECT ... FROM ...
WHERE tscol BETWEEN '2022-01-31 00:00:00 America/Vancouver'
                AND '2022-02-07 00:00:00 America/Vancouver';

If you don't want to hard code the time zone, the best thing is to set the timezone database parameter to the correct time zone in your database session. Then you can use a simple type cast, which will respect the setting:

SELECT ... FROM ...
WHERE tscol BETWEEN CAST ('2022-01-31 00:00:00' AS timestamp with time zone)
                AND CAST ('2022-02-07 00:00:00' AS timestamp with time zone);