Menu
I want to retrieve the daily number of signups recorded in Berlin timezone, but by default my database is using timezone '-08'. How do I do a select statement while specifying timezone?
915
In PostgreSQL 2 temporal data types namely timestamp and timestamptz where one is without timezone and the later is with timezone respectively, are supported to store Time and Date to a column. Both timestamp and timestamptz uses 8 bytes for storing timestamp values.
PostgreSQL assumes your local time zone for any type containing only date or time. All timezone-aware dates and times are stored internally in UTC . They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.
You can check all of the supported timezone names by using the following query: select * from pg_timezone_names; Let's exit our psql session, and reenter into it again. If we run SHOW timezone , it would be Asia/Kuala_Lumpur right?
If you need to change the timezone, you can connect to your Heroku Postgres database with $ heroku pg:psql and running ALTER DATABASE your_database_name SET timezone = timezone_name; . However, please consider the repercussions of changing the timezone for your application.
Introduction to PostgreSQL timestamp The timestamp datatype allows you to store both date and time. However, it does not have any time zone data. It means that when you change the timezone of your database server, the timestamp value stored in the database will not change automatically.
select some_date at time zone 'Europe/Berlin' from some_table
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
