Ecto3 Timestamps and NaiveDatetime

Why don’t you simply use :utc_datetime instead? You will get transparent conversion to UTC upon saving your records and everything will be in one timezone. That’s what I tend to do and I defer the conversion to local timezone to the frontend.

To clarify, you’ll still get NaiveDateTime structs in your Elixir code but they will correspond to UTC and not your (or the server’s) timezone.

You mean when configuring Ecto timestamp options?

I generally create a custom schema macro + migration config, which sets timestamps to :utc_datetime_usec when starting a project.

But I feel there are a few misconceptions to clear up as well:

1. A the database level there’s no difference between :naive_datetime and :utc_datetime. Both are stored as timestamps. The difference is only at runtime, where on storage :utc_datetime does enforce that the datetime was supplied with Etc/UTC timezone, while the naive counterpart has no timezone to enforce. When loading data from the db :utc_datetime expects the timestamp to be in UTC.

2. Postgres has a timestamptz, but contrary to it’s name it doesn’t store a timezone just like timestamp. The difference is that for timestamptz postgres will try to convert datetimes to (and from) utc on its own. So with ecto you’re better of not having that, as ecto handles that part at runtime and you’ve one thing less to worry about with timestamp columns. timestamptz can make sense if you’re using the db not just with ecto.

So to conclude. Your db never actually stores a timezone, so from that standpoint it’s completely irrelevant if you’re using :naive_datetime or :utc_datetime. As for why ecto defaults to :naive_datetime I’d guess it’s because of backwards compatibility (but that could’ve been changed with ecto 3) and maybe the fact that elixir core does not ship with a timezone database, so it can only handle UTC as a timezone.

Yes and not only. Just declare all your datetime fields as :utc_datetime_usec or :utc_datetime.

This is imo not good advice. There are various places where naive_datetime is enough (a wall time in a fixed place) or :utc_datetime not being enough (a wall time in a certain place, which needs to be compared to times in other places).

Seeing as [in my experience] most people want a quick advice to get started and not have to write DB timestamp fixing scripts later, that advice is good enough IMO. Nuances of course always exist.

This is what I expected to be by default, not that I would need to have to remember to set up each time.

Exactly the type of workarounds that should not be needed.

Need to play around with this in Postgres, because at first sight I don’t get it.

So if you tell me that each time we use NaiveDatetime we are indeed using something that was created from UTC+0, then I am ok with NaiveDatetimes as the default, otherwise I just need to keep using workarounds all over the place.

That’s the case. The api is named NaiveDateTime.utc_now for a reason. Without an external timezone db elixir doesn’t even know of timezones outside of Etc/UTC. You can still mess this up though if your machines clock is not correct though, but that’s nothing elixir can cater for.

Thanks for pointing it out. It was not clear from reading the Ecto docs that the timestamps are being created with it.

timestamps(opts \\ [])
(macro)

Generates :inserted_at and :updated_at timestamp fields.

The fields generated by this macro will automatically be set to the current time when inserting and updating values in a repository.
Options

    :inserted_at - the ecto schema name of the field for insertion times or false
    :updated_at - the ecto schema name of the field for update times or false
    :inserted_at_source - the name of the database column for insertion times or false
    :updated_at_source - the name of the database column for update times or false
    :type - the timestamps type, defaults to :naive_datetime.
    :autogenerate - a module-function-args tuple used for generating both inserted_at and updated_at timestamps

All options can be pre-configured by setting @timestamps_opts.

And when I looked into NaiveDateTime docs It was not clear to me either, maybe because utc_now is the last one in that docs, and everything from the start in that page doesn’t lead you into UTC being used

iex> naive = ~N[2000-01-01 23:00:07]

naive.year
2000

naive.second
7