Phoenix, Json and Unix Timestamps

2
votes

I'm exporting data from SQL Server in json format so I can import it into my Phoenix app. What I'm not sure about is how I should deal with dates. At the moment I'm exporting dates as a Unix timestamp. Here's a sample:

[
  { "Log": { "Start": 1319734790, "End": 0, "Comment": "" },
  { "Log": { "Start": 1319732847, "End": 1319734790, "Comment": "Had lunch today" }
]

In Phoenix/Elixir what's the best way to convert a Unix timestamp into a DateTime object? I'm assuming because I'm inserting into an Ecto database, that I need to use an Ecto DateTime object.

2
jsondatetimeelixirphoenix-framework
michal.muskala.eu/2015/07/30/unix-timestamps-in-elixir.html – Dogbert
Also, if you are planning to work with time a lot, it may make sense to use Timex library, which makes things way easier. – NoDisplayName
In the end, because I have control over the json, I used strings in iso date format. These strings are automatically converted for a DateTime field and I didn't have to do any manual conversions – Mitkins
@Dogbert The link you gave is 404 – tejasbubane

2 Answers

2
votes

You can get an Erlang-style datetime tuple from a unix timestamp like this:

epoch = :calendar.datetime_to_gregorian_seconds({{1970, 1, 1}, {0, 0, 0}})
datetime = :calendar.gregorian_seconds_to_datetime(your_unix_timestamp + epoch)

This will have datetime in tuple format like {{2016, 4, 28}, {00:50:12}}

You can convert that tuple to an Ecto.DateTime with Ecto.DateTime.from_erl/1

But check your assumptions. Maybe you need timezone information. And I see you have the value 0 in your example. Do you really want 1970-01-01 00:00:00 to represent "no value"?

1
votes

You can use DateTime.from_unix/2 to transform unix timestamp to DateTime struct, like below

# Change unit to millisecond, the default is second
timestamps |> DateTime.from_unix(:millisecond)

When you have a DateTime construct, you can just pass it to Ecto models field. Ecto supports 2 field types naive_datetime and utc_datetime which corresponding to NaiveDateTime and DateTime structs. You can choose what you want and Ecto will do the transformation for you.

Finally, both 2 field types will be transformed to the same type in database, i.e. timestamp without time zone in PostgreSQL. So you can easily switch the model schema.