Cannot load "05:00:00" as type :time, how to fix?

Greetings,

I have a very small project for which I want to use the Sqlite backend. I am experimenting with the Phoenix 1.6 rc. Right now there is just one table, “alarms”, and it has this structure:

CREATE TABLE IF NOT EXISTS "alarms" ("id" INTEGER PRIMARY KEY, "fire_time" TIME, "fire_date" DATE, "dow" JSON, "inserted_at" TEXT_DATETIME NOT NULL, "updated_at" TEXT_DATETIME NOT NULL);

It seem that I don’t have any trouble inserting rows into the table, but when I try to get them back out, I get

Repo.get(Lizzieclock.Clock.Alarm, 5)    
[debug] QUERY OK source="alarms" db=0.1ms idle=1484.9ms
SELECT a0."id", a0."dow", a0."fire_date", a0."fire_time", a0."inserted_at", a0."updated_at" FROM "alarms" AS a0 WHERE (a0."id" = ?) [5]
** (ArgumentError) cannot load `"05:00:00"` as type :time for field :fire_time in %Lizzieclock.Clock.Alarm{__meta__: #Ecto.Schema.Metadata<:loaded, "alarms">, dow: nil, fire_date: nil, fire_time: nil, id: nil, inserted_at: nil, updated_at: nil}
    (ecto 3.7.1) lib/ecto/repo/queryable.ex:409: Ecto.Repo.Queryable.struct_load!/6
    (ecto 3.7.1) lib/ecto/repo/queryable.ex:233: anonymous fn/5 in Ecto.Repo.Queryable.preprocessor/3
    (elixir 1.12.2) lib/enum.ex:1582: Enum."-map/2-lists^map/1-0-"/2
    (ecto 3.7.1) lib/ecto/repo/queryable.ex:224: Ecto.Repo.Queryable.execute/4
    (ecto 3.7.1) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (ecto 3.7.1) lib/ecto/repo/queryable.ex:146: Ecto.Repo.Queryable.one/3

All the objects were generated with the built-in generators. What am I missing here? Thanks for the help!

Looks like (meaning I scanned the code but haven’t tried any of this):

  1. There is no TIME data type in sqlite so I think its being interpreted as TEXT.
  2. ecto_sqlite3 doesn’t have any special handling for time fields, but it does for date time.

If the data is from an existing database then I think you’ll need to interpret that fire_time as text and convert it in your own code, perhaps using Time.from_iso8601/1 which can consume “05:00:00”.

3 Likes

Thanks for the response Kip!

So it appears that Sqlite does have a TIME field type. The database was generated entirely from Ecto, it’s not a previously existing db.

CREATE TABLE IF NOT EXISTS "alarms" ("id" INTEGER PRIMARY KEY, "fire_time" TIME, "fire_date" DATE, "dow" JSON, "inserted_at" TEXT_DATETIME NOT NULL, "updated_at" TEXT_DATETIME NOT NULL);

So I’m guessing this has something to do with a gap in the newer Sqlite support, because if I switch the DB to Postgres, it all works fine. It’s OK, I can use Pg. I wish I knew enough about Phoenix internals to track this down and fix it.

Does it?

https://www.sqlite.org/datatype3.html

It can infer date/time but only when it’s ISO8601, a floating-point number describing a Julian day number, or UNIX time (integer; in seconds from 1 Jan 1970).

Your format is not one of these three.

sqlite3 support is very much work in progress in Elixir still, btw.

This tutorial can help bring the issues to light: SQLite Date & Time - How To Handle Date and Time in SQLite

Ah ha, so my ignorance of the flexibility of Sqlite is showing. I did not know that its typing was so dynamic, I mistakenly assumed that if I declared a column with type “TIME” then it really was a TIME type! Mea culpa.

Better times for sqlite3 are coming but most work still should be done in CHECK constraints and/or application-level libraries (like Ecto or any other data mapper / ORM library).

You should just make double sure everything is casted to what you need at the boundaries of your system, basically.