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.

i query this select pg_typeof(users.last_update_time) From users And it give result: timestamp without time zone — Mạnh Tô
– Mạnh Tô, Commented Feb 9, 2022 at 8:45

Laurenz Albe · Accepted Answer · 2022-02-09 10:11:44Z

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);

edited Feb 9, 2022 at 10:11

answered Feb 9, 2022 at 9:21

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mạnh Tô Over a year ago

thank you so much for this. But it will be a problem if I put this query in the dashboard. It will not be an automatic change result following the time I chosse because the clause is from 31/01 - 07/02.

Laurenz Albe Over a year ago

I have added information how to avoid hard coding the time zone.

Collectives™ on Stack Overflow

How can change/convert timezone when I query in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related