Ecto, Phoenix and utc datetime

Phoenix Forum Questions / Help
,
#1

Hello,

I have a Phoenix app using Html.Form datetime_select
I fill user input using Timex zone convert in Europe/Tallinn (UTC+2):

59
Screen Shot 2018-12-07 at 20.46.59.png704×146 5.15 KB

Saved result is same as input

10

Shouldn’t it be saved as UTC, since all times are UTC in db?

field(:planned_start_at, :utc_datetime)

Q: Where should this convert to UTC? Should i create custom Ecto.Type for this? or in changeset before insert? or can Ecto handle it itself?

inserted_at and updated_at are correctly in UTC
Phoenix 1.4, Ecto 3.0, Postgres

Thanks,

Why does Phoenix use naive datetime for timestamps by default?
#2

As I recall :utc_datetime stores timezone information where :naive_datetime does not and stores things as utc always. I think… ^.^;

Regardless, datetimes in the future you want to store with timezone information. Timezones change all the time and if someone plans something on, say, March 18th at 5pm, and daylight savings starts on March 20th but then that gets changed to start on March 12th then if you stored the datetime as utc in the database then you are off by an hour where if you stored it with the proper timezone then it will still be correct. :slight_smile:

Past times are of course fine to store timezoneless.

1 Like
#3

It does not. Both just store a timestamp, but :utc_datetime does check at runtime that the timezone is UTC. The naive datetime is just stored as is.

#4

Which if you are storing future dates is not what you want regardless. ^.^

#5

When casting a non-UTC %DateTime{} to :utc_datetime it’s gonna be converted to UTC timezone as we can do this safely. After casting it’s all UTC.

As far as storing future date times in UTC that’s indeed dangerous. See @Lau’s excellent blog posts: http://www.creativedeletion.com

4 Likes
#6

Thanks for replies,
current problem is that posting value from Phoenix.HTML.Form datetime_select in non-UTC timezone will put same value to database.

  1. Should i convert user input datetime to UTC in changeset? or custom Ecto.Type?

i have conn.assigns[:timezone] =“Europe/Tallinn” available, i wonder what is the correct way to go.

Changeset before posting, prefilled form (Timex Datetime)

changes: %{
    planned_start_at: #DateTime<2018-12-08 20:03:47.432869+02:00 EET Europe/Tallinn>
  },

During insert

 changes: %{
    planned_start_at: #DateTime<2018-12-08 20:03:00Z> << 2 hours wrong
  },
#7

If you are sure your visitors will always send a timezone parameter you might as well do the transormation in the browser and always assume that your backend receives UTC date/times. Or you can transform the date/time to UTC inside your users’ browsers if a timezone parameter is not always present.

This is a good SO thread about detecting timezone in the browser.

#8

I will provide my solution here. So i created custom Ecto.Type which will receive local datetime and timezone. And that will convert it to utc.

  @behaviour Ecto.Type
  def type(), do: :utc_datetime
..
  def cast(%{"datetimelocal" => datetimelocal, "tz" => tz}) do
    case Timex.parse(datetimelocal, "{ISO:Extended}") do
      {:error, _error} ->
        Ecto.Type.cast(type(), nil)
      {:ok, value} ->
        Ecto.Type.cast(type(), Timex.to_datetime(value, tz))
    end
  end
  ..
  def cast(data), do: Ecto.Type.cast(type(), data)
2 Likes