Converting non-UTC timezones to UTC during cast

[edmz]

I am a little confused on how to manage dates my phoenix app when my DB is on UTC and most (but not all) my users edit dates in a specific timezone (that is not UTC).

I posted my question to Elixir Forum (https://elixirforum.com/t/confused-with-storing-dates-with-calendar-ecto/2162) and José suggested create a PR for Calecto.DateTimeUTC.cast to accept a non "etc/utc" timezone and convert it to utc.

What are your thoughts on this?

Thanks in advance.

[lau]

If you use Postgres you can use the Calecto.DateTime type. Also see this article http://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html

If you know the time zone you can do that. If you don't you can use NaiveDateTime.

What the Calecto.DateTime type does is save both the time zone identifier (e.g. America/New_York) and the local "wall time". In this case the wall time would be the time it is specific time zone. If you don't use postgres you could also manually handle saving both the time zone identifier and the wall time in two separate fields.

[waiting-for-dev]

Hi!

Calecto.DateTime is nice but it only works in postgres and it couples a database type to an application internal implementation.

I think it would be ideal to have the possibility to define types for specific time zones. That time would be persisted as UTC to the database, but the type module load and dump functions would do the transformation transparently. I think that it is in that boundary where this transformation should happen, that way the database remains portable and the application works within its timezone transparently. What do you think?

Right now, Ecto doesn't allow to define somehow configurable custom types, so calecto should define a custom type for each time zone, or, surely preferable, implement some macro to define them dynamically (I'm not still quite skilled in Elixir metaprogramming, but I guess it should be possible, isn't it?).

By the way, is there any reason why, being postgres specific, timestamp with time zone has not been used as Calecto.DateTime backend type?

Thanks!

[lau]

Hi

This is why "timestamp with time zone" is not used:

For timestamp with time zone, the internally stored value is always in UTC

The name is misleading. It does not really store the time zone.

Here is some more information about why it is necessary to save the local "wall time" instead of UTC: http://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html

[waiting-for-dev]

Thanks for the response. I read the article and now it makes sense 👍