I am using PostgreSQL 10. I have a couple of timestamp with timezone columns in 3 tables.
Should I use UTC as my default timezone in postgresql.conf and constantly transforming to Europe Athens in every session?
Or have Europe Athens as my default, never have to transform, but if other apps access the same database will have to transform to their timezone?
What is wiser? I guess I have to consider if setting a timezone in the session affects the query speed.
Should I use UTC as my default timezone in postgresql.conf and constantly transforming to Europe Athens in every session?
No, this is a waste of effort because the database stores timestamps in UTC anyway, whatever you choose for your default timezone.
For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.
Source: https://www.postgresql.org/docs/10/static/datatype-datetime.html
europe/athens and then another app access the DB, it will take 2 transformations to make it eg europe/zurich from athens to UTC to zurich. But according to you (and manual) it will be UTC -> athens -> UTC -> zurich. Right? So better to just leave it in UTC and anyone that access it, sets a timezone for the session.