How to store date/time as UTC but retrieve as localized date/time?

[GeminPatel]

Hi,

I was looking for a solution that will store my datetimes in UTC but on Application side will be in IST.
I have followed the guide and added use Timex.Ecto.Timestamps into my def model do in web.ex.

Now querying the Repo I get updated_at and inserted_at as #<DateTime(2017-06-05T05:40:55.030608Z Etc/UTC)>. Is there a way I can set some kind of config to serialize the datetime as IST. Also when I will insert I want to give IST to the changeset and it be properly converted and set in DB to UTC.

Going through the docs was not sufficient to find a solution to this problem.

I will create a MR to update docs if any solution such problem exists.

[bitwalker]

This was the motivation behind Timex.Ecto.DateTimeWithTimezone, which will store the datetime and the timezone name in the database so that you can store and retrieve a localized datetime. Timex.Ecto.DateTime will convert to UTC on write, so any subsequent reads of that field will be in UTC rather than the previously localized date/time.

While I could have written DateTimeWithTimezone to store the datetime field as UTC, and then convert on read, it currently stores the localized date/time - unfortunately changing that behaviour would break existing data, so I'm not able to do so. I could introduce a new type which behaves like DateTimeWithTimezone but stores the date/time as UTC, something like DateTimeWithTimezoneUniversal - thoughts?

[pmyjavec]

This is how I would have expected the library to work actually, I'm glad I read this before using it.

[GeminPatel]

@bitwalker I think that it would be really good if we can have this new addition. I could not find a solution while I was trying to solve this problem. The best I got was Timex. But as you know it was just one step less than what I actually needed. I have also seen lots of questions on StackOverflow and other boards where people are looking for something that provides date/time as UTC but retrieve as localized date/time. It would be really nice if you can add this feature.

All this said, I could have used DateTimeWithTimezone but I would like to keep my code agnostic of DB. If need be I would like to switch to MySQL.

PS: I am new to elixir and phoenix. So thanks for the help :)

[GeminPatel]

@pmyjavec Glad to have helped you :)

[bitwalker]

@GeminPatel regardless the new type would need the same treatment that DateTimeWithTimezone requires today - that is, you need to create a custom type, and at this point I'm only supporting Postgres for that. That said, I'll take a look at this and post back when I have something for you to test.

[bitwalker]

I'll investigate using timestamptz as the underlying type, so it's perhaps possible we can avoid a custom type with this.