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.
To change how the result is displayed, see AML Date Format.
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. To change how the result is displayed, see Formatting number as date parts.
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
date_format
date_format(datetime, format)
Description
Formats a date according to the specified format string.
Return type
Text
Examples
date_format(orders.created_at, '%Y-%m-%d')
date_format(orders.created_at, '%Y-%m-%d %H:%M:%S')
date_format(orders.created_at, '%B %d, %Y')
date_format(orders.created_at, '%A, %B %d, %Y')
Format Patterns
| Pattern | Description | Example |
|---|---|---|
%Y | Four-digit year | 2018 |
%y | Two-digit year | 18 |
%q | Quarter of the year (1–4) | 3 |
%m | Two-digit month | 07 |
%B | Full month name | July |
%b | Abbreviated month name | Jul |
%A | Full day of week | Sunday |
%a | Abbreviated day of week | Sun |
%d | Two-digit day of month (01-31) | 08 |
%H | Two-digit hour based on 24-hour clock (00–23) | 00 |
%I | Two-digit hour based on 12-hour clock (01–12) | 12 |
%M | Two-digit minutes (00–59) | 34 |
%S | Two-digit seconds (00–59) | 59 |
%p | AM or PM | AM |
%L | Three-digit milliseconds (000–999) | 000 |
%f | Six-digit microseconds (000000–999999) | 000000 |
%% | The percent sign | % |
Not all format patterns are supported by all databases.
from_unixtime
from_unixtime(number)
Description
Converts a Unix timestamp (seconds since epoch) to a datetime value.
Return type
Datetime
Example
from_unixtime(1528795609)
last_day
last_day(datetime, date_part)
Description
Returns the last day of the period for a given date.
Return type
Date
Examples
last_day(orders.created_at, 'month')
last_day(orders.created_at, 'quarter')
Supported Date Parts
'month': The last day of the month'year': The last day of the year'quarter': The last day of the quarter'week': The last day of the week