You should use :utc_datetime unless you have a reason not to. The reason why Ecto’s timestamps() are naive by default is backwards compatibility. Since they are always UTC anyway, you should use :utc_datetime for them (and I believe the default will be changed to that in the future).

The difference between :utc_datetime and :naive_datetime is that the former will ensure that you can only insert UTC DateTimes and will read back UTC DateTime structs from the DB, whereas the latter will remove timezone information when writing and return NaiveDateTimes when you read from the DB.

For most cases, you should use UTC.

BTW, if you need to store the timezone too (in PostgreSQL), you have no option but to use a naive datetime field and store the offset or timezone information separately. This is because PostgreSQL does not have a data type for storing a timestamp with timezone.

There is the confusingly named timestamp with timezone but all it does is convert your input timestamp to UTC and convert it back to whatever your DB connection’s timezone is when reading (so for most cases it’s useless). It does not even store the offset/timezone. So you need to do that yourself somehow.

Also, use naive/UTC to store datetimes in the past, use timezones only when storing datetimes in the future, don’t mix them (personal experience). ^.^;

Exactly, that is why future times should always have a time zone.

Based on Postgres wiki:

Don’t use timestamp (without time zone) to store UTC times

Storing UTC values in a timestamp without time zone column is, unfortunately, a practice commonly inherited from other databases that lack usable timezone support.

Use timestamp with time zone instead.

Why not?

Because there is no way for the database to know that UTC is the intended timezone for the column values.

This complicates many otherwise useful time calculations. For example, “last midnight in the timezone given by u.timezone” becomes this:

date_trunc('day', now() AT TIME ZONE u.timezone) AT TIME ZONE u.timezone AT TIME ZONE 'UTC'

And “the midnight prior to x.datecol in u.timezone” becomes this:

date_trunc('day', x.datecol AT TIME ZONE 'UTC' AT TIME ZONE u.timezone)
  AT TIME ZONE u.timezone AT TIME ZONE 'UTC'

When should you?

If compatibility with non-timezone-supporting databases trumps all other considerations.

Wrong, timestamptz will return the timestamp in the connection’s local timezone. They explicitly won’t be getting UTC from the database, unless they have set the connection to UTC. Quoting:

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone.

This is what I never want to happen.

Now it’s true that if you fire up psql or some script and compare the timestamp values to for example NOW(), you may end up with unexpected results if you didn’t remember to set the connection timezone. For me, using timestamptz here would be optimising for a special case and making the general case worse. It’s also reliant on magic (and frankly unintuitive) behaviour from PostgreSQL, and I feel being explicit in what you do (like you have to be when using plain timestamp) is better.

timestamp with time zone is a misnomer and IMO a misfeature that shouldn’t be in PostgreSQL.