I’m confused how time zones is handled in phoenix / ecto. We are having problems as all our sql code works locally but fails on our servers (using date_trunc).
If we connect with dbeaver and execute show timezone we get correctly Europe/Vienna back, however if I point my phoenix app to our server and execute Repo.query("show timezone") UTC is returned from our server.
If I point my phoenix app to my local postgres the timezone Europe/Vienna is returned.
How can I configure Ecto or Postgres to use Europe/Vienna by default so our date_trunc code is working currectly.
Postgrex is using the binary protocol for postgrex communication (unlike most GUI clients), which does not set any user session defaults like timezones. You’d need to do that manually if you want it to happen. Maybe you can use after_connect or you can do it per transaction/query.
When the input value is of type timestamp with time zone , the truncation is performed with respect to a particular time zone; for example, truncation to day produces a value that is midnight in that zone. By default, truncation is done with respect to the current TimeZone setting, but the optional time_zone argument can be provided to specify a different time zone.
A time zone cannot be specified when processing timestamp without time zone or interval inputs. These are always taken at face value.
I mean relying on the postgres timezone setting to be Europe/Vienna is error prone. I like to be explicit with timezone shenanigans because it is subject to myriad errors.
I was under the impression that one should keep the DB server at UTC if they can.
It very much depends. Individual timestamp columns I tend to keep in UTC, but if the orginal timezone is relevant I also keep that (see TzDatetime — tz_datetime v0.1.3).
For fetching data it depends even more. It’s often a lot more efficient to rollup aggregations in the database and e.g. aggregations, which are date based are timezone dependent (when does a day start or stop in a certain location). Just need to make sure that db and app server run of the same tz database version to not get mismatches.
Explicitly setting the db to use a supplied TZ is “explicit”. As mentioned the wire protocol doesn’t implicitly use a certain timezone.
If you’re certain the db timezone is configured as you expect and won’t be changed by anyone at any point in the future, or between when it changes and the next time you check what it is (if you’re checking).
I guess most people are in control of the servers and databases on which their stuff runs, so they don’t worry about this sort of thing.
Sure, proper db setup is required as well, but personally I try to not depend on the timezone set for the db in the first place. If nothing depends on the db being setup for a specific timezone nobody can mess it up.
First time I am using a DB setup that has a time zone set up and using a timestamp with timezone column. We did some research 2 years back and until now never had problems with it. I guess both ways have pros and cons.
