Handling time zones is hard in any environment I worked with. Part of the problem is that time zones are political construct rather than geographical one, and as such - they change whenever authorities make such decision. This is happening more often than you think.

To map local times between different time zones, our computers need an up to date database. This is generally provided by tzdata package (or similar) on Linux systems.

While this is fine for use of programs that by definition can rely on OS-level time zone data, many programs or programming environments decided to handle things differently. This includes Elixir - a multi platform environment.

PostgreSQL

Part of the confusion, and also source of many difficult to debug bugs, is quite unfortunate SQL types system.

By default, when you use Ecto, your database migrations will use SQL’s timestamp type. If you dare to check documentation on PostgreSQL site, you learn that timestamp is in fact “timestamp without time zone”.

What does it mean? In short: this data type will be interpreted differently, depending on the time zone settings of clients that connect to PostgreSQL server.

Ecto uses Postgrex, a database connection driver for Elxir, that is unlike many other PostgreSQL drivers out there. Instead of relying on a text protocol, it uses - more secure and performant - binary protocol.

A side effect of using binary protocol, is the fact that all timestamps are interpreted as UTC, because client’s time zone is always set to UTC.

Knowing the above, one could assume that in such case your dates in database are always kept in UTC. Right?

Wrong. While this will be true when you connect to PostgreSQL from your Elixir application, as soon as you connect using different client (psql, Ruby driver etc), the problems will start creeping in. SQL queries like:

SELECT * FROM events WHERE events.start_date < CURRENT_TIMESTAMP;

will have different meaning when you are in Europe/Warsaw time zone, and very much different when you are in PDT (California, USA), because of the 9 hour difference. If you have a reporting or maintenance script that you run against this database, depending on your client locale settings, you will miss more or less events from the query, or include unwanted ones in results. It would only be correct if your client’s settings were UTC.

How can you fix this issue? Use timestamptz data type in PostgreSQL, which is a shortcut to timestamp with time zone.

Now, the naming here is again super confusing. timestamp with time zone does not mean that the entries in the database carry over any time zone information. This type assumes that timestamp is in UTC time zone.

In my humble opinion, it’s safer to make your Elixir/Ecto application use timestamptz data type, instead of default timestamps. To do it, you should write your migrations in this form:

create table(:events) do
  add :title, :string
  add :start_date, :timestamptz
  add :end_date, :timestamptz

  timestamps(type: :timestamptz)
end

This will ensure, that whenever you run the SQL query from psql or from Elixir - your results will look the same.

Time zones handling in Elixir

Historically, Elixir (and Erlang) did not have any time zone handling, nor built in types to store this information. When Ecto was first developed, the authors came up with own data types, such as Ecto.DateTime that maps data type from database to custom type in Ecto schema, and your Elixir code.

Don’t use these types anymore. Elixir has now built-in DateTime, that you can use instead.

There is also NaiveDateTime. The difference between these two, is that “naive” version does not have the time_zone field, which means it won’t carry over any zone information.

When your database always keeps the timestamps in UTC time zone, I think it makes most sense to use the same assumption in your Ecto schemas:

schema "events" do
  ...
  field :starts_at, :utc_datetime
  field :ends_at, :utc_datetime
  timestamps(type: :utc_datetime)
end

By doing so, you can assume that all timestamps in your Ecto schemas, are always in UTC time zone. The types of values in these fields will be always DateTime with time_zone fixed to UTC, whenever you write or read it.

So what happens when you need to display the timestamp from the database to your web application users, in their local time zones?

You need some extra Elixir packages, I am afraid. Elixir does not come with many calendar functions, it only defines appropriate data structures. Detailed time zone manipulation functions are implemented by external libraries (Timex or Calendar).

The one that seems to be my choice these days, is simply called Calendar. You will need to add it in deps:

defp deps do
  [  {:calendar, "~> 0.17.2"},  ]
end

and also start it’s OTP application:

def application do
  [applications: [:calendar]]
end

Behind the scenes, Calendar relies on tzdata package, which provides database of time zones. Moreover, it periodically checks for changes in time zones, and updates it’s local database to reflect those changes. Pretty sweet.

Phoenix and the web users

When I want to display timestamps converted to local time zone for my users, I tend to stick to creating a boundary between my Elixir code and HTML / form data. The assumption is that anything in controllers and below, will have dates in UTC for simplicity. Whatever the user sees and sends back to the server - may be in their local time zone.

Simple view helper to display timestamps in user’s local time zone could be as follows:

def format_timestamp(nil) do
  nil
end

def format_timestamp(timestamp, time_zone) do
  timestamp
  |> shift_zone!("Europe/Warsaw")
  |> Calendar.Strftime.strftime!("%d/%m/%Y %H:%M")
end

defp shift_zone!(nil, time_zone) do
  nil
end

defp shift_zone!(timestamp, time_zone) do
  timestamp
  |> Calendar.DateTime.shift_zone!(time_zone)
end

