Ecto: 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 https://github.com/elixir-lang/ecto/pull/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.

[MSch]

(was writing the same thing, only less eloquent) :+1: @lau

Btw, it seems MySQL has equivalents to Postgres timestamp and timestamptz:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

But TIMESTAMP's max year is 2038 (which I do hope that it will be fixed soon?), while DATETIME supports up to year 9999.

[josevalim]

Isn't the timezone setting based on the transaction though? So if you receive user data, which is meant to be in the user time zone, and then we invoke Ecto.Model.Timestamps which assumes UTC for created_at, if they both are timestampz, at least one of them will be wrong. Or am I missing something?

[mobileoverlord]

MSSQL will support this through datetimeoffset data type. I am torn because I agree with Jose about portability, but I also believe that data types like these should be exposed.

[lau]

@MSch With MySQL TIMESTAMP when you retrieve it and MySQL decides to convert it to some other time - does it always tell you the offset? If it doesn't it's not equivalent.

@josevalim I'm not sure I understand. If you receive user data (let's say from a web app) in the user timezone and want to save it you would either save it at something else than timestamptz or convert it to UTC first.

@mobileoverlord That's nice that MSSQL has that. I don't think we will loose portability, because MySQL can just "degrade" to a normal datetime.

[mobileoverlord]

I'm not sure if this is right, but the goals here seem to be.

1. All timestamps should be stored as UTC in the database.
2. The adapter / driver should handle the casting to and from data stored as UTC to the intended time zone.

Am I not correct in thinking that we can't have both because let's say you have an app written in PG and then you migrate it to MySQL which doesn't support this casting. The app would need to change because all the times wouldn't get casted? The app would then need to contain the logic for doing the conversion.

[lau]

@mobileoverlord Here are some goals I would support:

a) For the timestamps feature in Ecto that uses inserted_at and updated_at, allow using timestamptz and the MSSQL equivalent - and whatever other database that supports something equivalent.

b) Allow a "datetimez" type which can use as a field in an Ecto schema which is meant for saving datetimes in UTC.

The timezone would always be UTC. You tell the database: save this datetime e.g. 2015-03-22 23:00:00Z. When you retrieve it again, if there is any funkyness with the database returning something with other than Z or +0, for instance 2015-03-22 20:00:00 +3, then Ecto/the adapter/driver could correct this because it has all the information available to do this. But hopefully we never have to do that conversion if we can somehow force the database to always display these datetimes as UTC when we retrieve them, either by SQL or a setting in the connection or something.

Anyway let's say you use MySQL instead of MSSQL or Postgres. You will still get UTC time just like you do today.

From the users points of view you tell Ecto an UTC datetime and you always get the same datetime back. Like I wrote before it's more about other uses of the database. For instance if you decide to not use Elixir anymore in 10 years and you have a Postgres database with timezonetz timestamps then your database is aware that the timestamps are UTC. If you use MySQL and the datatimes are "naive" about the timezone, you still know that the timestamps are UTC, but MySQL doesn't.

Imagine if MySQL didn't have timestamps or datetimes at all and we saved inserted_at and updated_at timestamps as RFC3339 strings. It would still work in MySQL and it would still be UTC, but it's just better to have a database where a more informative and precise type is used. It's the same principle.

[josevalim]

@lau because the configuration is per transaction, I can't have in a single
insert timestampz given in UTC and timestampz given by the user in
another l time
zone. This means that, if we default to timestampz to everything,
inserted_at and updated_at will be broken because the automatically
generated date time by Ecto is in UTC.

_José Valimwww.plataformatec.com.br
http://www.plataformatec.com.br/Founder and Lead Developer_

[lau]

because the configuration is per transaction, I can't have in a single insert timestampz given in UTC and timestampz given by the user in another l time zone.

My proposal is to only use timestamptz for UTC anyway. The user should not input a timestamptz in another timezone.

