Date Arithmetic

Use date() to write time-based conditions and calculations in plain, readable arithmetic — ObjectQuel handles the database-specific translation automatically.

explanation

Temporal Arithmetic with date()

There are two kinds of date() value:

  • Point in time — date("now") or date(p.createdAt). Hydrated as a \DateTime object when retrieved.
  • Duration — date("6 days"), date("2 hours"), etc. Hydrated as a plain int (seconds) when retrieved.

The result type of an arithmetic expression follows from the operand types:

LeftOpRightResultExample
point in time+durationpoint in timeorder date + 30 days
duration+point in timepoint in time30 days + order date
duration+durationduration6 days + 2 hours
point in time-durationpoint in timenow − 30 days
point in time-point in timedurationnow − created date
duration-durationduration6 days − 1 day

Argument forms:

  • date("now") — current time as a Unix timestamp
  • date("N unit") — a duration: "6 days", "2 hours", "1 year 30 minutes"
  • date(p.createdAt) — a datetime column converted to a Unix timestamp

Supported interval units: second(s), minute(s), hour(s), day(s), week(s), month(s), year(s). Composite intervals are supported: "4 years 20 minutes".

// Filter posts created within the last 30 days
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\PostEntity
    retrieve (p)
    where date(p.createdAt) > date(\"now\") - date(\"30 days\")
");

// Retrieve a datetime column as a \DateTime object (point in time)
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\PostEntity
    retrieve (created = date(p.createdAt))
    where p.id = 1
");
$created = $results[0]['created']; // \DateTime

// Retrieve the age of a post as an integer number of seconds (duration)
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\PostEntity
    retrieve (age = date(\"now\") - date(p.createdAt))
    where p.id = 1
");
$ageInSeconds = $results[0]['age']; // int
Mixing a date() value with a plain scalar in arithmetic (e.g. date("6 days") + 1) is a type error and is rejected at query time. Both operands of + or - must be date() values when either one is.