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

Questions / Help
1

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: https://hexdocs.pm/ecto/Ecto.Schema.html 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!

2

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

3

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?

4

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