Skip to main content

Date & Time Functions

Date and time functions operate on temporal values such as dates, timestamps, or intervals.
They are used to extract or compare time-based information, compute durations, and align values to time units.

Usage​

Date and time functions are expressed using structured DQL expressions and follow specific forms based on the number of inputs:

  • $binary: for functions that take exactly two operands (e.g. difference between timestamps)
  • $variadic: for functions that require multiple operands (e.g. dateDiff)
  • $unary: not used in temporal functions

Operands must be valid expressions that evaluate to date, timestamp, or interval types. This includes:

  • column references (e.g. $identifier)
  • scalar values (e.g. ISO date strings)
  • computed expressions (e.g. nested function calls)

If a raw string is used, casting may be necessary using $cast or $shortCast.

Date and time functions expect a specific number of operands. Using too few or too many will result in a validation error.

$dateTrunc​

Added in: indicate.dsl.dql@1.0.0
Date truncation functions allow rounding or truncating a date to a specified partition, such as year, month, or day.

  {
  "$fn": "dateTrunc",
  "$operands": [
    { object (Expression) },
    { object (Expression) }
  ]
}
Example: 
{
  "$fn": "dateTrunc",
  "$operands": [
    { "$scalar": "month" },
    {
      "$qualifier": { "$table": "events" },
      "$identifier": "event_time"
    }
  ]
}

$dateDiff​

Added in: indicate.dsl.dql@1.0.0
Calculates the difference between two dates or timestamps.

{
  "$fn": "dateDiff",
  "$operands": [
    { object (Expression) },
    { object (Expression) },
    { object (Expression) }
  ]
}
Example: 
{
  "$fn": "dateDiff",
  "$operands": [
    { "$scalar": "day" },
    {
      "$qualifier": { "$table": "orders" },
      "$identifier": "order_date"
    },
    {
      "$qualifier": { "$table": "orders" },
      "$identifier": "delivery_date"
    }
  ]
}

$currentDate​

Added in: indicate.dsl.dql@1.0.0
Returns the current date in the local timezone.

{
  "$fn": "currentDate"
}
Example: 
{
  "$fn": "currentDate"
}

$currentTime​

Added in: indicate.dsl.dql@1.0.0
Returns the current time in the local timezone.

{
  "$fn": "currentTime"
}
Example: 
{
  "$fn": "currentTime"
}

$currentTimestamp​

Added in: indicate.dsl.dql@1.0.0
Returns the current timestamp in the local timezone.

{
  "$fn": "currentTimestamp"
}
Example: 
{
  "$fn": "currentTimestamp"
}

$datePart​

Added in: indicate.dsl.dql@1.0.0
Extracts a specified part from a date or timestamp.

{
  "$fn": "datePart",
  "$operands": [
    { object (Expression) },
    { object (Expression) }
  ]
}
Example: 
{
  "$fn": "datePart",
  "$operands": [
    { "$scalar": "year" },
    {
      "$qualifier": { "$table": "events" },
      "$identifier": "event_time"
    }
  ]
}

$extract​

Added in: indicate.dsl.dql@1.0.0
Alias for datePart; extracts a specified part from a date or timestamp.

{
  "$fn": "extract",
  "$operands": [
    { object (Expression) },
    { object (Expression) }
  ]
}
Example: 
{
  "$fn": "extract",
  "$operands": [
    { "$scalar": "month" },
    {
      "$qualifier": { "$table": "orders" },
      "$identifier": "order_date"
    }
  ]
}

$strftime​

Added in: indicate.dsl.dql@1.0.0
Formats a date or timestamp using a format string (e.g., '%Y-%m-%d').

{
  "$fn": "strftime",
  "$operands": [
    { object (Expression) },
    { object (Expression) }
  ]
}
Example: 
{
  "$fn": "strftime",
  "$operands": [
    {
      "$qualifier": { "$table": "events" },
      "$identifier": "event_date"
    },
    { "$scalar": "%Y-%m-%d" }
  ]
}