SoliDB Docs
Back to SDBQL Reference

Date Functions

Functions for working with dates, times, and timestamps in SDBQL.

Date & Time Functions

SDBQL provides comprehensive date and time functions for parsing, formatting, calculating, and manipulating temporal data.

Current Time & Conversion

DATE_NOW()

Returns current Unix timestamp (ms).

RETURN DATE_NOW() --1733234387000
DATE_ISO8601(timestamp)

Converts timestamp to ISO 8601 string.

RETURN DATE_ISO8601(1733234387000)
--"2025-12-03T13:59:47.000Z"
DATE_TIMESTAMP(date)

Converts ISO 8601 string to timestamp (ms).

RETURN DATE_TIMESTAMP("2025-12-03T13:59:47.000Z")
--1764770387000
HUMAN_TIME(date)

Converts date to relative human-readable string (e.g. "5 minutes ago").

RETURN HUMAN_TIME(DATE_SUBTRACT(DATE_NOW(), 5, "minutes"))
--"5 minutes ago"

Component Extraction

DATE_YEAR/MONTH/DAY(date)

Extracts year, month, or day.

RETURN DATE_MONTH(DATE_NOW())
DATE_HOUR/MINUTE/SECOND(date)

Extracts time components.

RETURN DATE_HOUR("2024-01-01T15:00:00Z") --15
DATE_DAYOFWEEK(date)

Returns day of week (0=Sunday to 6=Saturday).

RETURN DATE_DAYOFWEEK("2024-01-07") --0
DATE_QUARTER(date)

Returns quarter of the year (1-4).

RETURN DATE_QUARTER("2024-12-01") --4
DATE_ISOWEEK(date)

Returns ISO 8601 week number (1-53).

RETURN DATE_ISOWEEK("2025-06-15T14:30:45Z")
--24
DATE_DAYOFYEAR(date, tz?)

Returns day of year (1-366). Optional timezone.

RETURN DATE_DAYOFYEAR("2025-06-15T14:30:45Z")
--166
DATE_DAYS_IN_MONTH(date, tz?)

Returns days in the month (28-31). Optional timezone.

RETURN DATE_DAYS_IN_MONTH("2024-02-15T00:00:00Z")
--29 (leap year)

DATE_TRUNC Truncate Date

Truncates date to specified unit. Returns ISO 8601 string.

DATE_TRUNC(date, unit, timezone?)

Parameters

  • date - timestamp (ms) or ISO 8601 string
  • unit - y/year, m/month, d/day, h/hour, i/minute, s/second, f/millisecond
  • timezone - optional IANA timezone (e.g. "America/New_York")

Examples

DATE_TRUNC("2025-06-15T14:30:45Z", "day")
--"2025-06-15T00:00:00.000Z"

DATE_TRUNC("2025-06-15T20:30:00Z", "day", "America/New_York")
--"2025-06-15T04:00:00.000Z"

DATE_FORMAT Format Date

Formats date according to format string (strftime-style).

DATE_FORMAT(date, format, timezone?)

Format Specifiers

%Y - year (2025) %m - month (01-12) %d - day (01-31) %H - hour 24h (00-23) %M - minute (00-59) %S - second (00-59) %I - hour 12h (01-12) %p - AM/PM %A - weekday (Sunday) %B - month (June) %j - day of year %V - ISO week

Examples

DATE_FORMAT("2025-06-15T14:30:45Z", "%Y-%m-%d")
--"2025-06-15"

DATE_FORMAT("2025-06-15T14:30:45Z", "%I:%M %p")
--"02:30 PM"

DATE_FORMAT("2025-06-15T14:30:00Z", "%H:%M", "America/New_York")
--"10:30"

DATE_ADD Add Time

Add or subtract a specified amount of time to/from a date. Returns ISO 8601 string.

DATE_ADD(date, amount, unit, timezone?)

Parameters

  • date - timestamp (ms) or ISO 8601 string
  • amount - number to add (positive) or subtract (negative)
  • unit - y/year, m/month, w/week, d/day, h/hour, i/minute, s/second, f/millisecond
  • timezone - optional IANA timezone

Examples

DATE_ADD("2025-06-15T14:30:45Z", 3, "months")
--"2025-09-15T14:30:45.000Z"