I have also hacked together a mechanism for my users to be able to specify time zone when they add timestamps on the forms. In our example, Event has start and end dates. My users can specify time zone information using this amended version of standard datetime_select, that accepts the same arguments and behaves similar - yet has an extra dropdown for time zone (that can be replaced with hidden field). The code for the helper:

def date_time_and_zone_select(form, field, opts \\ []) do
  time_zone = Keyword.get(opts, :time_zone) || "Etc/UTC"

  value = Keyword.get(opts, :value, Phoenix.HTML.Form.input_value(form, field) || Keyword.get(opts, :default))
          |> shift_zone!(time_zone)


  default_builder = fn b ->
    ~e"""
    <%= b.(:year, []) %> / <%= b.(:month, []) %> / <%= b.(:day, []) %>
    —
    <%= b.(:hour, []) %> : <%= b.(:minute, []) %>
    <%= b.(:time_zone, []) %>
    """
  end

  builder = Keyword.get(opts, :builder) || default_builder

  builder.(datetime_builder(form, field, date_value(value), time_value(value), time_zone, opts))
end

@months [
  {"January", "1"},
  {"February", "2"},
  {"March", "3"},
  {"April", "4"},
  {"May", "5"},
  {"June", "6"},
  {"July", "7"},
  {"August", "8"},
  {"September", "9"},
  {"October", "10"},
  {"November", "11"},
  {"December", "12"},
]

map = &Enum.map(&1, fn i ->
  pre = if i < 9, do: "0"
  {"#{pre}#{i}", i}
end)

@days   map.(1..31)
@hours  map.(0..23)
@minsec map.(0..59)

defp datetime_builder(form, field, date, time, time_zone, parent) do
  id   = Keyword.get(parent, :id, input_id(form, field))
  name = Keyword.get(parent, :name, input_name(form, field))

  fn
    :year, opts when date != nil ->
      {year, _, _}  = :erlang.date()
      {value, opts} = datetime_options(:year, year-5..year+5, id, name, parent, date, opts)
      select(:datetime, :year, value, opts)
    :month, opts when date != nil ->
      {value, opts} = datetime_options(:month, @months, id, name, parent, date, opts)
      select(:datetime, :month, value, opts)
    :day, opts when date != nil ->
      {value, opts} = datetime_options(:day, @days, id, name, parent, date, opts)
      select(:datetime, :day, value, opts)
    :hour, opts when time != nil ->
      {value, opts} = datetime_options(:hour, @hours, id, name, parent, time, opts)
      select(:datetime, :hour, value, opts)
    :minute, opts when time != nil ->
      {value, opts} = datetime_options(:minute, @minsec, id, name, parent, time, opts)
      select(:datetime, :minute, value, opts)
    :second, opts when time != nil ->
      {value, opts} = datetime_options(:second, @minsec, id, name, parent, time, opts)
      select(:datetime, :second, value, opts)
    :time_zone, opts ->
      {value, opts} = timezone_options(:time_zone, parent[:zones_list] || Tzdata.zone_list(), id, name, time_zone, opts)

      if parent[:hide_time_zone] == true do
        hidden_input(:datetime, :time_zone, Keyword.merge(opts, [value: time_zone]))
      else
        select(:datetime, :time_zone, value, opts)
      end
  end
end

defp timezone_options(type, values, id, name, time_zone, opts) do
  suff = Atom.to_string(type)
  {value, opts} = Keyword.pop(opts, :options, values)

  {value,
    opts
    |> Keyword.put_new(:id, id <> "_" <> suff)
    |> Keyword.put_new(:name, name <> "[" <> suff <> "]")
    |> Keyword.put_new(:value, time_zone)}
end


defp datetime_options(type, values, id, name, parent, datetime, opts) do
  opts = Keyword.merge Keyword.get(parent, type, []), opts
  suff = Atom.to_string(type)

  {value, opts} = Keyword.pop(opts, :options, values)

  {value,
    opts
    |> Keyword.put_new(:id, id <> "_" <> suff)
    |> Keyword.put_new(:name, name <> "[" <> suff <> "]")
    |> Keyword.put_new(:value, Map.get(datetime, type))}
end

defp time_value(%{"hour" => hour, "minute" => min} = map),
  do: %{hour: hour, minute: min, second: Map.get(map, "second", 0)}
defp time_value(%{hour: hour, minute: min} = map),
  do: %{hour: hour, minute: min, second: Map.get(map, :second, 0)}

defp time_value(nil),
  do: %{hour: nil, minute: nil, second: nil}
defp time_value(other),
  do: raise(ArgumentError, "unrecognized time #{inspect other}")

defp date_value(%{"year" => year, "month" => month, "day" => day}),
  do: %{year: year, month: month, day: day}
defp date_value(%{year: year, month: month, day: day}),
  do: %{year: year, month: month, day: day}

defp date_value({{year, month, day}, _}),
  do: %{year: year, month: month, day: day}
defp date_value({year, month, day}),
  do: %{year: year, month: month, day: day}

defp date_value(nil),
  do: %{year: nil, month: nil, day: nil}
defp date_value(other),
  do: raise(ArgumentError, "unrecognized date #{inspect other}")

end

