Timestamps with time zone broken

[MSch]

Seems like Ecto 0.9 has broken support for timestamps with time zone. Adding the following after create table(:posts) in integration_test/support/migration.exs breaks the tests:

execute "ALTER TABLE posts ALTER COLUMN inserted_at TYPE timestamp with time zone;"

I tried to do some investigating but didn't get far.

---

Related: Specifying :datetime in migrations should create a column of type timestamp with time zone, not timestamp without time zone:

Long story short – I believe that using timestamps (without time zone) is, in almost every case, a bug. And should be fixed.

http://www.depesz.com/2014/04/04/how-to-deal-with-timestamps/

Use timestamp with time zone (aka timestamptz) and time with time zone (aka timetz). They store values as UTC, but convert them on selection to whatever your time zone setting is.
Avoid timestamp without time zone (aka timestamp)

http://justatheory.com/computers/databases/postgresql/use-timestamptz.html

[josevalim]

Ecto does not support timestamps with timezones. :) That's why it is "broken" for you. I actually advocate that dates should be saved without timezones in the database and @lau could even give good reasons for such.

Also, because not all databases support timezones, having no timezones by default is the best way to ensure they work with any database (which is one of Ecto goals).

It is likely that the best solution here will be an ecto_pg_timezone package. :)

[MSch]

Just to make sure we are talking about the same thing: timestamp with time zone (= timestamptz) does not save the time zone in the database, like timestamp without time zone (= timestamp) it just stores a timestamp without storing any timezone/offset information.

The difference is timestamptz is defined to always be in UTC and is transparently converted from/to the time zone that is set for the current connection. (Most applications will set the connection's time zone to UTC, so that no conversion is necessary)

E.g.

select timestamp '1999-01-08 04:05:06 -8:00'; -- 1999-01-08 04:05:06 TZ information is just discarded
select timestamptz '1999-01-08 04:05:06 -8:00'; -- 1999-01-08 12:05:06+00 The TZ set for the connection is used to convert the timestamp to UTC
select timestamptz '1999-01-08 04:05:06'; -- 1999-01-08 04:05:06+00 if the timestamp is already in UTC nothing happens

I feel strongly that Ecto should at least support the timestamptz out of the box and - I realize that is an appeal to authority - the people I quoted are Postgres experts.

Supporting timestamptz in Ecto does not require any changes to the Ecto.DateTime struct and Postgrex already supports en/decoding it since it is exactly the same wire format as a normal timestamp.

I called it "broken" only because this used to work in Ecto 0.8.0 and I did not expect support for this data type to go away.

If you want I can move this to the mailing list.

[josevalim]

Confirming: we are talking about the same thing! I was a bit imprecise though, sorry about that.

I called it "broken" only because this used to work in Ecto 0.8.0 and I did not expect support for this data type to go away.

It worked before when we were not automatically casting postgres types. I believe the fix would be to add the types here: https://github.com/elixir-lang/ecto/blob/master/lib/ecto/adapters/postgres/datetime.ex

But I am really not confident that we should do it. Would it be a lot of work for you to provide such extensions for your types?

[josevalim]

And it is fine to discuss it here. :)

[MSch]

I don't think it's much work, but it is something I believe should work out of the box with Ecto.

If I'm not mistaken all I need to do is copy&paste the decode and encode lines for timestamptz_send and timetz_send and write tests. I actually would have expected the Ecto adapter to transform Postgrex.DateTime to Ecto.DateTime instead of doing the de-/encoding itself. Then timestamptz would also work out of the box.

Why do you think those types should not be supported? And why not default to timestamptz as default for :datetime? I honestly don't see why except for "Rails does it (because of MySQL)". (The links above already explain the arguments in favor of timestamptz, so I'll avoid repeating them.)

I tried Googling for arguments against timestamptz and didn't find much. One StackOverflow answer talks about the problem of having timestamps in the future when the DST changes and this moves the timestamp. But this has the same problems if the application uses timestamp and uses UTC instead of the local time zone internally. And if the future timestamp is e.g. an appointment between people in multiple time zones then none of those approaches solve the problem.

[lau]

You don't always want to save datetimes in UTC. Example: If you want to save a time for a meeting in the future which is scheduled at a certain local time, you want to save the time in local time along with the time zone. For instance a meeting in Paris, France 2015-10-30 10:00:00. If you convert that to UTC and save it and then the French timezone rules change later, then when you load it you might get for instance 11:00:00 instead. I'm actually writing a blog post about this particular problem, which will be out soon.

