Hey, @Nicd, and everyone who participated in this discussion.
I noticed in the PostgreSQL documentation that there is a
timezone with timestamp type. For reference - https://www.postgresql.org/docs/12/datatype-datetime.html, although it is present before version 12 too (the earliest supported version, 9.4, also has it). For
What would be the reason for Ecto to not use it?
From the docs:
The SQL standard differentiates timestamp without time zone and timestamp with time zone literals by the presence of a “+” or “-” symbol and time zone offset after the time. Hence, according to the standard,
TIMESTAMP '2004-10-19 10:23:54'
is a timestamp without time zone, while
TIMESTAMP '2004-10-19 10:23:54+02'
is a timestamp with time zone.
PostgreSQL never examines the content of a literal string before determining its type,
and therefore will treat both of the above as timestamp without time zone.
To ensure that a literal is treated as timestamp with time zone, give it the correct explicit type:
TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
There is a discussion here from a few years ago - https://github.com/elixir-ecto/ecto/issues/535#issuecomment-84567574, in which Jose advocates that “dates should be saved without timezones in the database”.
Am I right in understanding that the general consensus is that there is no reason to use timezone information at the database level and the right approach would be to convert a timestamp for example in the view layer of Phoenix depending on the current client?