New user - timezone question

Phoenix Forum Questions / Help
,
1

hi,
i am a new user to elixir & phoenix. Current using rails.

We work in 4 timezones, and our servers & databases are already in the correct timezones. Rails just has one server setting for local timezone, which works great.

I would like to do the same in phoenix. I do not want any conversion to utc.

However all my queries are coming back with utc datetime, and I cannot find anywhere i can set a global default. Happy to have a server default for each site, but don’t want to have to do any code or changes on every query.

Any help would be appreciated.

thanks.

2

Elixir has a DateTime module which works with a time zone and UTC by default, and NaiveDateTime module, which represents a date/time without time zone.

If you don’t want your server to work in UTC (even though it is generally strongly recommended to setup server in UTC), then you have to figure out how to return NaiveDateTime structs instead of UTC DateTime structs.

Maybe share a piece of code.

3

hi Thomas, thank you for the reply.

All servers are in their correct timezone, and databases are working in those time-zones. The queries are just the simple ones generated with mix.

Repo.query(CurrentUser)

I’m just not expecting these dates to be changed from what the database is returning, which is what is happening. Very obvious for me as I am working in Sydney GMT + 10

schema "currentuser" do
    field :datecreated, :naive_datetime

Is there a global server setting? Or what is normally done whey you are not living in GMT ?

4
  1. GMT is not UTC, one has DST and leap seconds, the other hasn’t.
  2. Usually it’s common to store as UTC and convert on the presentation layer.
4 Likes
5

Or what is normally done whey you are not living in GMT ?

To emphasize what I said earlier, most people here do not live in GMT time zone but have their servers configured in UTC (it’s like GMT without daylight saving time changes).
And all time display in specific time zones would be a responsibility for the client, not the server.

So in my case, as everything is UTC, I can simply use DateTime, and I don’t face the issues you are having.

There are many reasons for setting up the database and server in UTC, but it is best to do some research on that.

Nonetheless I guess sometimes it can make sense to have the servers in the local time zone as you did…

All servers are in their correct timezone

Again, what is meant by “correct” here? If someone tells me the server is “correctly” configured, that would mean it’s configured in UTC :grin: but I guess you implied: configured in their local time zone.

6

Again, thank you for the reply.

My opinion, but I simply don’t believe it is the responsibility of the webserver to force a timezone.

This appears to be what phoenix is doing, forcing the data from an existing timezone, AEST (my timezone) back to utc. Doesn’t matter if the dates are utc or naive. I don’t accept phoenix owns this responsibility and it should have a bit more flexibility.

As the first project was changing existing rest api calls, it presents a big roadblock.

7

Phoenix doesn’t do anything in this regard, the database is ectos responsibility.

It’s behaviour follows technical limitations from what was possible with early elixir without pulling in even more additional (and controversial) dependencies, now required to remain backwards compatible, coincidentally this also follows best practices in the industry.

8

This is nothing phoenix or for the matter any other library enforces. Elixir itself does ship with a timezone database, which only knows of UTC. If you need other timezones you’ll need to install a third party timezone database. So you can either use naive datetimes and implicitly declare they’re in local time or install e.g. tzdata or tz. That’s one part.

The other is what you likely think of as phoenix, but as already mentioned it’s nothing about phoenix, but rather about ecto. ecto does two things:

  • It comes only with naive_datetime and utc_datetime native field types. This is understandable with elixir by default not supporting other timezones and given that it’s a best practice to store datetimes as timestamps in UTC into a db. If you want to store a DateTime with a different timezone (as single field) you’ll need to create a custom ecto type.
  • It autogenerates timestamps: Those can by default also only be in UTC, again because elixir by default doesn’t support other timezones. There’s no api, which could do otherwise with stock elixir. You can however override how those timestamps are generated once you’ve installed an alternative timezone db.

As you can see there is enough flexibility to do what you ask for. It does require some setup though given constraints with elixir as well as your usecase not being a best practice in the first place.

3 Likes
9

So if you use NaiveDateTime, do you have any problem now?

When I was mentioning the best practice to configure the servers/DB in UTC, it was not specifically related to Elixir/Ecto; it’s a general best practice in programming for working with time zones.

I didn’t want to sound like this is forced upon you. It’s important to share best practices among developers. Maybe you have already understood and considered the pro/cons and decided to go for the local time zone instead of UTC anyway. You can use NaiveDateTime and that should solve your problems.

10

Just curious, if a visitor from a totally unexpected timezone visits your site, what does he see for time?

In my phoenix project I always use NaiveDateTime with the unwritten assumption of it is in UTC. I pass the time to the front end and use javascript to present time in local timezone to the user.

11

The one part I should have mentioned at the beginning, this is an large existing system. Rightly or wrongly it was setup with timezone as local. However, this has worked very well, every language/tool can just use datetime no matter where they are.
I understand this goes against best practices, and we always try to encourage best practices.

naive_datetime doesn’t make any difference, all dates are changed to UTC.

I incorrectly mixed UTC & GMT, only because one of our sites is in GMT. (so use to thinking in those terms when i see the dates)

As a new user, I don’t think in terms of separation of responsibilities between phoenix & ecto. I am only thinking in terms of data coming back. I’ll ensure I tag ecto in future.

We can look into the client changes required with utc datetimes.

