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 thedatetime
value.'week'
: The first day of the week in the week that contains thedatetime
value. 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 thedatetime
value.'quarter'
: The first day of the quarter in the quarter that contains thedatetime
value.'year'
: The first day of the year in the year that contains thedatetime
value.'hour'
: The hour in the day that contains thedatetime
value.'minute'
: The minute in the hour that contains thedatetime
value.
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
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