Custom timestamp formats and columns in an existing (legacy) database

I’m working on a simple Phoenix project to expose the usual CRUD methods for editing a resource, but the catch is that it uses an existing MySQL database. The database is shared by other applications, so changing the column names and data types is not practical. I would like the Phoenix application to continue to support the table’s various timestamps for tracking when a row was created, updated, deleted etc. To make things a bit more challenging, the data types are not consistent. Some of the columns use UNIXTIME integers (i.e. seconds since epoc), and other columns use MySQL dates (e.g. 2018-01-15 23:48:59).

I’m looking at the docs for Ecto: and I see the @timestamps_opts… it says the :type uses the default of :naive_datetime, but it doesn’t say which data types are available… a list or at least a link would be helpful there. What is the type that should be used for UNIX timestamps?

Can anyone point me to some resources to help figure this out? Thanks!

Depends on the ‘type’ of the column in the database. :slight_smile:

In MySQL, the table definition includes int(11) unsigned NOT NULL … that might make me guess to use :integer or perhaps :int, but it doesn’t tell me which types can be used.

How about MySQL columns that use MySQL’s timestamp type? Or its datetime type?

Yick, yeah that is an Integer.

However, you can make a custom Ecto type (really easy, just define a module with a couple converter functions, see the Ecto Type docs) that will auto convert it to/from an Elixir NaiveDate if you so wish (I wonder if there is already one…).

Then you’d use naive_date if it is timezone-less, or date if it has a timezone. :slight_smile:

1 Like