@dimitarvp so we should override and go with…
@timestamps_opts [type: :utc_datetime_usec]
I have an application that spans timezones, how do run reports and handle timezones between locations? Pull the UTC out and then add the timezone data back to it?
Yes you can basically store the UTC timestamps and store timezones separately [preferably in tzdata ; ex: America/New_York]
You can use the Calendar module to apply shift operations.
Is the Calendar module not needed, at first glance at the documents for tz_datetime it looks like it transforms it when it saves and has a function to retrieve it. As I move between locations and different timezones how would I set the timezone on save dynamically? I didn’t really see how you would set the “American/New_York” in the documents or examples.
How you figure out the timezone for things stored or which timezone to present data in is out of scope for the library. It only deals with the fact that timezone offsets might change while things are in the database, while making sure the db stores datetimes in utc for comparison/sorting purposes.
The Calendar module is not needed, but it is my go-to for all time-related operations as the library itself is very comprehensive and has some powerful APIs.
https://github.com/lau/calendar
You would have your client to notify your server in some way about it’s new timezone. In terms of web apps, it should be straightforward to do it through JavaScript, for example.
You can save that value (which is of the format America/New_York) as a String in your documents/tables.
It is unfortunate that this cannot be done in Ecto because of backwards compatibility concerns, but it would be nice to do this by default for all new Phoenix applications.
I started a discussion about that on the mailing list if anyone is interested in following along:
Everybody agrees about what should be done on Ecto’s side (use :utc_datetime_*).
But @LostKobrakai advice is to use timestamp in the DB, while the links given by @wojtekmach seem to say otherwise (Don't Do This - PostgreSQL wiki and Time zones in PostgreSQL, Elixir and Phoenix | AmberBit Sp. z o. o.).
Is there a consensus?
I don’t think there can be unconditional consensus. As far as ecto is concerned timestamp and timestamptz work just the same. Ecto automatically ensures everything is in UTC. Postgres in theory shouldn’t convert anything for timestamptz, as datetimes are always utc.
Where the difference of those two come in is really how postgres behaves around other clients to the database.