Storing dates in iso8601 format

Questions / Help
1

I am rather new to the Elixir and Phoenix ecosystem and decided try moving a part of my serverless backend to Elixir as a learning project.

For compatibility with my other database (DynamoDB) I’d like to store timestamps in iso8601 format in Postgres while using Ecto. Is this achievable without any external libraries, do I need to implement a custom Ecto type?

2

Date module has a function for formatting %Date{} to iso8601

iex> date = Date.utc_today()
~D[2018-02-19] # %Date{} struct

iex> Date.to_iso8601(date)
"2018-02-19"

iex> to_string(date) # called in when being rendered in views
"2018-02-19"

So with postgres you would should be fine by using postgrex, since it supports %Date{} structs.

3

Thanks for the suggestion! I should’ve been a bit clearer though, I’m currently using Phoenix with Ecto for DSL. I’m looking for a way for to insert and receive iso8601 dates when querying dates with Postgres.

I’ve tried achieving this by setting Ecto’s @timestamp_opts to use :utc_datetime. Storing a date using generated using DateTime.utc_now() |> DateTime.to_iso8601() just results in the following ChangeError
** (Ecto.ChangeError) value `"2018-02-18T16:00:39.194018Z"` for `App.Thing.inserted_at` in `insert` does not match type :utc_datetime

4

If you want to store your datetimes in string format, you need to use a :string type in Ecto. Or you can create your own custom Ecto type that does the formatting and parsing for you, that might also work with Ecto’s timestamps().

1 Like
5

Thanks, it seems like a custom Ecto.Type is the best way to go!

6

You should never ever store dates as a string in a database, ever ever. It makes working on them in the database for further processing a living hell (personal experience working with older databases that I have to interact with).

Never ever ever ever store a date in a database as a string, this is important enough to repeat many times.

2 Likes