I have used the :datetime type in an old migration and defined the column in the schema as utc_datetime, however I noticed the Ecto warning that the :datetime type in migrations is deprecated, please use :utc_datetime or :naive_datetime instead.
I wonder if I need to do something more and possibly rollback if I want to fix this? If I just edit the migration so that I comply with the warning, would this result in a discrepancy on the database level at some future point, or the fact that the field in the schema is of type utc_datetime would suffice?
I looked through the Ecto documentation but could not find any explanation related to this warning.
Would this mean that specifying utc_datetime in the schema in the model is not a good idea?
Edit: Edited the Ecto github url to be for version 2.2, as it is the one I’m currently using in the project. The relevant code is not changed compared to the version from the commit I initially posted.
Both :naive_datetime and :utc_datetime will map to the same database type by default, e.g. in PostgreSQL they will use timestamp (without timezone). The only difference is that the first one will only take in NaiveDateTimes and the second one will 1) assume that everything read from the DB is in UTC, and 2) only accept and read DateTimes.
What you should use in your schema and migrations depends on your existing data in the database. If you know the data is UTC (which seems to be the case), you can rewrite them as :utc_datetime. If your data is not UTC, you will need to use :naive_datetime instead.
Thank you for the quick answer! I have been using UTC data, which means it should probably be fine.
As a sidenote, I just tested this and it seems that having utc_datetime specified in the model schema does not prevent you from saving a naive datetime, so if someone is not careful they can get mixed up. This might be of help to someone in a similar position as mine in the future.
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'
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?
For utc_datetime ecto does already ensure that the datetime is in the Etc/UTC timezone. Other timezones cannot be set to those fields. What timezone with timestamp does is ensure that saved datetimes are in UTC and if not convert to UTC. So for usage with ecto there’s actually no benefit to using timezone with timestamp at all, because everything is UTC all the time when it comes to what postgres is working with. timezone with timestamp does contrary to it’s naming not retain the initial timezone it converted from. So if you need to store a datetime in a timezone different to UTC you’ll need additional columns no matter what you chose.
timezone with timestamp becomes tricky when you take into account that postgres is doing the conversion (potentially different timezone db than elixir) and it’s depending on things like server configuration (conversion and e.g. psql) and in case of e.g. postgres admin tools possibly the client timezone as well. Personally I’d rather have all those things be UTC and only have the app deal with conversions.
So unless you want to integrate many applications via a single database and not all of them use ecto there’s not much to gain from using timezone with timestamp.
It indeed would be very useful in many situations to store timezone information with timestamps in the database. But that is not what timestamptz (timestamp with time zone) does. Timestamptz does not store timezone information. Its unfortunate naming is PostgreSQL’s worst feature in my opinion.
What timestamptz does is two things:
It converts any timestamp you store to UTC.
When retrieved or used in queries, it converts the timestamp into the database connection’s timezone.
If you want that behavior, you should use timestamptz. Personally I never want behavior 2, because I want to be explicit about my timezone conversions. Ecto already ensures that :utc_datetime only accepts DateTime structs and that they are converted to UTC on insertion.
If you need the original offset or timezone, you will need to store that separately. PostgreSQL won’t do it for you, even though it wants you to think it does.
And excuse me for the typo, I wrote 