To save such a datetime with Ecto and Postgres you can use Kalecto with the Kalecto.DateTime type. It uses a composite type in Postgres which consists of a "naive datetime", a time zone name and a total UTC offset in seconds.

I agree that working with "naive datetimes" is not what you want for most situations. Which is why in the Kalends library the DateTime type has to have an explicit timezone. If you use the Kalecto.DateTimeUTC type, you can only save DateTime structs to it that have the "Etc/UTC" timezone.

About the timez type: time (without date) with timezone, it's just really weird. The existence of that type makes me sceptical of the timezone functionality of Postgres.

[MSch]

@lau agreed re meeting scheduled at certain local time in the future. But as I said, if you schedule e.g. a videoconference across time zones then that problem is just hard and not solved by any of those approaches.

What do you think about storing past timestamps (inserted_at, updated_at)? Should they be stored in UTC or local time?

Would you use timestamptzs for Kalecto.DateTimeUTC?

The existence of that type makes me sceptical of the timezone functionality of Postgres.

Yeah timetz is really weird, afaik it's a legacy leftover and no one uses it. But I wouldn't say that this is a reason to be sceptical of timezone functionality in Postgres, I have never heard about any problems and generally Postgres functionality works as advertised. To quote the documentation:

We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard).

[lau]

But as I said, if you schedule e.g. a videoconference across time zones then that problem is just hard and not solved by any of those approaches.

If you want to schedule a videoconference, you can agree on a datetime and a timezone. Eg. 2015-04-01 12:00:00 UTC. Or 2015-04-01 12:00:00 Europe/Paris. We are talking about persisting datetimes, so we don't have to be too concerned with the whole process of talking to all the parties and agreeing on a time with a timezone. When we have the datetime we can persist it using the methods that for instance Kalecto uses.

What do you think about storing past timestamps (inserted_at, updated_at)? Should they be stored in UTC or local time?

Today the timestamps are stored as UTC timestamps, but the type at the database level is a "naive time". Just to be clear, I wouldn't call it local time.

Yes, I do see an advantage in having UTC datetimes (for instance inserted_at, updated_at) and other UTC datetimes (for instance Kalecto.DateTimeUTC) saved as UTC datetimes (timestamptz) at the database level if possible. If this could be the default for Postgres somehow it would be neat.

One way to do it would be to add another datetime type to Ecto. Maybe called datetimez. In Postgres it could map to timestamptz. In other databases it could map to something equivalent - or if something equivalent doesn't exist for that database it could just map to the same naive datetime type that is used for datetime today.

[MSch]

One way to do it would be to add another datetime type to Ecto. Maybe called datetimez.

What do you think about making the :datetime type support both timestamp and timestamptz? That's as easy as #537

Then the only question left would be which type is created by migrations. From my perspective timestamptz make sense, since in my (and those people I've linked) opinion most of the time that's what you want. Maybe there could be an option similar to usec: false: timezone: true?

[josevalim]

We shouldn't use timestamptz by default because it is a Postgres only feature.

[MSch]

Sorry I wasn't clear, I didn't mean that the MySQL adapter should use timestamptz, only the Postgres adapter should. IMO every database should be supported as well as possible, I don't see a downside when using timestamps without time zones on MySQL, since there is nothing better available there anyhow, I only see the upside of providing a better experience for Postgres without penalizing other adapters.

[lau]

What do you think about making the :datetime type support both timestamp and timestamptz? That's as easy as #537

That sounds good to me. I guess you could just put timestampz in the migrations if you use postgres and everything using standard Ecto features (ie. not postgres specific SQL) would work the same way as if it was a normal datetime type. But the advantage is that we have the information about the timestamp being UTC saved in the database. And if you want to use timestampz specific functionality you can.

Then the only question left would be which type is created by migrations. From my perspective timestamptz make sense, since in my (and those people I've linked) opinion most of the time that's what you want. Maybe there could be an option similar to usec: false: timezone: true?

I think an option sounds good. How about specifying the type in the migration? In the migrations you would use this:
timestamps type: :timestamptz

It would be simple to add here: https://github.com/elixir-lang/ecto/blob/master/lib/ecto/migration.ex#L388

[josevalim]

@MSch we should support each adapter well but the default and the main goal should be about portability. Because developers will write extensions and code that is meant to work with any Ecto adapter and they should get different behaviour on each.

[lau]

In this case it seems like the behavior would be the same no matter what database you use. If you use Ecto in a generic way. The difference is if other apps interact with the database and it's postgres, they will work with the timestamptz type and also if someone wants to use postgres-specific SQL queries they can make use of what timestamptz provides.