I’m experienced Django developer and I wanted to try Phoenix. In Django time zones work like magic. Just set config variable or request variable and it works out of the box. No problem with that.
But in Phoenix it’s a pain to work with time. It seams like I’m missing something.
So here is what I tried so far. Ecto does not use timestamptz as a data type for storing datetimes in DB. Which means this query gives exact opposite answer to you if run with timestamp vs. with timestamptz. Only way out seams to be unmaintained library timex_ecto.
SELECT date_ended AT TIME ZONE 'Europe/Prague',
date_ended AT TIME ZONE 'Europe/London',
date_ended AT TIME ZONE 'UTC',
date_ended AT TIME ZONE 'Asia/Tokyo',
date_ended AT TIME ZONE 'America/New_York'
FROM time_entries;
ALTER TABLE time_entries ALTER COLUMN date_ended TYPE timestamptz;
To make timestamps more useful I wanted to use :utc_datetime_usec. This was possible by placing this snippet in every single schema I have. This is tedious and I think there is/should be a better way but I could not find it in docs. Migrations are OK, because that can be set via config. Doc link
Finally UI representation. Since I’m not able to query data in desired timezone I need to change timezone in Elixir.
All of this could be solved in some hacky solution which would sorta work but I would not trust. DB types can be changed manually in migrations. I can write custom SQL or use query set timezone. All of these solutions seam like a really bad idea and there must be a better way. Even if the way is to write my own library.
There are various cases where this becomes an issue, such as setting the primary key type to something other than the standard defaults available. What I tend to do in these cases is create a module like this:
defmodule MyApp.Schema do
defmacro __using__(_env) do
quote do
use Ecto.Schema
@primary_key {:id, Pointers.ULID, autogenerate: true}
@foreign_key_type Pointers.ULID
import Ecto.Changeset
import Ecto.Query
end
end
end
and then replace the use Ecto.Schema line with use MyApp.Schema in all my schemas. Allows fiddling with these things in one place and have them take effect globally.
As for storing with or without timezones, in postgresql timestampts with timezones are always stored as UTC and shifted when returned, and getting timezones “right” is often tricky … it’s generally a better idea to store everything as UTC and then do whatever TZ magic desired for display only.
While timestamp is indeed the default column type nothing in ecto prevents you from instead using timestamptz in migrations. Neither column type actually stores the timezone. timestamptz just is automatically converted from UTC to the timezone of connected session. Given postgrex is using the binary protocol of postgres there won’t be any difference anyways given it defaults to UTC as timezone (not sure if that’s even changable). So Ecto essentially doesn’t care which one you use.
You could create a custom ecto type to setup a schema field to use a different timezone similar to :utc_datetime, but enforcing a different timezone than UTC.
Ecto doesn’t ship with something like that likely for a few reasons:
One is that UTC is the only timezone guaranteed to be known. For any other timezone you need a third party timezone db.
The other is that it’s generally best practise to keep timezones out of your business logic anyways. Most people treat converting datetimes to users timezones as a presentational concern, while everything else works with UTC only. :utc_datetime is the type for that.
I already have TZ library. Timezones change quite frequently so even in Python tz database is a library.
My main concern is date aggregation. It’s pretty harmless to use UTC here in Europe. But elsewhere in the world that could make garbage results. Especially for time zones that are more than + or - 8 hours.
Fair, but that’s a query / aggregation concern and not one of how datetimes are stored. As mentioned even timestamptz doesn’t store the timezone of a datetime. It just stores the base UTC timestamp. With both column types you can make aggregation for non UTC datetimes work, timestamp just doesn’t automatically convert to the current timezone set.
If you want to work in a different timezone than your default postgres is setup for then you can use this:
Repo.transaction(fn ->
Repo.query("SET LOCAL timezone TO 'Australia/Sydney';", [])
…
end)
If you’re only using one other timezone you could maybe set the timezone for the whole session. DBConnection has some means to execute a callback for each newly started connection. That’s likely what Django does behind the scenes.
All this however is completely unrelated to what ecto type you use at runtime. For :utc_datetime you’d still get a datetime with UTC as timezone back from queries.
I do aggregation of data such as “when are you most productive” or “when are all our users most productive” based on user submitted events. To get the correct (read: intuitive for user) data, I get the data from the user as their local time (datetime + offset). I store it in the database as both UTC (DateTime) and the local time (NaiveDateTime). The latter version is used for the aggregates mentioned in the example, so it is impervious to different timezones. The UTC is used for other things (like “when did this user have their last event”).
In any case, you cannot store the original offset (or timezone for that matter) in PostgreSQL, unless you do that manually in a separate field. So the choice is to store UTC (as timestamp or timestamptz) or a naive local time without offset information.
Glad I am not the only doing this. I default to this in my hobby projects for at least a year now, although making this practice the default in the commercial projects I participate in is a much bigger challenge.
It also a more compact representation, looks nicer in URLs, and doesn’t have ‘filler’ characters (‘-’) while being 100% compatible at the storage layer with UUIDs, meaning you can switch between them easily. I’m quite happy with them…
What do you mean by this? It seams PostgreSQL recognizes just one format for UUID. Using SELECT '12345678901234567890123456'::uuid; gave mi an error. This is valid ULID format but it doesn’t work in SQL. In Elixir it works like magic though.
I like ULIDs and used them in hobby projects but a common complaint against them is that the characters they use can be ambiguous with certain fonts (which is eh, not an actual drawback). But the lack of a clear migration path from UUIDs is what made every single customer of mine to refuse my idea to use ULIDs.
Not bashing on them, quite the contrary; I like them very much. But it seems the world is stuck on UUIDs for the time being.
Textual format, yes. But the binary storage underneath is the same. So in Elixir you can get ULID (or UUID) formatted representations of the 128 bits of data that is stored in PostgreSQL. Postgres will still show it as UUIDs from e.g. psql, but that’s just its default text representation (there are extensions that provide ULID conversion).
The upshot of this is that one can switch between ULID and UUID in an Ecto schema without changing what is stored in the database (no migrations).
Yeah, I don’t consider UUIDs or ULIDs to be human readable.
That is indeed the rub. If you have a lot of data stored as UUIDs out there then it is not easy indeed. One can pattern match on UUIDs vs ULIDs and slowly migrate off of UUIDs, though at that point I’m not sure if the benefits would be worth it. (At work we actually have something like this: some of the global IDs are represented as text UUIDs some as ULIDs, a decision that leaves me generally baffled and predates me being at the company by a good margin … so confusing … but we have validation REs for both)
I think it’s one of those decisions one needs to make at the beginning of a project and then stick with it … or do like they did at my work and change your mind half-way through and have a strange mix
