Why use `utc_datetime` over `naive_datetime` for Ecto?

Questions / Help
, , ,
1

Hi all,

Trying to get some more clarity over utc_datetime and naive_datetime for Ecto:

https://hexdocs.pm/ecto/Ecto.Schema.html#module-types-and-casting
https://hexdocs.pm/elixir/NaiveDateTime.html
https://hexdocs.pm/elixir/DateTime.html#content

The documentation above suggests that while NaiveDateTime doesn’t store any
TimeZone data associated with it, DateTime seems to, however the former, when used,
Ecto seems to store timestamps (inserted_at, updated_at) in according to UTC timezone anyway.
I read that using utc_datetime “ensures” that dates are always converted to utc before getting stored/retrieved from and to the database, however I am not sure what the significance of the “ensures” aspect here is, since naive_datetime seems to do it anyway.

And I also think storing timestamptz values in the database may not be really required, since I can just store utc timestamps everywhere and user timezone preferences in another table and use these two pieces of info to convert accordingly before presenting to the users.

Can someone throw some light on what the real world significance in using utc_datetime type for timestamps is? What am I missing here?

4 Likes
2

You’re correct. timestamp columns are actually better for ecto (alone) as with timestamptz postgres will try to convert the timestamp to the clients timezone on its own. While for timestamp you can be sure postgres just leaves timestamps as is, with ecto handling all the timezone stuff at runtime using the settings/timezone db of elixir.

naive_datetime does store timestamps as UTC datetimes – that’s true. But that’s only “accidental” because it’s using NaiveDateTime.utc_now() to generate timestamps. If you manually set such fields to a different naive datetime you build from another timezone there’s no way for ecto to detect that. NaiveDateTime structs just don’t have a timezone component, which is the reason why they’re called naive.

For utc_datetime ecto will enforce that any saved datetime uses the Etc/UTC timezone. It’ll error otherwise. If you query the record you’ll also get back only datetimes with tthe timezone set to UTC. So the fact that datetimes use UTC changed from being implicit and prone to error with naive_datetime to explicit and enforced at runtime with utc_datetime.

17 Likes
3

This only works for automatically generated fields like inserted_at and updated_at and if you manually set other datetime fields to DateTime.utc_now(). But if you have any other Elixir code that is not timezone-aware and it fetches time in the local timezone (or have a legacy DB with non-UTC datetimes in it) then you’re in for trouble.

Better to do all of these together:

  • Use utc_datetime type for datetime fields in your Ecto schema modules.
  • When setting times manually, always start with DateTime.utc_now() and/or do calculations with Elixir’s stdlib or timex, always making sure you use UTC.
  • If you have to work with an inherited legacy database, just make a one-off script to convert any non-UTC datetimes to UTC. It will save you a world of trouble in the future.
11 Likes
4

Definitely use :utc_datetime_*. In hindsight, we wish we made that the default in Ecto but now we can’t due to backwards compatibility (and there are no plans for Ecto 4)

Here are some resources:

29 Likes
5

Thank you all so much! this sums it up, once and for all !
So basically it’s just great that Ecto is enforcing saving DateTime values in utc with the utc_datetime, which I feel really positive about since it’s a standard development practice. I’ve seen no other library/framework enforcing it.

I made the following global changes to my Phoenix 1.5 app to use utc_datetime instead of naive_datetime by Ecto, both in schemas and migrations for anyone who may find it useful:

In config.exs, I added this:

config :myapp, MyApp.Repo, migration_timestamps: [type: :utc_datetime]

created a new schema.ex with the following code:

defmodule MyApp.Schema do
  defmacro __using__(_) do
    quote do
      use Ecto.Schema
      @timestamps_opts [type: :utc_datetime]
    end
  end
end

And in my models everywhere, I replaced use Ecto.Schema with use MyApp.Schema

And, done!

16 Likes
6

I do almost the same:

@timestamps_opts [type: :utc_datetime_usec]

You likely will have cases when you would need sub-second precision. Even if you never do I’d think this extra precision won’t cost you almost anything in terms of disk space.

4 Likes
7

I started with :utc_datetime but switched to :utc_datetime_usec just because it got annoying having to do DateTime.utc_now() |> DateTime.truncate(:second) every time. I don’t really need the precision but it seemed to be less friction for the stuff I’ve done so far.

10 Likes
8

Sounds great! then I’ll make this change in my app to get rid of this friction, nothin’ much to lose here anyway.

9

Why was the migration_timestamps change in your config.exs necessary?

10

Because IIRC having inserted_at and updated_at being at a microsecond precision is not the default.

1 Like
11

Because Ecto.SQL.Migration needs that configuration. AFAIK it is a different module from Ecto.Schema

2 Likes
12

@dimitarvp so we should override and go with…

@timestamps_opts [type: :utc_datetime_usec]

I have an application that spans timezones, how do run reports and handle timezones between locations? Pull the UTC out and then add the timezone data back to it?

13

Yes you can basically store the UTC timestamps and store timezones separately [preferably in tzdata ; ex: America/New_York]

You can use the Calendar module to apply shift operations.

3 Likes
14

You can use TzDatetime — tz_datetime v0.1.3 as a base for that.

1 Like
15

Is the Calendar module not needed, at first glance at the documents for tz_datetime it looks like it transforms it when it saves and has a function to retrieve it. As I move between locations and different timezones how would I set the timezone on save dynamically? I didn’t really see how you would set the “American/New_York” in the documents or examples.

16

How you figure out the timezone for things stored or which timezone to present data in is out of scope for the library. It only deals with the fact that timezone offsets might change while things are in the database, while making sure the db stores datetimes in utc for comparison/sorting purposes.

1 Like
17

The Calendar module is not needed, but it is my go-to for all time-related operations as the library itself is very comprehensive and has some powerful APIs.

You would have your client to notify your server in some way about it’s new timezone. In terms of web apps, it should be straightforward to do it through JavaScript, for example.
You can save that value (which is of the format America/New_York) as a String in your documents/tables.

18

It is unfortunate that this cannot be done in Ecto because of backwards compatibility concerns, but it would be nice to do this by default for all new Phoenix applications.

I started a discussion about that on the mailing list if anyone is interested in following along:

https://groups.google.com/g/phoenix-core/c/dQA8zyCv93g

8 Likes
19

Everybody agrees about what should be done on Ecto’s side (use :utc_datetime_*).

But @LostKobrakai advice is to use timestamp in the DB, while the links given by @wojtekmach seem to say otherwise (Don't Do This - PostgreSQL wiki and Time zones in PostgreSQL, Elixir and Phoenix | AmberBit Sp. z o. o.).

Is there a consensus?

20

I don’t think there can be unconditional consensus. As far as ecto is concerned timestamp and timestamptz work just the same. Ecto automatically ensures everything is in UTC. Postgres in theory shouldn’t convert anything for timestamptz, as datetimes are always utc.

Where the difference of those two come in is really how postgres behaves around other clients to the database.

1 Like