12

If your datetimes are in the database (Postgres?) in a timezone that is not UTC (which is what I understand from your posts) but you want those datetimes to always be interpreted in a different timezone then you might consider an Ecto custom type.

Basic steps would be:

  1. Your custom type would wrap the timestampz Postgres data type which will convert the persisted UTC time into the timezone of the server, connection or user.
  2. When receiving a data value from the database using the load/1 callback, add the timezone to the received datetime
  3. When storing the value, convert the received datetime to an appropriate zone in the dump/1 callback
  4. Implement the cast/1 callback to interpret provided data as you need it.

It probably sounds more complicated than it is - its not too difficult to implement a custom type. All you need to do is ensure that you return a valid DateTime including a timezone from the database. I don’t actually know how you decided what the timezone is since Postgres doesn’t give you any indication of that - I supposed that is derived from the server or connection somehow?

Here is a simple example of a custom ecto type in one of my libs.

3 Likes
13

If your fields in database are defined with timezone(timestamptz), you could try timex_ecto:

14

This should not happen. If you feed a NaiveDateTime into a :naive_datetime field and read it back later, it should stay the same (and naive). Can you show example code of how you insert data into the database, how you read it out, and what your DB schema looks like?

15

Apologies if i have not formatting correct.

Here is an example of reading the data in iex. This is a postgresql database and we use mariadb (galera cluster) in production.

$ iex -S mix
Erlang/OTP 23 [erts-11.0] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:1] [hipe]

Interactive Elixir (1.10.3) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> import Ecto.Query
Ecto.Query
iex(2)> Upi.Account.CurrentUser |> Upi.Repo.all
[debug] QUERY OK source="currentuser" db=12.0ms decode=1.5ms queue=1.7ms idle=194.4ms
SELECT c0."id", c0."actiondate", c0."clientversion", c0."closesession", c0."facility_fk", c0."ipaddress", c0."last_updated", c0."logindate", c0."os_version", c0."python_version", c0."usermachine", c0."username" FROM "currentuser" AS c0 []
[
  %Upi.Account.CurrentUser{
    __meta__: #Ecto.Schema.Metadata<:loaded, "currentuser">,
    actiondate: ~N[2020-08-10 04:40:14],
    clientversion: "v10 build 436",
    closesession: false,
    facility_fk: 1,
    id: 756780,
    ipaddress: "127.0.1.1",
    last_updated: ~N[2020-08-10 06:16:56],
    logindate: ~N[2020-08-10 04:40:14],
    os_version: "Linux",
    python_version: nil,
    usermachine: "mark@auMan103Mark",
    username: "mark"
  },
  %Upi.Account.CurrentUser{
    __meta__: #Ecto.Schema.Metadata<:loaded, "currentuser">,
    actiondate: ~N[2020-08-04 03:31:11],
    clientversion: "v10 build 436",
    closesession: false,
    facility_fk: 1,
    id: 756727,
    ipaddress: "192.168.2.77",
    last_updated: ~N[2020-08-10 06:16:56],
    logindate: ~N[2020-08-04 03:31:11],
    os_version: "Windows 10",
    python_version: nil,
    usermachine: "chris@auWin77Chris",
    username: "chris"
  }
]

defmodule Upi.Account.CurrentUser do
  use Ecto.Schema
  import Ecto.Changeset

  schema "currentuser" do
    field :actiondate, :naive_datetime
    field :clientversion, :string
    field :closesession, :boolean, default: false
    field :facility_fk, :integer
    field :ipaddress, :string
    field :last_updated, :naive_datetime
    field :logindate, :naive_datetime
    field :os_version, :string
    field :python_version, :string
    field :usermachine, :string
    field :username, :string

    # timestamps()
  end

The data is not written by Ecto/Phoenix.

But I just updated the last_updated column as follows, and you can see the dates in sql.

sql> update currentuser set last_updated = now();

sql> select actiondate,   last_updated, logindate   from currentuser;
          actiondate           |         last_updated          |           logindate           
-------------------------------+-------------------------------+-------------------------------
 2020-08-10 14:40:14.473904+10 | 2020-08-10 16:16:56.257595+10 | 2020-08-10 14:40:14.473915+10
 2020-08-04 13:31:11+10        | 2020-08-10 16:16:56.257595+10 | 2020-08-04 13:31:11+10
(2 rows)

sql> show timezone;
     TimeZone     
------------------
 Australia/Sydney
(1 row)
16

You are using PostgreSQL’s timestamptz type I guess? It converts the returned times to whatever the timezone of the connection is, which I guess is UTC in your case. I don’t know how to configure the connection timezone but I found an old comment with some example: https://github.com/elixir-ecto/db_connection/issues/63#issuecomment-253908710

Hope someone else who has done this can comment with a surely working example.

2 Likes
17

thank you for that reply.
I have tried it, however, it doesn’t change the output.

The link came from the following one, interesting read and certainly not a new issue.

18

Did you check the timezone was applied to the connection correctly? With something like show timezone;?

19

apologies, I don’t know where would the output for this go …

nothing is appearing on the console. I had expected it would appear on startup of mix phx.server.

20

The default Phoenix logger configuration should log all queries in dev mode. When running the show timezone; query, you can use IO.inspecton the result to print it into the console.