What causes "Cannot add hour, minutes or seconds to a date" in prestodb/presto?

Use an interval with whole days only: date + INTERVAL '5' DAY.

prestodb/presto · error · PrestoException

INVALID_FUNCTION_ARGUMENT

INVALID_FUNCTION_ARGUMENT

Error message

Cannot add hour, minutes or seconds to a date

What it means

datePlusIntervalDayToSecond rejects adding an INTERVAL DAY TO SECOND to a DATE when the interval has a nonzero time-of-day component (hours/minutes/seconds), since a DATE has no time part. Presto raises INVALID_FUNCTION_ARGUMENT for the operation.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/type/DateTimeOperators.java:48

import static com.facebook.presto.spi.StandardErrorCode.INVALID_FUNCTION_ARGUMENT;
import static com.facebook.presto.util.DateTimeZoneIndex.getChronology;
import static com.facebook.presto.util.DateTimeZoneIndex.unpackChronology;

public final class DateTimeOperators
{
    private static final DateTimeField MILLIS_OF_DAY = ISOChronology.getInstanceUTC().millisOfDay();
    private static final DateTimeField MONTH_OF_YEAR_UTC = ISOChronology.getInstanceUTC().monthOfYear();

    private DateTimeOperators()
    {
    }

    @ScalarOperator(ADD)
    @SqlType(StandardTypes.DATE)
    public static long datePlusIntervalDayToSecond(@SqlType(StandardTypes.DATE) long left, @SqlType(StandardTypes.INTERVAL_DAY_TO_SECOND) long right)
    {
        if (MILLIS_OF_DAY.get(right) != 0) {
            throw new PrestoException(INVALID_FUNCTION_ARGUMENT, "Cannot add hour, minutes or seconds to a date");
        }
        return left + TimeUnit.MILLISECONDS.toDays(right);
    }

    @ScalarOperator(ADD)
    @SqlType(StandardTypes.DATE)
    public static long intervalDayToSecondPlusDate(@SqlType(StandardTypes.INTERVAL_DAY_TO_SECOND) long left, @SqlType(StandardTypes.DATE) long right)
    {
        if (MILLIS_OF_DAY.get(left) != 0) {
            throw new PrestoException(INVALID_FUNCTION_ARGUMENT, "Cannot add hour, minutes or seconds to a date");
        }
        return TimeUnit.MILLISECONDS.toDays(left) + right;
    }

    @ScalarOperator(ADD)
    @SqlType(StandardTypes.TIME)
    public static long timePlusIntervalDayToSecond(SqlFunctionProperties properties, @SqlType(StandardTypes.TIME) long left, @SqlType(StandardTypes.INTERVAL_DAY_TO_SECOND) long right)
    {

View on GitHub (pinned to 55bb57d202)

Solutions

Use an interval with whole days only: date + INTERVAL '5' DAY.
Truncate the interval's time component before adding, or convert the date to TIMESTAMP first if sub-day precision is needed.
If time-of-day math is required, cast the DATE to TIMESTAMP and add the full interval there.

Example fix

// before
SELECT d + INTERVAL '2 06:30:00' DAY TO SECOND FROM t;
// after
SELECT CAST(d AS TIMESTAMP) + INTERVAL '2 06:30:00' DAY TO SECOND FROM t;

Defensive patterns

Strategy: validation

Validate before calling

-- reject intervals with a time-of-day component before adding to a DATE
SELECT * FROM t
WHERE date_add('millisecond', MILLIS_TO_SECONDS_PART(iv), DATE '1970-01-01') IS NOT NULL
  -- simpler: ensure the literal only uses DAY fields:
  AND NOT regexp_like(cast(iv AS VARCHAR), '\d+ \d{2}:');

Type guard

boolean isWholeDays(org.joda.time.ReadableDuration d) { return d.getMillis() % 86400000L == 0; }

Try / catch

try { result = date + iv; } catch (PrestoException e) { /* INVALID_FUNCTION_ARGUMENT: fall back to TIMESTAMP addition */ }

Prevention

Use INTERVAL 'n' DAY literals when the target column is DATE.
Cast DATE to TIMESTAMP whenever sub-day intervals are involved.
Truncate durations to whole days before date arithmetic.

When it happens

Trigger: Evaluating date + INTERVAL '5 06:00:00' DAY TO SECOND, or any interval whose MILLIS_OF_DAY component is nonzero.

Common situations: Queries ported from systems that truncate time parts, or application code building intervals from durations that include sub-day components.

Related errors

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/d94e32cfcde29dd3. Report an issue: GitHub.