Why can't `:timestamptz` be set up as default timestamp for migrations in config?

Questions / Help
,
22

I hear you - I did mention timestamptz, although my real issue is the two incompatible DateTime types.

Yep! That’s what I landed on in the timestamps() call, but I wish I didn’t have to remember with a fresh Phoenix app.

It looks like your monkey patch will only need to be a one-time fix?

My preference, personally, would be for this major use case (Postgres) to “just work” without needing to remember manual adjustments.

24

Btw, I double checked, and Phoenix also supports this:

config :my_app,
  generators: [timestamp_type: :utc_datetime]

So using the generators will include type: :utc_datetime automatically. I believe this is the default for fresh Phoenix v1.8+ apps.

25

Excellent - thank you for the extra time checking - I really appreciate it.

26

Ya, I always make a MyApp.Schema and add the :generators config.

It would be nice if this could be included in phx_new, at least as an option.

$ mix phx.new cool_app --timestamps utc_datetime_usec

(apologies if this was already suggested and rejected in this thread years back, I didn’t go over it again)

1 Like
27

I’ll chime in, because this is a subject that always annoyed me about how it was handled by default and I need to manually change things to my taste (I’m using PostgreSQL).

First, I want my timestamps to be timestamptz in the DB. Forget that it’s badly named (confusion about timezone), it is the correct type to represent a “point in time”. If your DB uses timestamp (without tz), it’s ambiguous and someone needing to interface with the DB outside your app won’t have the slightest clue what they represent (is it local time (ouch…) or UTC?). Using the correct type makes confusion impossible.

Anyway, most of the things I change have been mentioned in this thread.

Make sure migration timestamps are timestamptz by default and have a default, which makes it easier to insert rows in DB manually for testing. It’s important that the default timezone is set to UTC (it could be set in the DB, but I prefer to have my system work either way. It’s important because Ecto was doing some casting where where: token.inserted_at > ago(^days, "day") was converted to SQL inserted_at > $2::timestamp + (interval ...) and the only this conversion works correctly is if the default timezone is UTC.

config :my_app, MyApp.Repo,
  migration_primary_key: [type: :identity],
  migration_timestamps: [type: :timestamptz, default: {:fragment, "now()"}],
  after_connect: {Postgrex, :query!, ["SET TimeZone TO 'UTC';", []]}

As already mentioned, I have a custom schema:

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

I don’t use the generators much, but still I have a private copy of the phx.gen.schema templates.
schema.ex, make it use my schema:

defmodule <%= inspect schema.module %> do
  use <%= hd(Module.split(schema.repo)) %>.Schema

migration.exs, make sure fields with utc_datetime and utc_datetime_usec are converted correctly to timestamptz, and at the same time make sure text is always used because I can’t stand the sight of varchar(255):

<%
internal_type_mapper = fn
  :string -> :text
  :utc_datetime_usec -> :timestamptz
  other -> other
end

type_mapper = fn
  :utc_datetime -> ":timestamptz, size: 0"
  {:array, :utc_datetime} -> "{:array, :timestamptz}, size: 0"
  {:array, subtype} -> inspect({:array, internal_type_mapper.(subtype)})
  other -> inspect(internal_type_mapper.(other))
end
%>defmodule <%= inspect schema.repo %>.Migrations.Create<%= Macro.camelize(schema.table) %> do
...
... <%= type_mapper.(Mix.Phoenix.Schema.type_for_migration(v)) %> ...
7 Likes
28

That an interesting footgun. While there’s no transformation of timestamptz values based on the session timezone on the wire protocol – vs the text based protocol, which converts timestamptz values to the session timezone – it seems timestamp values will use the session timezone when getting compared to timestamptz values. Seems like any comparisons between timestamptz and timestamp would be problematic like that.

1 Like
29

Another reason for having sane defaults, and if that is not possible, slam a big disclaimer on it.

In most enterprise settings, then the database might be accessed by others then the developer and his Ecto application.

3 Likes
30

I was also surprised to find that the default is not timestamptz for created_at, inserted_at, and other utc_datetime_* fields.

timestamptz is the equivalent of :utc_datetime_*, while timestamp is the equivalent of :naive_datetime.

Postgres Ecto JS Semantic Meaning
timestamptz :utc_datetime_* Temporal.Instant an instant on the universal timeline
timestamp :naive_datetime Temporal.PlainDateTime a calendar date and wall-clock time, e.g., future scheduled events at a physical location

This has implications when writing queries that perform timestamp math on the DB side, as well as sharing your DB with other clients. Dealing with time zones is confusing enough as it is, one small way to help is by pushing engineers towards using the correct timestamp types in their DBs.