Form for utc_datetime

azimlord · September 8, 2019, 7:43am

Hi guys,

I’m having some trouble with utc_datetime.

So I have a form that accept start_time and end_time which both in utc_datetime and inside a bookings table.

The problem is that I’m in Malaysia so the booking that is created will be in UTC and when I convert it to "Asia/Kuala_Lumpur" using timex, it will show a different time.

Timex.Timezone.convert(booking.end_time, "Asia/Kuala_Lumpur")

For example:
start_time: 2019-09-08 22:00:00
end_time: 2019-09-08 23:00:00

when convert it using timex to "Asia/Kuala_Lumpur"
start_time: 2019-09-09 06:00:00
end_time: 2019-09-09 07:00:00

Now I see that I have 2 options:

Use naive_datetime instead of utc_datetime and store the timezone in a separate column
Manipulate the start_time and end_time from the form and shift it to the UTC timezone which I already tried and failed.

changeset
|> put_change(:start_time, Timex.shift(Timex.to_datetime(start_time, "Asia/Kuala_Lumpur"), "Etc/UTC"))
|> put_change(:end_time, Timex.shift(Timex.to_datetime(end_time, "Asia/Kuala_Lumpur"), "Etc/UTC"))

I’m not sure if I did it correctly or not and I’m not sure which is the best way

Thanks for your help in advance!

dimitarvp · September 8, 2019, 11:12am

I am not sure I understand your problem exactly.

Why are you converting the time to your local time zone at all?
You want your UI to show the times in your local timezone and not in UTC, is that it?

azimlord · September 8, 2019, 12:09pm

I’m sorry you don’t understand my question

I wanted to store the time correctly if using utc_datetime

I have a model that looks like this

schema "booking" do
    field :reference, :string
    field :status, :string
    field :start_time, :utc_datetime
    field :end_time, :utc_datetime

    timestamps()
  end

And I have a form which looks like this

    <div class='input-field col s12 m4'>
        <span>Start Time</span>
        <%= datetime_local_input f, :start_time %>
        <%= error_tag f, :start_time %>
    </div>
    <div class='input-field col s12 m4'>
        <span>End Time</span>
        <%= datetime_local_input f, :end_time %>
        <%= error_tag f, :end_time %>
    </div>

But when I store it to the database and show it on a page it will look like this
2019-09-08 22:00:00Z
And I try to set it to the local time and shift it to UTC time using Timex which I show from my initial post
So when I want to display it, I will convert it to display the correct time.

For example:
From the form it, I will select the time
start_time: 2019-09-08 20:00:00
end_time: 2019-09-08 21:00:00

And convert it to UTC and store it into the database, which will change the time to:
start_time: 2019-09-08 12:00:00Z
end_time: 2019-09-08 13:00:00Z

Where ever in the world, I will show the correct time depending on the timezone. For my timezone which is "Asia/Kuala_Lumpur" will convert it back to
start_time: 2019-09-08 20:00:00
end_time: 2019-09-08 21:00:00

This how I do it currently in elixir but does not work

changeset
|> put_change(:start_time, Timex.shift(Timex.to_datetime(start_time, "Asia/Kuala_Lumpur"), "Etc/UTC"))
|> put_change(:end_time, Timex.shift(Timex.to_datetime(end_time, "Asia/Kuala_Lumpur"), "Etc/UTC"))

I’m also not sure if this is a proper way to do it or not.

So I thought of another way which using the naive_datetime instead of utc_datetime and have another column in my table where it will store the timezone as well. The model should look like this

schema "booking" do
    field :reference, :string
    field :status, :string
    field :start_time, :naive_datetime
    field :end_time, :naive_datetime
    field :timezone, (something to represent timezone)

    timestamps()
  end

Hope you understand

LostKobrakai · September 8, 2019, 1:07pm

The problem you’re facing is that the submitted value of a datetime_local_input does not include timezone information at all. It’ll send the server a (“naive”) iso8601 datetime without timezone information. It’s also unlikely to change. Your utc_datetime field will then assume it’s UTC, because that column is meant to only ever hold datetimes with UTC set as timezone.

I’ve spent some time to code up and document how I’d handle those cases and hide a bit of the boilerplate necessary. Since elixir 1.8 elixir can finally handle the problem on it’s own with a timezone database configured.