DATE_ADD("2025-06-15T14:30:45Z", -7, "days")
--"2025-06-08T14:30:45.000Z"

DATE_ADD(1733234387000, 2, "hours")
--"2024-12-03T15:59:47.000Z"

DATE_SUBTRACT Subtract Time

Subtract a specified amount of time from a date. Convenience wrapper for DATE_ADD with negated amount.

DATE_SUBTRACT(date, amount, unit, timezone?)

Parameters

  • date - timestamp (ms) or ISO 8601 string
  • amount - number to subtract (always positive)
  • unit - y/year, m/month, w/week, d/day, h/hour, i/minute, s/second, f/millisecond
  • timezone - optional IANA timezone

Examples

DATE_SUBTRACT("2025-06-15T14:30:45Z", 7, "days")
--"2025-06-08T14:30:45.000Z"

DATE_SUBTRACT("2025-06-15T14:30:45Z", 3, "months")
--"2025-03-15T14:30:45.000Z"

DATE_SUBTRACT(1733234387000, 1, "day")
--"2024-12-02T13:59:47.000Z"

DATE_DIFF Date Difference

Calculate the difference between two dates in a given time unit. Returns negative if date2 is before date1.

DATE_DIFF(date1, date2, unit, asFloat?, tz1?, tz2?)

Parameters

  • date1 - timestamp (ms) or ISO 8601 string
  • date2 - timestamp (ms) or ISO 8601 string
  • unit - y/year, m/month, w/week, d/day, h/hour, i/minute, s/second, f/millisecond
  • asFloat - optional, preserve decimal places (default: false)
  • timezone1 - optional IANA timezone for date1
  • timezone2 - optional IANA timezone for date2

Examples

DATE_DIFF("2025-06-01T00:00:00Z", "2025-06-11T00:00:00Z", "days")
--10

DATE_DIFF("2025-06-15T00:00:00Z", "2025-06-15T12:00:00Z", "days", true)
--0.5

DATE_DIFF("2025-06-15T00:00:00Z", "2025-06-10T00:00:00Z", "days")
---5 (negative: date2 before date1)

TIME_BUCKET Time Series Bucketing

Buckets timestamp into fixed intervals. Useful for time series aggregation.

TIME_BUCKET(time, interval)

Example

-- Group events into 5-minute buckets
FOR event IN events
  COLLECT bucket = TIME_BUCKET(event.timestamp, "5m")
  WITH COUNT INTO count
  RETURN { bucket, count }

HUMAN_TIME Human Readable Time

Converts a timestamp or ISO 8601 string into a human-readable relative time string (e.g., "2 hours ago", "3 days from now").

HUMAN_TIME(date, now?)

Parameters

  • date - timestamp (ms) or ISO 8601 string
  • now - optional reference date (default: current time)

Examples

HUMAN_TIME(DATE_SUBTRACT(DATE_NOW(), 5, "minutes"))
--"5 minutes ago"

HUMAN_TIME(DATE_ADD(DATE_NOW(), 2, "hours"))
--"2 hours from now"

HUMAN_TIME("2025-06-15T14:30:00Z", "2025-06-15T14:35:00Z")
--"5 minutes ago"

HIGHLIGHT Result Highlighting

Wraps matching terms in text with <b> tags (case-insensitive). Useful for displaying search results.

HIGHLIGHT(text, terms)

Example

LET text = "The quick brown fox jumps over the lazy dog"
RETURN HIGHLIGHT(text, ["fox", "dog"])
-- "The quick brown fox jumps over the lazy dog"

Practical Search Examples

-- Get events from the last 7 days
FOR event IN events
  FILTER event.created_at >= DATE_SUBTRACT(DATE_NOW(), 7, "days")
  RETURN event

-- Group orders by month
FOR order IN orders
  COLLECT month = DATE_TRUNC(order.date, "month")
  AGGREGATE total = SUM(order.amount)
  RETURN { month, total }

-- Calculate user age
FOR user IN users
  LET age = DATE_DIFF(user.birthdate, DATE_NOW(), "years")
  RETURN { name: user.name, age }

-- Format date for display
FOR doc IN documents
  RETURN {
    title: doc.title,
    created: DATE_FORMAT(doc.created_at, "%B %d, %Y at %I:%M %p")
  }
String Functions Array Functions