Store unix time in timestamp() when creating table?

Questions / Help
1

Is there a way to use Unix/Epoch integer or :milli/:micro instead of naive_datetime for inserted_at or updated_at fields?
It is easier for fleet management app to calculate delay between position send_at and when the record was received/inserted_at when using unix time format.

2

what is the query you are looking at? postgres/ecto can easily calculate that for you using a fragment…

3

I understand that it can be calculated. I want to change the inserted_at/updated_at to integer type rather than NaiveDateTime. Is there a option that can be passed to timestamps() when create table is executed?

4

https://hexdocs.pm/ecto/Ecto.Schema.html#timestamps/1

type integer, and then put in an autogenerate module/function call that returns the epoch eg. :erlang.system_time / 1.0e6 |> round or similar…

unless you are very certain I would warn against this… for db normalization reasons - are all tables gonna use these timestamps or only some(sic)…

1 Like
5

:+1:

Exactly what I was looking for. Yes, all tables would use the same timestamps(), so I am looking at implementing it at @timestamps_opts. I don’t know how yet.

6

I works now.
This is what I had to do.
In the migration:

timestamps type: :bigint

In the schema:

timestamps(type: :integer, autogenerate: {:erlang,:system_time,[:microsecond]})

The record shows as:

   inserted_at: 1520697680339178

Where should I set the @timestamps_opts?

7

assuming phoenix here?

in your myapp_web.ex (or is it myapp.ex) you can create a macro model like this example https://github.com/bitwalker/timex_ecto#using-with-phoenix

then remove use Ecto.Schema from the schema file and replace with use MyApp, :model

obviously you can call it something other than “model” - wasn’t that word banned recently :wink:

1 Like