https://hexdocs.pm/tz_datetime/TzDatetime.html

azimlord · September 9, 2019, 4:13am

I’m sorry if this is a stupid question

From the library that you make is to avoid the issue that mentions in this article http://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html. Correct?

I guess, with your solution, I need to add another 2 field, which is

schema "booking" do
    field :reference, :string
    field :status, :string
    field :start_time, :utc_datetime
    field :end_time, :utc_datetime
    field :time_zone, :string          #new field
    field :original_offset, :integer   #new field

    timestamps()
end

with that, then my changeset just need to do this

require TzDatetime
changeset
    |> TzDatetime.handle_datetime(input_datetime: :start_time, datetime: :start_time)
    |> TzDatetime.handle_datetime(input_datetime: :end_time, datetime: :end_time)

before storing it into the database

and for displaying

require TzDatetime
TzDatetime.original_datetime(booking, datetime: :start_time)

will get the correct datetime

But I’m not sure how to get the original_offset. Can you help me with this?
And I’m guessing that the time_zone will be the timezone that I wanted. eg: "Asia/Kuala_Lumpur"

If there’s anything wrong with what I’m saying, please do correct me.

LostKobrakai · September 9, 2019, 9:11am

You have it mostly correct.

TzDatetime.handle_datetime(cs, input_datetime: :start_time, datetime: :start_time)

I’d suggest a virtual field for the input separate to the field storing the utc_datetime, but this should work. Also you don’t need to require TzDatetime. There are no macros involved here so just calling the functions is enough.

The timezone field you’ll need to set somehow either by letting the user submit the value or adding it to the changeset based on other information your system has.

As for the original_offset: You shouldn’t need to care about that at all. It’s set by handle_datetime/2 and read by original_datetime/2. The latter will return different values if there’s a missmatch detected between the stored offset and the offset based on your current timezone database.

azimlord · September 9, 2019, 12:17pm

Having a bit issue

So I separate the field for input and the datetime like you suggested.

field :input_start_time, :naive_datetime, virtual: true
field :input_end_time, :naive_datetime, virtual: true

changeset
    |> TzDatetime.handle_datetime(input_datetime: :input_start_time, datetime: :start_time)
    |> TzDatetime.handle_datetime(input_datetime: :input_end_time, datetime: :end_time)

When I try to create a booking, the changes are as below

changes: %{
    input_end_time: ~N[2019-09-10 11:00:00],
    input_start_time: ~N[2019-09-10 10:00:00],
    time_zone: "Asia/Kuala_Lumpur"
  }

But it returns an error

** (exit) an exception was raised:
    ** (CaseClauseError) no case clause matching: {:error, :utc_only_time_zone_database}
        (tz_datetime) lib/tz_datetime.ex:190: TzDatetime.do_handle_datetime/3

Not sure what is going

LostKobrakai · September 9, 2019, 12:31pm

Elixir itself only ships with a timezone database supporting UTC and nothing else. This is because updating the timezone db shouldn’t really be bound to the release cycles of elixir the language. Currently the only implementation for full timezone support ships with the tzdata library, which you’d need to install / configure.

azimlord · September 9, 2019, 1:12pm

Nice. I works! Thank you!

Convert the start_time and end_time using Timex, shows the correct time

Thank you very much!

LostKobrakai · September 9, 2019, 1:22pm

Please use original_datetime/2 to convert the utc_datetime back to your original timezone. It mostly does shift the timezone just like timex does, but you’ll be made aware if the timezone definitions changed to cause a different offset to the time when the datetime was stored. This will hardly ever happen for datetimes in the past, but is a valid problem to look for in future datetimes.

azimlord · September 9, 2019, 1:45pm

Noted. Changed the conversion using original_datetime/2.

Is there any other information that I should be aware of regarding the solution?
What ‘should I do’ and what ‘I should not do’?

LostKobrakai · September 9, 2019, 1:56pm

Most stuff should be in the documentation. Maybe I’ll add a paragraph about configuring the timezone db as well at a later time. I’d also gladly accepts questions/improvements on that part.

azimlord · September 18, 2019, 5:58am

