Why isn't Phoenix using `utc_datetime` and `timestamptz`?

timestamps
Just in case you missed it, there’s a search box on the top left of all elixir documentation.

No idea, I always use timestamptz.

In migrations: timestamps(type: :timestamptz)

In schemas: timestamps(type: :utc_datetime_usec)

What are some downsides of using timestamptz or utc_datetime_usec?

Thanks. I haven’t noticed. It’s too dark. They should make the input box with white background.

Current state:

image721×428 21.8 KB

VS

My proposal:

image721×428 21.1 KB

Are you aware that there is a search box on this forum too, and one of the Google homepage?

You can change hexdocs to light theme at the bottom of the page.

I made the white background input box. It is not part of theToggle night mode. Toggle night mode is changing only the color of the main content, sidebar is unchanged. Try it for yourself, if you don’t believe me

One of the answers in that linked StackOverflow post points to an official Postgres FAQ that has a decent-sounding reason:

(in response to "Don’t use timestamp without TZ to store UTC"):

When should you?

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

Ecto already converts values to UTC, and needs to support other databases that don’t have timestamp with time zone.

I don’t see any, which is why I always use it. timestamptz is the postgres type, utc_datetime_usec is the corresponding ecto type which maps to the DateTime elixir type.

Please use the search facilities of this forum, just a search for timestamptz returns several topics that have details about this.

The reason why Ecto’s timestamps() are naive by default is backwards compatibility. Back when Ecto was created, Elixir didn’t even have datetime types and Ecto didn’t have timezone support.

Also quoting my earlier answer in an earlier topic:

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. It does not even store the offset/timezone. So you need to do that yourself somehow.

Personally I don’t find much use in timestamptz, but that’s up to everyone to decide for themselves. It’s easy to override this in your migrations. In your schema you would use something like timestamps(type: :utc_datetime) and in migration timestamps(type: :timestamptz).

If you’re using just Elixir and your application is the only one connecting to the database, then it doesn’t make a difference if you use timestamp or timestamptz. If you use other applications to also connect to the same database, timestamptz will have different implications.