Difference in between :utc_datetime and :naive_datetime in Ecto

[ashish173]

I am using Ecto timestamps with postgres, I can see the timestamps() use the :naive_dateime but for my use case I wanted to store the timezone too.

I read a little more here and found out that on the DB end both the fields are the same which is utc time without timezone.

Now, I am a little confused about when should I use :naive_datetime and when :utc_datetime.

[Nicd]

You should use :utc_datetime unless you have a reason not to. The reason why Ecto’s timestamps() are naive by default is backwards compatibility. Since they are always UTC anyway, you should use :utc_datetime for them (and I believe the default will be changed to that in the future).

The difference between :utc_datetime and :naive_datetime is that the former will ensure that you can only insert UTC DateTimes and will read back UTC DateTime structs from the DB, whereas the latter will remove timezone information when writing and return NaiveDateTimes when you read from the DB.

For most cases, you should use UTC.

BTW, if you need to store the timezone too (in PostgreSQL), you have no option but to use a naive datetime field and store the offset or timezone information separately. This is because PostgreSQL does not have a data type for storing a timestamp with timezone.

There is the confusingly named timestamp with timezone but all it does is convert your input timestamp to UTC and convert it back to whatever your DB connection’s timezone is when reading (so for most cases it’s useless). It does not even store the offset/timezone. So you need to do that yourself somehow.

[OvermindDL1]

Also, use naive/UTC to store datetimes in the past, use timezones only when storing datetimes in the future, don’t mix them (personal experience). ^.^;

[outlog]

@OvermindDL1

Also, use naive/UTC to store datetimes in the past, use timezones only when storing datetimes in the future, don’t mix them (personal experience). ^.^;

for datetime in a specific location you generally need “wall time” - as timezones and/or daylight saving time changes…

something like calecto GitHub - lau/calecto: Adapter for the Calendar library in Ecto will get you wall time…

time is fun (not!)

[OvermindDL1]

@outlog

for datetime in a specific location you generally need “wall time” - as timezones and/or daylight saving time changes…

Exactly, that is why future times should always have a time zone.

[SZJX]

The “timestamp with timezone” type still seems to be useful since it makes all clients that try to read from this database interpret it as UTC time. Otherwise each client might try to interpret the timestamp in its local time, which would be a total mess. I find this article to summarize everything related to timezone in Elixir + Postgres pretty well: https://www.amberbit.com/blog/2017/8/3/time-zones-in-postgresql-elixir-and-phoenix/

[LostKobrakai]

@SZJX

Otherwise each client might try to interpret the timestamp in its local time, which would be a total mess.

The following argues that the exact same mess would happen when using timestamptz as well and that being the reason to not use it in ecto in the first place: utc_datetime having no affect on PostgreSQL DDL · Issue #1868 · elixir-ecto/ecto · GitHub

[SZJX]

Interesting. The author of the article I linked to actually seems to think that this is the desired behavior:

While this will be true when you connect to PostgreSQL from your Elixir application, as soon as you connect using different client (psql, Ruby driver etc), the problems will start creeping in. SQL queries like:

SELECT * FROM events WHERE events.start_date < CURRENT_TIMESTAMP;

will have different meaning when you are in Europe/Warsaw time zone, and very much different when you are in PDT (California, USA), because of the 9 hour difference. If you have a reporting or maintenance script that you run against this database, depending on your client locale settings, you will miss more or less events from the query, or include unwanted ones in results. It would only be correct if your client’s settings were UTC.

How can you fix this issue? Use timestamptz data type in PostgreSQL, which is a shortcut to timestamp with time zone.

i.e. because the timestamp will then be guaranteed to be output in local time, the SQL queries will behave consistently across different clients.

I haven’t explored it myself yet so I’m not sure if this would indeed result in consistent behaviors for SQL queries, as the author claimed.

[Nicd]

@SZJX

The “timestamp with timezone” type still seems to be useful since it makes all clients that try to read from this database interpret it as UTC time. Otherwise each client might try to interpret the timestamp in its local time, which would be a total mess.

Wrong, timestamptz will return the timestamp in the connection’s local timezone. They explicitly won’t be getting UTC from the database, unless they have set the connection to UTC. Quoting:

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone.