Hey I’m building an API for the same function,
but when passing the input_start_time: 2019-09-20T10:00 and input_end_time: 2019-09-20T11:00 it returns an error

(ArgumentError) argument error
        :erlang.binary_to_atom(nil, :utf8)
        (tzdata) lib/tzdata/release_reader.ex:91: Tzdata.ReleaseReader.do_periods_for_zone_time_and_type/3
        (tzdata) lib/tzdata/release_reader.ex:72: Tzdata.ReleaseReader.periods_for_zone_time_and_type/3
        (tzdata) lib/tzdata.ex:242: Tzdata.possible_periods_for_zone_and_time/3
        (tzdata) lib/tzdata.ex:196: Tzdata.periods_for_time/3
        (tzdata) lib/tzdata/time_zone_database.ex:32: Tzdata.TimeZoneDatabase.time_zone_periods_from_wall_datetime/2
        (elixir) lib/calendar/datetime.ex:262: DateTime.from_naive/3
        (tz_datetime) lib/tz_datetime.ex:190: TzDatetime.do_handle_datetime/3

Do you know what am I missing?

Nicd · September 18, 2019, 6:00am

You have to show your code. It seems like you are passing a nil somewhere where a binary is expected.

LostKobrakai · September 18, 2019, 6:14am

Seems like a problem of tzdata. Did you update the library to the most recent version? Afaik every now and the there are problems with changes in the data format of the file it‘s consuming.

azimlord · September 18, 2019, 6:36am

schema "booking" do
    field :reference, :string
    field :status, :string
    field :start_time, :utc_datetime
    field :end_time, :utc_datetime
    field :time_zone, :string
    field :original_offset, :integer
    field :input_start_time, :naive_datetime, virtual: true
    field :input_end_time, :naive_datetime, virtual: true
    timestamps()
  end

def create_changeset(booking, attrs) do
    booking
    |> cast(attrs, [:reference, :status, :start_time, :end_time, :time_zone, :original_offset,  :input_start_time, :input_end_time])
    |> validate_required([:input_start_time, :input_end_time])
    |> change_start_end_timezone()
  end

defp change_start_end_timezone(changeset) do
    changeset
    |> TzDatetime.handle_datetime(input_datetime: :input_start_time, datetime: :start_time)
    |> TzDatetime.handle_datetime(input_datetime: :input_end_time, datetime: :end_time)
  end

Create function

def create(conn, %{"booking" => params}) do
    with {:ok, %Booking{} = booking} <- MyContext.create_booking(params) do
      render(conn, "show.json", booking: booking)
    end
  end

def create_booking(attrs \\ %{}) do
    %Booking{}
    |> Booking.create_changeset(attrs)
    |> Repo.insert()
 end

My request

{
	"booking": {
		"input_start_time": "2019-09-20T10:00",
		"input_end_time": "2019-09-20T11:00"
	}
}

here you go

azimlord · September 18, 2019, 6:42am

Yup. I check the code in GitHub, but I don’t really understand it.

I’m using the latest version for both tz_datetime and tzdata

[
   {:tz_datetime, "~> 0.1.1"},
   {:tzdata, "~> 1.0.1"}
]

I did not update tzdata but I tried both version 0.1.0 and 0.1.1 of your library
It worked on web, it just does not work for API

Nicd · September 18, 2019, 7:04am

Is the :time_zone field in your changeset nil or set to something proper?

azimlord · September 18, 2019, 7:16am

Thank you for telling me that. The time_zone need to be set.
My request should be like this

{
	"booking": {
		"input_start_time": "2019-09-20T10:00",
		"input_end_time": "2019-09-20T11:00",
        "time_zone": "Asia/Kuala_Lumpur"
	}
}

I forgot that I need to manually set the time_zone for tz_datetime and tzdata to work.
It is actually in the past conversation from this thread. My bad

Thank you very much!

azimlord · September 19, 2019, 9:20am

Question

Since the start_time is stored as UTC. How would you suggest to do query by date or datetime. For example:

date = "2019-09-20" #Let's say this date is from "Asia/Kuala_Lumpur"
date = Date.from_iso8601!(date)
Booking
|> where([b], fragment("?::date", b.start_time) == ^date)
|> Repo.all

The results would be wrong, where it tries to compare date from a different time zone