How are dates stored in postgres via Ecto (using Timex)?

Questions / Help
1

In my phoenix application, I’ve been using Ecto with Timex to store my dates in postgres. When I show results to the end user, I show days in reverse order (i.e. today is at the top of the list) and then sort results in each day chronologically.

For this to work properly, I need to apply the users timezone before processing the sort. What I’ve been doing (until now) is retrieve the data, converting the dates using Timex and then run the sorting client-side. For example, I might do something like this:

tz_user = Timex.Timezone.get( "Australia/Hobart", Timex.now )

captains_log = CaptainsLog.list_logs_for_user( current_user )

days = captains_log
|> Enum.map( fn (log) -> %Log{ log |
  star_date: Timex.Timezone.convert( log.star_date, tz_user ),
  end_transmission: Timex.Timezone.convert( log.end_transmission, tz_user )
} end)
|> Enum.group_by( fn ( log ) -> Timex.to_date(log.end_transmission) end )
|> Enum.sort( fn (e1, e2) ->
  { date1, _ } = e1
  { date2, _ } = e2

  Timex.compare( date1, date2 ) >= 0
end)

However, now I’d like to achieve this server-side. If I do something like the following:

select end_transmission at time zone 'AEST' from captains_log;

The date is not adjusted for my time zone (as I had assumed would be the case). For example, “8:48am” appears as “11:48am”. AEDT isn’t much better, “8:48” appears as “10:48am”.

So how are dates/times stored in postgres by Ecto? Can I write a server-side query that adjusts to the desired timezone? The timezone for each user may be different, what can I pass up to postgres so I can make the query work for different time zones?

2

How does your migration look like and your schema?

Also how are you storing the date into the database? Especially the 8:48 you use in your example, how did that got into the database?

3

Here’s what my schema looks like:

defmodule Zoinks.CaptainsLog.Log do
  use Ecto.Schema
  import Ecto.Changeset
  alias Zoinks.CaptainsLog.Log

  schema "captains_log" do
    field :star_date, Timex.Ecto.DateTime
    field :end_transmission, Timex.Ecto.DateTime
    field :comment, :string
    belongs_to :user, Zoinks.Accounts.User

    timestamps()
  end

  @doc false
  def changeset( %Log{} = log, attrs ) do
    log
    |> cast( attrs, [:star_date, :end_transmission, :comment, :user_id] )
    |> validate_required( [:star_date, :user_id] )
    |> foreign_key_constraint(:user_id)
  end
end

And here’s my migration:

defmodule Zoinks.Repo.Migrations.CreateLog do
  use Ecto.Migration

  def change do
    create table(:captains_log) do
      add :star_date, :datetime
      add :end_transmission, :datetime
      add :comment, :text
      add :user_id, references(:users, on_delete: :nothing)

      timestamps
    end
    create index(:captains_log, [:user_id])

  end
end

In the case of creating a new log, end_transmission is set using Timex.now - just before it is inserted via Ecto. In the case of the “8:48am” example (above), if I run the following on postgres:

select end_transmission from captains_log where id = 4882;

The date it shows me is “2018-02-28 21:48:22.896673”. If I do the same thing, but with a time zone:

select end_transmission at time zone 'AEST' from captains_log where id = 4882;

The result is “2018-02-28 11:48:22.896673+00”. However, if I use Ecto to retrieve the record and modify the date using:

Timex.Timezone.convert( log.end_transmission, tz_user ) |> Timex.format!("{h24}:{0m}")

Then I see “08:48”. Which is confusing, if Ecto is storing dates in postgres using UTC, then shouldn’t the select statement with the at time zone clause work?

Of course, there are other things going on - when creating a log, star_date is set to the end_transmission of the previous record. Then there’s editing - star_date/end_transmission is displayed using the users local timezone. Users can edit and save these dates. Without going into details, all of this has been working without any hiccups. It wasn’t until I tried to format the dates using postgres that things started to get confusing.

4

What is the timezone set in your postgresql.conf?

5

I don’t have access to the postgresql.conf, but I did the following:

show timezone;
 TimeZone 
----------
 UTC
(1 row)
6

The “at time zone” behave differently depending on the data type of end_transmission
I believe the data type of end_transmission is “timestamp without time zone”.

If end_transmission is a timestamp without time zone, when you run:

select end_transmission at time zone ‘AEST’ from captains_log where id = 4882;

end_transmission is treated as located in AEST and it return the UTC equivalent.

if you convert end_transmission to a timestamp with UTC time zone first, only then it will convert between time zones (in you case from UTC to AEST):

select end_transmission::timeztamptz at time zone ‘AEST’ from captains_log where id = 4882;

PostgreSQL Reference

7

Awesome! Applying ::timestamptz resolved the issue for me! Thanks!

8

So slightly different problem. On my dev machine, I run the following query:

select 
  end_transmission, 
  end_transmission::timestamptz at time zone 'Australia/Hobart' 
from 
  captains_log where id = 202;

It gives the following result:

      end_transmission      |          timezone          
----------------------------+----------------------------
 2017-10-13 22:51:59.443831 | 2017-10-13 22:51:59.443831

Please bare in mind that the default timezone on my dev machine is “Australia/Hobart” (in the examples above this post, this was on my production server (Google Cloud) and the default timezone was “UTC”).

However, when interpreted by Ecto/Timex the date displayed is “2017-10-14 09:51”. I’m assuming this is something to do with how the date is inputted. Am I correct in assuming that Ecto/Timex is not taking into account timezone information when it stores data, then postgres assumes the default timezone when storing the date?

If so, am I better off setting my default timezone in postgres to UTC?

9

Yes, set postgres default timezone to UTC and then convert it to the desired timezone for display.

2 Likes
10

Hi, I am new and learning to deal with user timezone currently and bump into this topic. There are few things I am trying to understand here, I put the as comment in the code above.

I have read the docs, but i am not sure if I understood correltly.

Thanks in advance :slight_smile:

11

So basically Timezone.get returns the Timezone object. With a Timezone object you can convert a timestamp between time zones. Why? Because time zones are weird and they change all the time.

For example, there’s Daylight Savings. The start/end date of Daylight Savings may change from year to year, per location.

So yes, Timezone is an object that understandable by Timex. But ultimately it’s a key to a custom database that returns the correct conversion given the supplied location/time.

Hope that helps?

1 Like
12

By the way, I found this presentation entertaining and enlightening:

Here’s the accompanying article:

13

Sorry for the late reply. Yes it does help a lot. This short answer answers more question for me than reading through Timex docs. :heart_eyes:

1 Like