Trailing data errors in Carbon PHP using SQLite and the Lumen micro framework

Here’s a rather interesting error that cropped up when trying to pull together a quick web front end to a pre-existing SQLite database.

I decided to take Lumen for a spin and thought that pulling together a quick CRUD application for a two table database would be the perfect. Slightly more complicated than most trivial examples, I was hoping to have something to show for my efforts in a morning. Unfortunately, a date formatting anomaly caught me by surprise and now all I have to show for it is this blog post.

The Database

The pre-existing database has two tables in a small SQLite database. My main data entry table already has created_at and updated_at fields which I thought would be automatically supported by Lumen if I used the Eloquent data layer. I began simply enough, creating a model in my Lumen application. To keep things simple for the proof of concept, I just dumped it in my routes.php file, and tried to return ::all() against it from my default route.

The CRASH!

Whoops, looks like something went wrong.
InvalidArgumentException in Carbon.php line 392
Unexpected data found.
Trailing data

Line 2 of the exception dump we can see Carbon::createFromFormat is crashing. Carbon is a PHP API extension for DateTime. It’s unable to convert a datatime with a timezone indicator into the default format. I dropped down into the SQLite command line and called .schema on my table; created_at was defined as a TIMESTAMP.

A little research leads me to some suggestions to change the default date format for my model to 'U'. This isn’t difficult, it requires a small override in my model. No problem:

protected function getDateFormat() {
    return 'U';
}

But no. Same error. Why? My data type in my database is a TIMESTAMP. My date format is expecting a unix timestamp. So what’s gone wrong?

SQLite, Dynamic Typing and Type Affinity

Unlike many database systems, SQLite doesn’t implement a rigid type system. It implements a dynamic typing system and assigns a datatype to an individual value rather than rigidly enforcing a datatype on a per column basis. On a per-column basis, SQLite implements ‘type affinity’. This basically means that SQLite can suggest a type for a column based on it’s declared data type. This is the preferred data type for that column.

In real terms, what this boils down to, is that SQLite really only has 5 datatypes - NULL, INTEGER, REAL, TEXT and BLOB. How on earth, then, can our schema declare a TIMESTAMP datatype? Well, SQLite doesn’t impose any rigid rules on your datatype’s name. There are some rules about what type affinity a column will get depending on the name you use for that datatype.

The Raw Data

Delving into the actual raw data into the database for my created_at field, I noticed it was stored in the database not as an actual timestamp (seconds since epoch), but rather as a literal string:

2014-04-22T09:00:01+00:00

Back to Carbon. I started with a Trailing Data error. Carbon was failing because of the +00:00 part of my date time string. This portion of the string is the GMT offset. Setting my model’s dateformat to U similarly failed because Carbon was expecting a timestamp proper (i.e. seconds since the epoch).

The Fix

There are two approaches to fixing the problem

  • Fix the data - removing the GMT offset in the actual datetime string and removing the getDateFormat override works. We fallback to the default Carbon date format which subsequently works. This is a reasonable option. It removes the need to
  • Fix the getDateFormat override - making this work is about knowing your php date format options and in my case comes down to this:
protected function getDateFormat() {
    return 'Y-m-d\TH:i:sP';
}

The P at the end of the format string specifies:

Difference to Greenwich time (GMT) with colon between hours and minutes (added in PHP 5.1.3)

Fin

That’s it, we’ve succesfully fixed an initially peculiar looking error and in doing so, learned a little about SQLite’s dynamic typing and type affinity.