If you want to save a datetime which is not UTC, you can use a normal :datetime type. So let's say you have a single transaction where you save a new Ecto struct that has both inserted_at, updated_at and a custom datetime alarm_at which is just a :datetime 2015-03-23 10:10:10.000000. The alarm_at should not be modified in any way, it's just a datetime "without timezone" as far as postgres is concerted. inserted_at will be sent as e.g. 2015-03-23T08:00:00.000000Z.

From PostgreSQL docs http://www.postgresql.org/docs/current/static/datatype-datetime.html (search for the word "Universal" on that page to find this paragraph):

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's timezone parameter, and is converted to UTC using the offset for the timezone zone.

So since all "timestamp with time zone" timestamps are saved internally in UTC anyway, it only makes sense to use that type to save UTC timestamps. So we just make sure we send a "Z" along as an offset to postgres when we want save these timestamps and then it should be saved correctly without postgres assuming that it is in any other timezone. (By the way the Postgres documentation is wrong in suggesting that UTC and GMT is the same concept.)

"timestamp with time zone" is a confusing name, because it seems to really be more of a "timestamp saved as UTC - and if you give us a timestamp with an offset we can convert it to UTC for you". Postgres only saves it in one timezone: UTC.

[josevalim]

Yeah, this approach is confusing then because it requires users explicitly opt-in into another format when they don't want it to be in UTC.

There is also this: "it is assumed to be in the time zone indicated by the system's timezone parameter" which will definitely catch some off-guard.

Given the potential to confusion and the marginal benefits, I am really not convinced we should go with timestampz.

[lau]

Yeah, this approach is confusing then because it requires users explicitly opt-in into another format when they don't want it to be in UTC.

It doesn't have to be confusing. If by default the inserted_at/updated_at timestamps are of the UTC type, why does the user even have to care about it? Could it not be made transparent? In the migrations you don't see what database type it is, you just write timestamps. In the schema it's the same thing.

There is also this: "it is assumed to be in the time zone indicated by the system's timezone parameter" which will definitely catch some off-guard.

I agree that the way Postgres assumes the server timezone is weird, but it does not seem difficult to solve that.

The first part of that sentence "If no time zone is stated in the input string, " means that we just have to state the time zone in the input string. And it should be UTC. So we just add a "Z". Job done. If Ecto or the adapter/driver does this, the user won't have to think about that at all with normal use through the Ecto interface.

Given the potential to confusion and the marginal benefits, I am really not convinced we should go with timestampz.

Another benefit of supporting these types in general is compatibility with existing PostgreSQL and MSSQL databases. If it is important to support really old MySQL databases, shouldn't we also consider supporting these types which are used in existing PostgreSQL and MSSQL databases?

Here is a way it could work with a new type added:

| Ecto type | Postgres | MSSQL | MySQL |
| --- | --- | --- | --- |
| :datetime | timestamp | datetime | datetime |
| :datetimez | timestamptz | datetimeoffset | datetime |

It could be called datetimez or datetimeutc. The way to choose is quite simple. If you know you only want to save UTC timestamps use datetimez. If you want to save datetimes that are not only going to be in UTC, use a normal datetime. I frankly think that choosing between the two is easier than choosing between decimal and float.

For MySQL you would just use the same type for both. I don't think this affects portability.

P.S. The blog post I mentioned in an earlier post was published yesterday. There's been around 10000 pageviews and a lively discussion with more than 90 comments about it on Hacker News :)

[josevalim]

The first part of that sentence "If no time zone is stated in the input string, " means that we just have to state the time zone in the input string. And it should be UTC. So we just add a "Z". Job done.

Thanks. This helps a lot.

Btw, regarding the benefits, I agree supporting those types is beneficial, I just meant about supporting those types by default.

[josevalim]

Nice work on the article btw :)

[lau]

Nice work on the article btw :)

thanks :)

Btw, regarding the benefits, I agree supporting those types is beneficial, I just meant about supporting those types by default.

As in it being the default for the timestamps feature? If it is implemented in a good way, I don't see the downsides.

[josevalim]

We decided to support timestamptz but not as a default. The required changes are already in master. Users can explicitly opt-in to the new types (it is trivial to do so in migrations) and that's why we don't believe there is a need to provide any option.