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.


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:


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)


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.