Ecto - Postgresql wiki: Don't do this

I am curious about things you think that Ecto is doing from that list?

At least 4.1, but there are valid reasons to not follow the advice.

NaiveDateTime already doesn’t involve timezones and DateTime is enforced to have UTC set as timezone at the time of insertion (runtime). So timestamp is the better default over timestamptz because you can be sure the database won’t ever try to convert the timestamp. Basically ecto is already doing what timestamptz is supposed to do over timestamp but it does so in it’s own control.

Also I’d imagine the ecto team is well aware of what they’re doing.
See this for a response on the above: utc_datetime having no affect on PostgreSQL DDL · Issue #1868 · elixir-ecto/ecto · GitHub

The “don’t use table inheritance…never…use foreign keys instead” is relevant to a discussion I was having last week. That said, we came to the foreign key conclusion, and table inheritance isn’t suggested by the Ecto docs anyway.

I think Ecto keeps to those rules and hides away a lot of the potential pitfalls on that list (money type, uppercase column names, etc.)

Ecto is doing it because over binary protocol that postgrex uses, timestamp and timestamptz is precisely the same thing.

Most postgresql connectors use text based protocol, in other languages. Postgrex uses binary protocol. It doesn’t understand client’s time zone.

I do think that, however, timestamptz is a safer choice, especially when other clients can connect to database. For example, if you ever connect using psql, you could see dates & times in different time zone because of your client configuration and then this can even lead to mistakes. I guess it’s OK if you are aware of the issue. But for the consistency and possible error avoidance, I like to uze timestamptz everywhere which forces the dates to be displayed in UTC no matter what your client is.

I have written about it more in details here: Time zones in PostgreSQL, Elixir and Phoenix | AmberBit Sp. z o. o.

http://www.postgresqltutorial.com/postgresql-timestamp/:

When you query timestamptz from the database, PostgreSQL converts the UTC value back to the time value of the timezone set by the database server.

So the database server establishes the context for your time data?

• Not helpful when the database stores event data scattered throughout various timezones - i.e. the data defines the context, not whatever timezone is set on the database server.
• Not helpful if it makes sense to convert the time to the timezone where it is being consumed (halfway around the globe).

So recording all time data as UTC (for ease of point in time comparison without interference of DST) and recording the event timezone separately makes universally more sense (there are always exceptions).

Accepting the database server timezone as the centre of the universe seems myopic.

I think the real issue is that clients assume that time without a timezone reflects their own timezone. The ambiguity is gone if you establish upfront that any time without a timezone is always UTC (however inconvenient that may be).

A phone call today between São Paulo and Warsaw could have started at 14:00 UTC but would have been observed by the parties involved at 11:00 (-03:00) and 16:00 (+02:00 (DST)) respectively.

To be clear: timestamptz seems to want to do the right thing, i.e. store UTC times.

However the type timestamptz seems to signal that the DB server/driver boundary is the right place to do the conversion. I’m not convinced that in increasingly distributed systems that is necessarily the right place to do it.

The most important thing on this list that is not possible in Ecto right now is usage of existential queries. Right now the only option is to use anti-joins instead.

As you point out, that would be terribly inconvenient. However, that description is misleading for two reasons:

1. The timezone of the database server is merely a default for the timezone of the connection, which you can set to anything you want.

2. For my local time zone it doesn’t convert 2019-05-10 07:30:00 to 2019-05-10 00:30:00, but rather 2019-05-10 00:30:00-07. So, convenient in the default case, but trivial to parse accurately as UTC and display any way you want… (That’s for text-based protocol, I don’t know the details of binary, but I’d bet that you get UTC without being offset, because it makes no sense to adjust an 8-byte integer for easier reading by humans.)

Likewise for sending timestamps to the server, the “assume it’s server/connection time zone” only applies to timestamps which do not specify an offset.

You are right that would be a lousy way to do it, but it’s not really what PG does. Since the offset gets added, it’s not a conversion of the underlying value at all, but rather a guess at a convenient display format.

(Also “timezone of the server” can be specified in the PG config, and it’s common practice in distributed system to set it to UTC, rather than letting the system try to infer the actual time zone.)

Yeah I’ve had to help someone who had a nil in a blah not in ^thing, Ecto really needs to not use NOT IN in sql…

Probably reflecting some of my confusion at the time (hence my addendum to the post) but I’m still unconvinced that timestamptz is the cure all that the wiki promotes it to be.

It feels like a technological quick fix for a conceptual misunderstanding.

With timestamp without time zone the timezone is portrayed as missing which is entirely true when looking at the type and perhaps even the record in which a value of that type exists. But in truth the timezone is usually implied. The problem is that from an individual point of view it is local time that is perceived and recorded. And in legacy systems that often was the frame of reference.

However from a global point of view you either need an explicit timezone as a frame of reference or you need a fixed frame of reference, i.e. UTC.

So frankly any time represented/stored without a timezone should be UTC in order to avoid confusion. So storing UTC is the part that timestamptz gets right.

it’s not a conversion of the underlying value at all, but rather a guess at a convenient display format.

So the good news is that the UTC value is sent to the querying system. The bad news is that the value displayed in the an admin tool like psql won’t match it unless you set timezone to 'UTC'; - I can see that generating a lot of confusion.

For my local time zone it doesn’t convert 2019-05-10 07:30:00 to 2019-05-10 00:30:00, but rather 2019-05-10 00:30:00-07.

I’m assuming that 2019-05-10 07:30:00 refers to the already stored value, because:

=# set timezone to '-7';
SET
=# select '2019-05-10 07:30:00'::timestamptz;
      timestamptz       
------------------------
 2019-05-10 07:30:00-07
(1 row)

=# select '2019-05-10 07:30:00 UTC'::timestamptz;
      timestamptz       
------------------------
 2019-05-10 00:30:00-07
(1 row)

i.e. a string without a timezone, parsed inside the server is going to assume the set timezone or timezone from the postgresql.conf. But

it’s common practice in distributed system to set it to UTC

guards against that.

Seems don’t put anything but UTC into timestamp is a simpler solution to the problem (which seems to be the Ecto approach - and the “convenience” of timestamptz isn’t needed anyway).

Another problem that timestamptz doesn’t fix - ambiguity during local end of daylight savings (unless server/connection is set to UTC or ignore DS).

=# SELECT '2019-11-03 01:50'::timestamptz AT TIME ZONE 'UTC';
      timezone       
---------------------
 2019-11-03 06:50:00
(1 row)

=# SELECT '2019-11-03 05:50 UTC'::timestamptz;
      timestamptz       
------------------------
 2019-11-03 01:50:00-04
(1 row)

=# SELECT '2019-11-03 06:50 UTC'::timestamptz;
      timestamptz       
------------------------
 2019-11-03 01:50:00-05
(1 row)

There’s also 5.1:

varchar(n) is a variable width text field that will throw an error if you try and insert a string longer than n characters (not bytes) into it.

Ecto does it as far as I can tell, setting :string type without specifying size would produce varchar that is limited with 255 characters and it will indeed throw a database level error on an attempt to insert a longer value. I’ve got this error once or twice in the past and had to go back and explicitly validate / cast each string value.

This is probably because Ecto must support other databases including older versions of MySQL but still legit point with mentioned consequences.