This results in form sending an extra parameter when is being submitted, in timestamp fields. Instead of

%{"year" => "2017", "month" => "5", "day" => "1", "hour" => "12", "minute" => "30"}

server receives from form submit extra time_zone parameter:

%{"year" => "2017", "month" => "5", "day" => "1", "hour" => "12", "minute" => "30", "time_zone" => "Europe/Warsaw"}

I created a simple plug, that recursively walks through all the parameters, detects those custom 6-element maps with timestamps, and replaces them with Elixir’s native DateTime, properly shifted to UTC time zone. The complete code is:

defmodule ShiftToUtc do
  @behaviour Plug

  def init([]), do: []

  import Plug.Conn

  def call(%Plug.Conn{} = conn, []) do
    new_params = conn.params |> shift_to_utc!()

    %{conn | params: new_params}
  end

  defp shift_to_utc!(%{__struct__: mod} = struct) when is_atom(mod) do
    struct
  end

  defp shift_to_utc!(%{"year" => year, "month" => month, "day" => day, "hour" => hour, "minute" => minute, "time_zone" => time_zone} = map) do
    {year, _} = Integer.parse(year)
    {month, _} = Integer.parse(month)
    {day, _} = Integer.parse(day)
    {hour, _} = Integer.parse(hour)
    {minute, _} = Integer.parse(minute)

    second = case Map.get(map, "second", 0) do
      0 -> 0
      string ->
        {integer, _} = Integer.parse(string)
        integer
    end

    {{year, month, day}, {hour, minute, second}}
    |> Calendar.DateTime.from_erl!(time_zone)
    |> Calendar.DateTime.shift_zone!("UTC")
  end

  defp shift_to_utc!(%{} = param) do
    Enum.reduce(param, %{}, fn({k, v}, acc) ->
      Map.put(acc, k, shift_to_utc!(v))
    end)
  end

  defp shift_to_utc!(param) when is_list(param) do
    Enum.map(param, &shift_to_utc!/1)
  end

  defp shift_to_utc!(param) do
    param
  end
end

To use it, add it to the controllers you want, or to the browser pipeline in your router.ex.

The alternative approach would be to write a custom Ecto type, that’d shift time zone before persisting record to database. An example of such custom Ecto type, kindly provided by Michał Muskała is presented below:

defmodule ZonedDateTime do
  @behaviour Ecto.Type

  def type, do: :utc_datetime

  def cast({"time_zone" => time_zone} = map) do
    with {:ok, naive} <- Ecto.Type.cast(:naive_datetime, map),
         {:ok, dt} <- Calendar.DateTime.from_naive(time_zone) do
      {:ok, Calendar.DateTime.shift_zone!(dt, "Etc/UTC")}
    else
      _ -> :error
    end
  end
  def cast(value), do: Ecto.Type.cast(:utc_datetime, value)

  def dump(value), do: Ecto.Type.dump(:utc_datetime, value)

  def load(value), do: Ecto.Type.load(:utc_datetime, value)
end

Timestamps with special needs

While the approach above works for me and my users, you may have slightly different needs. Most notable case, in my opinion, is when you want to preserve the time zone information that the user specified.

For example, you may want to save the selected time zone in events table. How can you approach this?

While Elixir’s types would allow doing that, PostgreSQL does not by default. The general approach would be to store extra start_date_time_zone column in your events table.

Luckily, we don’t have to do it manually. There’s Calecto library out there, that provides a way to use :calendar_datetime primitive type in your migrations. Behind the scenes, it creates a compound PostgreSQL type, that stores information about timestamp and time zone. Whenever you save information to database that contains timestamp with zone information, the same timestamp with the same zone information will be returned later, when you use the schema to read it.

This is especially useful when you want to ensure that Event’s start_date will not change in local time zone, despite time zone changes. This may matter if your users are in Turkey or Venezuela, or the dates are generally far in the future.

The other special case are hours of day. PostgreSQL has dedicated type of time (with and without timzeone - of course!) to store information like opening hours of shops etc. When you store that information in UTC in database, you may find yourself in surprising situation if your users live in countries that use daylight saving time. In such cases you must preserve the zone information, and ensure you display the information correctly.

Summary

There is a lot of confusion about which libraries and data types to use when you write Elixir applications. The simplest approach that works for me is to:

use timestamptz in PostgreSQL
use :utc_datetime in Ecto schemas, which maps to Elixir’s native DateTime with zone set to UTC
convert the timestamps to local zone when displaying to user
write custom date/time/zone select to replace the standard one that comes with Phoenix
use custom plug to detect form parameters that carry over zone information, and shift them to UTC before passing on to controller/changeset

Post by Hubert Łępicki

Hubert is partner at AmberBit. Rails, Elixir and functional programming are his areas of expertise.

Time zones in PostgreSQL, Elixir and Phoenix

PostgreSQL

Time zones handling in Elixir

Phoenix and the web users

Timestamps with special needs

Summary

Post by Hubert Łępicki

Leave a comment...

Contact us

Want to get in touch about a project or say 'Hello'? Drop us a line!