OK maybe I’m just total idiot but I can’t figure this out. I have two projects with identical configs in config.exs regarding ecto. Schema and Repo are also defined the same way. One is project created long time ago and upgraded from Phoenix 1.5 to 1.6. The other is generated by 1.6. I’m doing migrations on clean database.
For some reason new one ignores completely :utc_datetime_usec in migrations. In both manually defined fields and timestamps defined in config. It generates just timestamp(0) field in Postgres and that’s it. No matter what I do it keeps ignoring my preferences.
New project works as is reasonable to expect. :utc_datetime_usec generates timestamp with timezone. naive_datetime is again timestamp with timezone. Not that I would ask for anything else but how is this happening? Did I misconfigure something? Is it a problem with new version?
It’s a sideproject and I burned two evenings googling for answers but I can’t find solution. Unfortunately I don’t have logs from migration because I don’t know how to get those. Setting log level to debug didn’t work.
I dropped database and created a new one. I don’t use mix ecto.drop but standard SQL drop database in a shell script that runs also mix ecto.create and mix ecto.migrate. I’ve did this multiple times, just to make sure I’m doing the right thing.
So is the new one working or not?
Can you show us your config and your MyApp.Schema module? Have you tried nuking the DB and creating it from scratch with mix ecto.drop?
Well I want the behaviour from old project. So in Postrgres I want to see timestamp with timezone. But it would be nice to know why it just ignores differences between utc_datetime and naive_datetime.
I think I didn’t make myself clear. I’ve edited it in first post. I dropped database and created a new one. I don’t use mix ecto.drop but standard SQL in a shell script that runs mix ecto.create and mix ecto.migrate. I’ve did this multiple times, just to make sure I’m doing the right thing.
This is the schema in both projects. Don’t worry about the ULID stuff that’s working as expected.
defmodule App.Schema do
defmacro __using__(_env) do
quote do
use Ecto.Schema
@timestamps_opts [type: :utc_datetime_usec, autogenerate: {DateTime, :now!, ["Etc/UTC"]}]
@primary_key {:id, App.Schema.ULID, autogenerate: true}
@foreign_key_type App.Schema.ULID
import Ecto.Changeset
import Ecto.Query
end
end
end
I think you can replace {DateTime, :now!, ["Etc/UTC"]} with {DateTime, :utc_now, []}.
I don’t think using :utc_datetime changes the Postgres type. I think you’d need to explicitly use a type with TZ information in the migraiton. Can you please check if this is the case in the old app? What type is it using in the migrations?
I found the culprit! I’ve checked everywhere but the migrations. I thought this was done in a more elegant way than plain old raw sql in a migration. There is one place in the old project where this migration is missing and sure enough that’s where there is old timestamp(0) in the DB. repo().query!("ALTER TABLE users ALTER COLUMN inserted_at TYPE timestamptz")
Now the question is can this be done in a migration without raw SQL involved?
defmodule MyApp.Repo.Migrations.CreateReports do
use Ecto.Migration
def change do
create table(:reports) do
add :name, :string, size: 64
add :from, :timestamptz
timestamps()
end
end
end
Thanks cmo for answer. I just didn’t pick his solution because I wanted to extend it to microseconds as well as default timestamps.
So for timestamp with timezone it’s enough to use timestamptz as a field type in migration. If more precision is needed size: x can be added to add more precision.
To achieve the same thing with default timestamps add this to your config. Rememeber, that size is optional if subsecond time precision is not desired. config :app, App.Repo, migration_timestamps: [type: :timestamptz, size: x]
This is unintuitive. In Ecto schema utc_datetime or utc_datetime_usec needs to be defined not timestamptz. That is also true for @timestamps_opts which can be defined in custom schema to make it available in all models with simple import.
This is an opinionated choice made by the Ecto team years ago, it’s not a recent change. Personally I don’t like using these “friendly names” of types in migrations, I would prefer using the database types directly for clarity.
That link is exactly what I was unsuccessfully looking for when I tired to figure out what was going on. I don’t like these either because in Django they have completely different names and by default those fields are timestamptz. Well I guess I need to learn these names all over again.
