Convert naive_datetime to utc_datetime_usec

Question

I have a model with this schema:

schema "my_things" do
    (...)
    field :shifted_inserted_at, :utc_datetime_usec
    timestamps()
  end

timestamps() type defaults to :naive_datetime.

I need to shift my inserted_at value and insert it into shifted_inserted_at.

How do I convert the NaiveDatetime to the right format? I've tried something like this:

  shifted_inserted_at =
    my_thing.inserted_at
    |> DateTime.from_naive!("Etc/UTC")
    |> Timex.shift(days: 10)

  my_thing
  |> Ecto.Changeset.change(shifted_inserted_at: shifted_inserted_at)
  |> Repo.update()

But I get:

** (ArgumentError) :utc_datetime_usec expects microsecond precision, got: #DateTime<2019-05-30 14:40:08Z>

Answer 1

DateTime type has a field named microsecond. It’s a tuple denoting a value and a precision.

iex|1 ▶ DateTime.from_naive!(
...|1 ▶   ~N[2016-05-24 13:26:08], "Etc/UTC").microsecond       
#⇒ {0, 0}
iex|2 ▶ DateTime.from_naive!(
...|2 ▶   ~N[2016-05-24 13:26:08.123456], "Etc/UTC").microsecond
#⇒ {123456, 6}

As one might see, unless the actual value has indeed microseconds, the precision is zero.

Assuming the value in the database, returned by my_thing.inserted_at has microseconds precision, one might simply:

~N[2016-05-24 13:26:08.123456]
|> DateTime.from_naive!("Etc/UTC")
|> DateTime.add(10 * 24 * 60 * 60) # 10 × seconds in a day
#⇒ #DateTime<2016-06-03 13:26:08.123456Z>

If the value does not have microseconds precision,

iex|3 ▶ DateTime.from_naive!(~N[2016-05-24 13:26:08], "Etc/UTC")
#        PRECISION ⇒ ZERO!    ⇓⇓⇓ 
#⇒ #DateTime<2016-05-24 13:26:08Z>

one might always set it manually:

with dt <- DateTime.from_naive!(~N[2016-05-24 13:26:08], "Etc/UTC"),
  do: %DateTime{dt | microsecond: {elem(dt.microsecond, 0), 6}}
#⇒ #DateTime<2016-05-24 13:26:08.000000Z>

The latter might be now inserted into the database.

Answer 2

I just came across this post as well and found another solution that helped me, so I thought I'd share.

If you're using Ecto, you can use Ecto.Type.cast/2 like the following:

with {:ok, naive_datetime} <- NaiveDateTime.utc_now(),
     {:ok, datetime} <- DateTime.from_naive(naive_datetime, "Etc/UTC"),
     {:ok, utc_datetime_usec) <- Ecto.Type.cast(:utc_datetime_usec, datetime),
     do: utc_datetime_usec

A bit of a contrived example, but you can substitute whatever data as needed. It's just meant to illustrate casting data with Ecto.