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

Examples

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

tip

All date part has a corresponding short-hand truncate function. Examples below will use the short-hand version.

• day(orders.created_at) or orders.created_at | day()
• month(orders.created_at) or orders.created_at | month()
• year(orders.created_at) or orders.created_at | year()
• quarter(orders.created_at) or orders.created_at | quarter()
• week(orders.created_at) or orders.created_at | week()
• hour(orders.created_at) or orders.created_at | hour()
• minute(orders.created_at) or orders.created_at | minute()

Return type

date or datetime depending on the input type.

date_diff

date_diff(datetime_part, start, end)

Examples

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