Eloquent Generates Valid SQL that throws an error.

Posted 4 weeks ago by Stubbs

I have a bit of a strange problem, an SQL query built by Eloquent is throwing an error when I run it from my app, but when I cut & paste the same SQL into a CLI client, it works fine.

The database is PostgreSQL 10.6, Laravel 5.8 and the code to get the objects back is fairly simple:

$team->events()
                     ->where('kick_off', '>=', 'now()')
                     ->where('kick_off', '<=', "now() + INTERVAL '1 MONTH'")
                     ->get()

Which, according to the error log generates this SQL:

select * from "events" where "events"."team_id" = ea3c5907-bc48-4fd4-8f90-e42fcd29bb33 and "events"."team_id" is not null and "kick_off" >= now() and "kick_off" <= now() + INTERVAL '1 MONTH'

The full error that's being thrown is:

    * SQLSTATE[22007]: Invalid datetime format: 7 ERROR: invalid input syntax for type timestamp: "now() + INTERVAL '1 MONTH'" (SQL: select * from "events" where "events"."team_id" = ea3c5907-bc48-4fd4-8f90-e42fcd29bb33 and "events"."team_id" is not null and "kick_off" >= now() and "kick_off" <= now() + INTERVAL '1 MONTH') (22007)
    * SQLSTATE[22007]: Invalid datetime format: 7 ERROR: invalid input syntax for type timestamp: "now() + INTERVAL '1 MONTH'" (22007)

I need to wrap the team ID in single quotes to get it to run, but when I use the psql to run it, it works fine. (I don't get the same error when I leave the quotes out, I guess that's because it's using a prepared statement?)

For completeness, the table structure is this:

sports_admin=# \d events
                            Table "public.events"
   Column   |              Type              | Collation | Nullable | Default
------------+--------------------------------+-----------+----------+---------
 id         | uuid                           |           | not null |
 created_at | timestamp(0) without time zone |           |          |
 updated_at | timestamp(0) without time zone |           |          |
 title      | character varying(255)         |           | not null |
 kick_off   | timestamp(0) without time zone |           | not null |
 meet_time  | timestamp(0) without time zone |           |          |
 address_id | integer                        |           | not null |
 team_id    | uuid                           |           | not null |
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.