Oracle: time fields round-trip as timestamp, breaking insert, read and filter

Describe the Bug

On Oracle, a field created with type time does not survive the schema round-trip — it comes back as timestamp. Every code path that branches on the field type then treats "10:15:30" as a timestamp, so reading, writing and filtering a time field all break.

1. The column is created as a timestamp. Oracle has no TIME type. addColumnToTable falls through to table[field.type](field.field), and knex's oracledb dialect maps .time() to timestamp with local time zone:

COLUMN_NAME: 'tm', DATA_TYPE: 'TIMESTAMP(6) WITH LOCAL TIME ZONE'

2. Nothing records that it was meant to be a time. DateHelperOracle.fieldFlagForField only returns a cast-* special for json and dateTime; there is no cast-time, so special stays null.

3. So the field reads back as timestamp. getLocalType does data_type.toLowerCase().split('(')[0] → "timestamp" → 'timestamp'.

4. PayloadService.processDates then parses the time string as a timestamp. The read branch does new Date("10:15:30").toISOString(), which throws RangeError: Invalid time value. This fires from prepareDelta while building the revision delta, so the request 500s before the insert is even attempted.

Two further consequences:

• The write path would corrupt silently rather than fail. The timestamp write branch calls writeTimestamp → parseISO("10:15:30") → Invalid Date, with no isValid guard — unlike the date and dateTime branches immediately above it, which throw InvalidPayloadError. Binding an Invalid Date to Oracle does not error; it stores garbage. A direct knex insert of Invalid Date into the column above stored -004713-01-28T16:19:15.000Z. This missing guard is not Oracle-specific and is worth fixing on its own.
• Filtering on a time value fails for a separate reason. DateHelperOracle.parse only short-circuits strings that are ≤10 chars and contain -. "10:15:30" misses that and falls through to new Date(date).toISOString(), throwing the same RangeError.

There is an existing workaround for this in the e2e suite — tests/e2e/tests/fields/timezone/timezone.sb.test.ts strips time from the payload and skips the assertion when the database is Oracle.

To Reproduce

Against a Directus instance backed by Oracle (reproduced on gvenzl/oracle-free:23-slim-faststart):

1. Create a collection with a time field:

curl -X POST -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' \
  -d '{"collection":"repro_time","schema":{},"meta":{},"fields":[
        {"field":"id","type":"integer","schema":{"is_primary_key":true,"has_auto_increment":true},"meta":{"hidden":true}},
        {"field":"tm","type":"time","schema":{},"meta":{}}]}' \
  "$URL/collections"

1. Read the field back — note "type": "timestamp", not "time":

curl -H "Authorization: Bearer $TOKEN" "$URL/fields/repro_time/tm"

{ "type": "timestamp", "schema": { "data_type": "TIMESTAMP(6) WITH LOCAL TIME ZONE" } }

1. Insert a time value:

curl -X POST -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' \
  -d '{"tm":"10:15:30"}' "$URL/items/repro_time"

Expected: the item is created and tm reads back as "10:15:30".

Actual: 500 INTERNAL_SERVER_ERROR

RangeError: Invalid time value
    at Date.toISOString (<anonymous>)
    at PayloadService.processDates (api/dist/services/payload.js:267)
    at PayloadService.processValues
    at PayloadService.prepareDelta
    at api/dist/services/items.js:191

1. Filtering on a time value fails independently, with a different stack:

curl -H "Authorization: Bearer $TOKEN" "$URL/items/repro_time?filter\[tm\][_eq]=10:15:30"

RangeError: Invalid time value
    at Date.toISOString (<anonymous>)
    at DateHelperOracle.parse (api/dist/database/helpers/date/dialects/oracle.js:9:32)
    at applyOperator (api/dist/database/run-ast/lib/apply-query/filter/operator.js:106)
    at addWhereClauses
    at applyFilter

All four steps reproduce on every other supported database without error.

Suggested fix

Make the round-trip lossless: have DateHelperOracle.fieldFlagForField return a cast-time flag for time, and have getLocalType honor it, following the existing cast-datetime / cast-timestamp pattern. The field then stays type: 'time', processDates skips it in dateColumns, and the existing timeColumns read path formats it back as HH:mm:ss. The column still needs to hold the value sensibly — storing it against a fixed epoch date is the usual approach — and DateHelperOracle.parse needs a guard for time-shaped strings so filters work.

Separately, the timestamp write branch in processDates should get the same isValid check the date and dateTime branches have, so an unparseable value raises InvalidPayloadError instead of being written as an Invalid Date.

Directus Version

main ([email protected], @directus/[email protected])

Hosting Strategy

Self-Hosted (Custom)

Database

Oracle Database 23ai Free (gvenzl/oracle-free:23-slim-faststart)