Time Intelligence Functions
epoch
epoch(date)
epoch(datetime)
Description
epoch returns a Unix timestamp which is the number of seconds that have elapsed since ‘1970-01-01 00:00:00’ UTC.
You can use this function to return a Unix timestamp based on the current date/datetime or another specified date/datetime.
Return type
Number
Example
Given an AQL expression as below:
epoch(orders.created_at)
The result would be:
| created_at | epoch(created_at) |
|---|---|
| 2018-06-12 09:26:49 | 1528795609 |
| 2018-06-12 | 1528761600 |
date_trunc
date_trunc(datetime, datetime_part)
Let's say that orders.created_at is 2021-05-28 10:30:39
| Function | Result |
|---|---|
orders.created_at | 2021-05-28 10:30:39 |
date_trunc(orders.created_at, 'day') | 2021-05-28 00:00:00 |
date_trunc(orders.created_at, 'month') | 2021-05-01 00:00:00 |
date_trunc(orders.created_at, 'year') | 2021-01-01 00:00:00 |
date_trunc(orders.created_at, 'quarter') | 2021-04-01 00:00:00 |
date_trunc(orders.created_at, 'week') | 2021-05-24 00:00:00 |
date_trunc(orders.created_at, 'hour') | 2021-05-28 10:00:00 |
date_trunc(orders.created_at, 'minute') | 2021-05-28 10:30:00 |
// orders.created_at -> 2021-05-28 10:30:39
date_trunc(orders.created_at, 'day') // -> 2021-05-28 00:00:00
date_trunc(orders.created_at, 'month') // -> 2021-05-01 00:00:00
date_trunc(orders.created_at, 'year') // -> 2021-01-01 00:00:00
date_trunc(orders.created_at, 'quarter') // -> 2021-04-01 00:00:00
date_trunc(orders.created_at, 'week') // -> 2021-05-24 00:00:00
date_trunc(orders.created_at, 'hour') // -> 2021-05-28 10:00:00
date_trunc(orders.created_at, 'minute') // -> 2021-05-28 10:30:00
Description
Truncates a date/datetime value to the granularity of datetime_part. The datetime value is rounded to the beginning of datetime_part. The supported parts are:
'day': The day in the Gregorian calendar year that contains thedatetimevalue.'week': The first day of the week in the week that contains thedatetimevalue. Weeks begin on the day that was set in` your Week Start Day Setting'month': The first day of the month in the month that contains thedatetimevalue.'quarter': The first day of the quarter in the quarter that contains thedatetimevalue.'year': The first day of the year in the year that contains thedatetimevalue.'hour': The hour in the day that contains thedatetimevalue.'minute': The minute in the hour that contains thedatetimevalue.
All date part has a corresponding short-hand truncate function. Examples below will use the short-hand version.
day(orders.created_at)ororders.created_at | day()month(orders.created_at)ororders.created_at | month()year(orders.created_at)ororders.created_at | year()quarter(orders.created_at)ororders.created_at | quarter()week(orders.created_at)ororders.created_at | week()hour(orders.created_at)ororders.created_at | hour()minute(orders.created_at)ororders.created_at | minute()
Return type
date or datetime depending on the input type.
date_part
Syntax
date_part(datetime_part, datetime)
Description
The date_part function extracts a specific numeric part from a date or datetime value. It returns the numeric representation of the specified part of the date.
Examples
Let's say orders.created_at is 2021-05-28 10:30:39
| Function | Result |
|---|---|
date_part('year', orders.created_at) | 2021 |
date_part('quarter', orders.created_at) | 2 |
date_part('month', orders.created_at) | 5 |
date_part('week', orders.created_at) | 22 |
date_part('dayofweek', orders.created_at) | 5 |
date_part('dow', orders.created_at) | 5 |
date_part('day', orders.created_at) | 28 |
date_part('hour', orders.created_at) | 10 |
date_part('minute', orders.created_at) | 30 |
date_part('second', orders.created_at) | 39 |
// orders.created_at -> 2021-05-28 10:30:39
date_part('year', orders.created_at) // -> 2021
date_part('quarter', orders.created_at) // -> 2
date_part('month', orders.created_at) // -> 5
date_part('week', orders.created_at) // -> 21
date_part('dayofweek', orders.created_at) // -> 4
date_part('dow', orders.created_at) // -> 4
date_part('day', orders.created_at) // -> 28
date_part('hour', orders.created_at) // -> 10
date_part('minute', orders.created_at) // -> 30
date_part('second', orders.created_at) // -> 39
Supported Date Parts
'year': Returns the numeric year (e.g., 2021)'quarter': Returns the quarter number (1-4)'month': Returns the month number (1-12)'week': Returns the week number of the year (1-53)'dayofweek'or'dow': Returns the day of the week number (0-6)'day': Returns the day of the month (1-31)'hour': Returns the hour of the day (0-23)'minute': Returns the minute of the hour (0-59)'second': Returns the second of the minute (0-59)
Shorthand Functions
Shorthand functions with _num suffix are available for quick access:
year_num(orders.created_at)ororders.created_at | year_num()quarter_num(orders.created_at)ororders.created_at | quarter_num()month_num(orders.created_at)ororders.created_at | month_num()week_num(orders.created_at)ororders.created_at | week_num()dayofweek_num(orders.created_at)ororders.created_at | dayofweek_num()dow_num(orders.created_at)ororders.created_at | dow_num()day_num(orders.created_at)ororders.created_at | day_num()hour_num(orders.created_at)ororders.created_at | hour_num()minute_num(orders.created_at)ororders.created_at | minute_num()second_num(orders.created_at)ororders.created_at | second_num()
Additional Notes
- The function returns an integer representing the specified part of the date
- Day of week use 0-based indices
- Week numbering follows the ISO 8601 standard
- Day of week numbering depends on your organization settings
Return type Number
date_diff
date_diff(datetime_part, start, end)
date_diff('day', orders.created_at, @now)
date_diff('month', orders.created_at, @now)
Description
Calculates the difference between two dates in the specified datetime_part. The supported parts are:
'day': The number of days between the two dates.'week': The number of weeks between the two dates.'month': The number of months between the two dates.'quarter': The number of quarters between the two dates.'year': The number of years between the two dates.
Return type
Number