ArgumentError reading :utc_datetime via Ecto.Schema

[dfrese]

Hi there,

I used 'timestamps' in an Ecto Schema with type :utc_datetime, and the corresponding table created via a migration also has :utc_datetime as the type for the field.

But reading it fails with

 ** (ArgumentError) :utc_datetime expects the time zone to be "Etc/UTC", got `#DateTime<2023-10-10 12:56:30.000000+00:00 UTC +02:00>`
...
(ecto 3.10.3) lib/ecto/type.ex:1362: Ecto.Type.check_utc_timezone!/2

Changing time_zone from to_binary(tz) to "Etc/UTC" in jamdb_oracle.ex fixed it, although that's probably not the "proper" fix:

  defp to_date({{year, month, day}, {hour, min, sec}, tz}),
    do: %DateTime{year: year, month: month, day: day, hour: hour, minute: min,
	second: trunc(sec), microsecond: parse_sec(sec), time_zone: "Etc/UTC",
	zone_abbr: "UTC", utc_offset: 0, std_offset: 0}

[vstavskyi]

Ecto type is :utc_datetime, but Oracle type is TIMESTAMP(0) WITH TIME ZONE
So, stored value must be AT TIME ZONE 'Etc/UTC'

insert into xxx (uuu) values (SYSTIMESTAMP AT TIME ZONE 'Etc/UTC')
not
insert into xxx (uuu) values (SYSTIMESTAMP)

If not, then convertion required
Jamdb.Xxx |> select([r], fragment("? AT TIME ZONE 'Etc/UTC'", r.uuu)) |> Jamdb.Repo.all

The idea was that
in application you have date in localtime or utctime or any time format
then you convert it to utc and store to database

[dfrese]

I see. But I did not insert the timestamp explicitly; this is done automatically - I think by Ecto - when using https://hexdocs.pm/ecto/Ecto.Schema.html#timestamps/1

In my case like so:

schema "mytable" do
      timestamps(
        inserted_at: :timestamp,
        type: :utc_datetime,
        inserted_at_source: :timestamp,
        updated_at: false
      )

Would be really nice if this would work out-of-the-box.

I'm not sure if Ecto uses a Database-Feature, or if it takes the application's current time.

In the to_date function above, tz is '+02:00' in my case.
Maybe that needs to be parsed into utc_offset?

[vstavskyi]

  defp encode(%DateTime{microsecond: {0, 0}, zone_abbr: "UTC"} = datetime)  do
    {date, {hour, min, sec}} = NaiveDateTime.to_erl(DateTime.to_naive(datetime))
    {date, {hour, min, sec, 0}, 28}
  end  
  defp encode(%DateTime{microsecond: {ms, _}, zone_abbr: "UTC"} = datetime) do
    {date, {hour, min, sec}} = NaiveDateTime.to_erl(DateTime.to_naive(datetime))
    {date, {hour, min, sec, ms}, 28}
  end

Now stored value tz is UTC +00:00

got #DateTime<2023-10-11 16:01:08.000000+00:00 UTC +00:00>

[vstavskyi]

fixed

[vstavskyi]

Add parse_offset
got #DateTime<2023-10-15 10:20:23+03:00>

fixed

[dfrese]

Thanks!