This is what I never want to happen.

Now it’s true that if you fire up psql or some script and compare the timestamp values to for example NOW(), you may end up with unexpected results if you didn’t remember to set the connection timezone. For me, using timestamptz here would be optimising for a special case and making the general case worse. It’s also reliant on magic (and frankly unintuitive) behaviour from PostgreSQL, and I feel being explicit in what you do (like you have to be when using plain timestamp) is better.

timestamp with time zone is a misnomer and IMO a misfeature that shouldn’t be in PostgreSQL.

[SZJX]

Thanks a lot for the answer. This is definitely quite a rabbit hole to jump into and time representation is always a nightmare to deal with. At least one universal consensus seems to be to always use :utc_datetime in Ecto when there are no backwards compatibility worries. I’ll do that.

[cnck1387]

@SZJX

This is definitely quite a rabbit hole to jump into and time representation is always a nightmare to deal with. At least one universal consensus seems to be to always use :utc_datetime in Ecto when there are no backwards compatibility worries. I’ll do that.

You’ve only reached the first tunnel of the rabbit hole because there’s timex and timex_ecto too.

[salman]

When your schema looks like:

schema "users" do
  ...
  timestamps()
end

How would you convert these to use :utc_timestamps?

[Nicd]

As documented here: https://hexdocs.pm/ecto/Ecto.Schema.html#timestamps/1

timestamps(type: :utc_datetime)

[ourway]

According to THIS reference, we should almost never use timestamp (without time zone).

Don’t use timestamp (without time zone)

Don’t use the timestamp type to store timestamps, use timestamptz (also known as timestamp with time zone) instead.

Why not?

timestamptz records a single moment in time. Despite what the name says it doesn’t store a timestamp, just a point in time described as the number of microseconds since January 1st, 2000 in UTC. You can insert values in any timezone and it’ll store the point in time that value describes. By default it will display times in your current timezone, but you can use at time zone to display it in other time zones.

Because it stores a point in time it will do the right thing with arithmetic involving timestamps entered in different timezones - including between timestamps from the same location on different sides of a daylight savings time change.

timestamp (also known as timestamp without time zone) doesn’t do any of that, it just stores a date and time you give it. You can think of it being a picture of a calendar and a clock rather than a point in time. Without additional information - the timezone - you don’t know what time it records. Because of that, arithmetic between timestamps from different locations or between timestamps from summer and winter may give the wrong answer.

So if what you want to store is a point in time, rather than a picture of a clock, use timestamptz.

More about timestamptz.

When should you?

If you’re dealing with timestamps in an abstract way, or just saving and retrieving them from an app, where you aren’t going to be doing arithmetic with them then timestamp might be suitable.

[ourway]

Based on Postgres wiki:

Don’t use timestamp (without time zone) to store UTC times

Storing UTC values in a timestamp without time zone column is, unfortunately, a practice commonly inherited from other databases that lack usable timezone support.

Use timestamp with time zone instead.

Why not?

Because there is no way for the database to know that UTC is the intended timezone for the column values.

This complicates many otherwise useful time calculations. For example, “last midnight in the timezone given by u.timezone” becomes this:

date_trunc('day', now() AT TIME ZONE u.timezone) AT TIME ZONE u.timezone AT TIME ZONE 'UTC'

And “the midnight prior to x.datecol in u.timezone” becomes this:

date_trunc('day', x.datecol AT TIME ZONE 'UTC' AT TIME ZONE u.timezone)
  AT TIME ZONE u.timezone AT TIME ZONE 'UTC'

When should you?

If compatibility with non-timezone-supporting databases trumps all other considerations.

[jung-hunsoo]

When You create a new record in default Ecto timestamp, it’ll be created with UTC datetime if you set the DB time zone properly.

Here is the real data.

My local PostgreSQL DB has time zone of ‘Asia/Seoul’(+09:00).
There’s a record created at 2019-12-26 10:50:23.
And in psql, it has 2019-12-26 01:50:23 in inserted_at.

But personally, I don’t think managing/manipulating tz on DB layer is a good idea. Your app will be dependent on PostgreSQL. Being aware of tz in timestamp is nonsense and a root of horror. Timestamp is best when used as UNIX timestamp.