Deprecation warning says to use :naive_datetime but I'd like to use :utc_datetime

Questions / Help
1

I’m working on converting dates in my ex338 application from Ecto.DateTime to Elixir DateTime. The deprecation warning says to use :naive_datetime. I currently use the default timestamps() in my schemas and migrations.

I read the article above by @hubertlepicki and would like to use :utc_datetime. However, I already have data in my production database.
Does the Ecto migration create different types in Postgres for naive_datetime and utc_datetime? Would I need to write a new migration to change all the timestamps in my existing tables to :utc_datetime? If so, what would that look like? Would I modify both the updated_at and inserted_at fields to change the type?

2

That should probably be separate thread so other can find answer easier but basically you most likely have:

timestamp without timezone as types of your columns. And Ecto would generate timestamp with timezone if you used :utd_datetime.

You use alter table command to change the types, there should not be problem with updating the column type this way, and no data should be changed/lost.

You can also leave it as it is, and it all will be good as long as you only use Ecto/postgrex to connect to your database server. For consistency between psql and Ecto, I would hovever recommend migrating those types.

2 Likes
3

That’s not correct. Ecto does use timestamp without timezone for both naive and utc datetime column. The difference is only that utc_datetime will check the timezone of runtime datetime structs before inserting things into the db, while the naive version does not have a timezone at runtime.

More here: utc_datetime having no affect on PostgreSQL DDL · Issue #1868 · elixir-ecto/ecto · GitHub

4 Likes
4

Yes you are correct. I meant the scenario where in schema you use :utc_datetime and in migration :timestamptz (as per blog post)

1 Like
5

Just to clarify. The warning says to use :naive_datetime since that’s the type with exactly the same behaviour as the old Ecto.DateTime. The :utc_datetime has slightly different semantics.

I agree, though, that for most cases, :utc_datetime is actually better.

2 Likes
6

The blog post’s suggestion is as unfortunate as PostgreSQL’s timestamptz type is. That type should not be used for anything (because it doesn’t do what it promises) and timezones should instead be handled in the program code.

1 Like
7

Well, no. Timestamp without timezone should not be used because it leads to
issues when your client’s timezone is something other than UTC. But the
name is unfortunate.

1 Like
8

In fact only timestamp without timezone should be used, as it returns the timestamp you stored as-is. Timestamp with timezone does not store the timezone, instead it stores it as UTC and converts it to whatever your SQL connection’s timezone is when reading. That does not match the user’s timezone in 9/10 of the cases and most likely just leads to strange problems if you forget to set it to UTC.

What you should do is store as timestamp without timezone and enforce that the data is UTC in your software (Ecto does this). Then you know it is all UTC and that PostgreSQL will not do any unexpected conversions. In case you need to store local times, store them as timestamp without timezone and store the timezone or offset (whichever you need) separately (because timestamptz won’t do this), and again enforce the correct timezone in your software.

5 Likes
9

Thank you everyone, I’ve learned a lot. I want to confirm what I think I understand:

If I used timestamps() in my original migrations and schemas then my Postgres database is using timestamp without timezone. If I want to change my schema to use timestamps(type: :utc_datetime), I could just change the schema without a new migration.

A migration to change my timestamp fields (inserted_at and updated_at) to :utc_datetime would not change the types in the Postgres database because :utc_datetime also uses timestamp without timezone.

Did I get that right? Also, would people recommend I write the migrations for all my tables just to keep them consistent with my schemas?

Thanks again!
Axel

1 Like
10

Again, that depends on how you look at it. In fact in both cases you can corner yourself into difficult to debug bugs.

Here’s the thing, if you use timestamp without time zone type, when you select * from something you will always get the value you inserted before. So, no matter what client’s time zone was when inserting 10:00 00:00:00, it will always return you 10:00 00:00:00. However, the NOW() will return different value depending on your client’s locale.

So, if you have a query like SELECT * FROM foo where CREATED_AT < NOW() this will lead to a bug if your connecting client’s time zone is not what you expect it to be.

On the other hand, if you use timestamp with time zone you will not have this bug, because NOW(), and the CREATED_AT field will be shifted to the same local time zone client uses. But you are then prone to errors if your client inserts values using SQL client, as you need to make sure these are now in local time as well - and not UTC. If your client is then set to “Europe/Warsaw”, the values you insert need to be local for timestamp with time zone.

Luckily, whatever you do, while you are using only Ecto/Postgrex to access your database, it will always treat the dates as UTC, because the binary protocol only uses UTC. So whichever type you use, as long as you convert the zones in your app, you are safe.

It can bite you in the behind when you either import data, or run reports using external scripts, however. Either way, it’s good idea to treat dates in import as UTC, and shift client zone to UTC in your reporting and importing scripts with SET TIME ZONE UTC, or avoid doing that sort of things completely.

3 Likes