Improving Wetware

Because technology is never the issue

Elixir/Phoenix/Ecto, UTC in database, localtime on web page

Posted by Pete McBreen 02 Oct 2019 at 22:29

Turns out this is non-trivial, as Elixir ships with partial support for timezones, as documented by Lau Taarnskov, the creator of the Tzdata library.

First thing is to add the tzdata to mix deps

{:tzdata, "~> 1.0.1"}, 

Then have to set ecto to use utc_datetime (microseconds not needed for simple table updates)

@timestamps_opts [type: :utc_datetime] 

At this point, no matter what timezone you are in, an update will store the UTC time in the database, and show that UTC time on the web page, which although correct is not all that useful. Annoyingly querying for a timestamp in pgAdmin, will transparently shift any timestamp with time zone column to show the local time, but when displayed on the web page it will show the UTC time with a Z after the time as in “2019-10-02 21:58:15Z” rather than the local time of “2019-10-02 15:58:15”

The fix for this is to use DateTime.shift_zone/3 (shift_zone!/3 coming in Elixir 1.10), for now that code can live in the view

def format_timestamp(timestamp, time_zone) do
    |> shift_zone(time_zone)
    |> raw

defp shift_zone(timestamp, time_zone) do
   case DateTime.shift_zone( timestamp, time_zone) do 
     {:ok, dt} -> NaiveDateTime.to_string(DateTime.truncate(dt, :second)
     {:error, _dt} -> NaiveDateTime.to_string(DateTime.truncate(timestamp, :second)) <> " UTC"

And then in the template you can just use this to format the timestamp correctly for the appropriate timezone

  <%= format_timestamp(subject.updated_at, "MST7MDT") %>

My timezone is MST7MDT, but it will be better to pull it in from the user’s browser using some JavaScript and push it into the session, or have it as a configurable value on the user profile. Luckily all modern browsers now have a simple way to get the timezone from the browser…

const tz = Intl.DateTimeFormat().resolvedOptions().timeZone;

Please note that to be safe, the shift_zone function will return the UTC time if the timezone passed in is not recognized.