How to convert Date to DateTime which Ecto needs

Questions / Help
,
1

Hello, I get Persian date from my client and convert it to normal calendar like this:

  def jalali_string_to_miladi_english_number(persian_string_time_number) do
    [yy, mm, dd] = String.split(persian_string_time_number, "/")
    {:ok, jalaali_date} = Date.new(Numero.normalize_as_number!(yy), Numero.normalize_as_number!(mm), Numero.normalize_as_number!(dd), Jalaali.Calendar)
    {:ok, iso_date} = Date.convert(jalaali_date, Calendar.ISO)
    iso_date
  end

my output is:

~D[2019-05-21]

but I can’t save this in my database and have this error:

value `~D[2019-05-21]` for `TransactionSchema.purchase_time` in `insert` does not match type :utc_datetime

how can convert it to utc_datetime can be saved in db with ecto like this DateTime.utc_now ?

Thanks

2

Add a bogus time to your Date to change it to DateTime. e.g. “2019-05-21 00:00:00Z”

1 Like
3 
value `~D[2019-05-21]` for `TransactionSchema.purchase_time` in `insert` does not match type :utc_datetime

Your processing suggests that you only want the date.

Your schema type and name suggests that you want the time as well.

If you only want the date then have a purchase_date of type :date rather than :utc_datetime.

If you want the time, then record the full date, time, and timezone.

Using a datetime type to store a date will only lead to confusion and bugs in the long run - this is especially true when timezones are involved. For example a thoughtless shift in timezone:

iex(1)> DateTime.from_iso8601("2019-05-21 00:00:00Z")
{:ok, #DateTime<2019-05-21 00:00:00Z>, 0}
iex(2)> DateTime.from_iso8601("2019-05-20 23:00:00-01:00")
{:ok, #DateTime<2019-05-21 00:00:00Z>, -3600}
iex(3)>

can lead to a different date for the same “point in time”.

3 Likes
4

Hmm…, I am confused, I need to save my user time zone I think, let me explain:
I have 2 user in the system Include (admin , user).

  1. admin can save a time that should Include a purchase_date, this field has to have (year, month, day, hour, min)
  def jalali_string_to_miladi_english_number(persian_string_time_number) do
    [yy, mm, dd] = String.split(persian_string_time_number, "/")
    {:ok, jalaali_date} = Date.new(Numero.normalize_as_number!(yy), Numero.normalize_as_number!(mm), Numero.normalize_as_number!(dd), Jalaali.Calendar)
    {:ok, iso_date} = Date.convert(jalaali_date, Calendar.ISO)
    {:ok, datetime, 0} = DateTime.from_iso8601("#{iso_date.year}-#{fix_month_and_day(iso_date.month)}-#{fix_month_and_day(iso_date.day)}T00:00:00Z")
    datetime
  end

In top code I have written I try to convert persian calendar to normal calendar like (2018), now I think I have a problem. where is my country Time zone (Iran/Tehran 3:30) , my country type is GMT ( UTC ) : (http://www.timebie.com/std/tehran.php?q=3.5)

but when I test this it backs me (“Etc/UTC”)

{:ok, times, zone} = DateTime.from_iso8601("2019-05-23T23:50:07.123+03:30")
iex(13)> times.time_zone
"Etc/UTC"

I should change my table name or not , but am I right in this way ?

just one thing I should explain, if the time was stored in db I have no problem because I make it to my calendar but it is wrong when I want to create a datetime in db and write time like (13:24)

1 Like
5

Given:

iex(1)> {:ok, datetime, offset} = DateTime.from_iso8601("2019-05-23 00:00:00+03:30")
{:ok, #DateTime<2019-05-22 20:30:00Z>, 12600}

“2019-05-22 20:30:00Z” is the time that is stored in the database.

:utc_datetime is a datetime which is implicitly understood to be with reference to UTC. There is no actual timezone being stored in :utc_datetime. So when it’s retrieved you would have to DateTime.shift_zone/3 the time to the IRST timezone - which only works if you have a custom Time zone database configured. Only after the shift will it convert to the correct local date.

Otherwise:

iex(1)> {:ok, persisted_time, offset} = DateTime.from_iso8601("2019-05-22 20:30:00Z")
{:ok, #DateTime<2019-05-22 20:30:00Z>, 0}
iex(2)> DateTime.to_string(persisted_time)
"2019-05-22 20:30:00Z"
iex(3)> DateTime.to_date(persisted_time)
~D[2019-05-22]
iex(4)>

I expect that the Date type (:date) is understood to represent a local date.

3 Likes
6 
  def convert_date_to_datetime(%DateTime{} = date), do: date
  def convert_date_to_datetime(%Date{} = date) do
    date
    |> Date.to_gregorian_days()
    |> Kernel.*(86400)
    |> Kernel.+(86399)
    |> DateTime.from_gregorian_seconds()
  end