Order entries by custom date and time fields

Question

I have an events channel where each entry has three custom date/time fields:

startDate (Date Only)

startTime (Time Only)

endTime (Time Only)

I was originally using this code to set my entries, but it causes problems if they aren't all entered on the same day:

{% set allEntries = craft.entries.section('sparkSchedule').limit(null).orderBy('startDate asc').all() %}

I've seen answers from Craft 2 saying that i could use time(startTime) to sort by just the time portion of my startTime field:

{% set allEntries = craft.entries.section('sparkSchedule').limit(null).orderBy('startDate asc, time(startTime)').all() %}

but when I do that, i get this error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'startTime' in 'order clause'

Am I doing something wrong?

Answer 1

You'll want to change that startTime to field_startTime.

Anything in the orderBy gets passed directly onto SQL and Craft is smart enough to do the replacement for startDate behind the scenes, but columns in methods (like time()) or aggregate functions need to be manually prefixed. This should work:

{% set allEntries = craft.entries.section('sparkSchedule').limit(null).orderBy('startDate asc, time(field_startTime)').all() %}