Datetime functions
You can use the following datetime functions in calculated columns to extract, modify, and calculate time-based values within Insights dashboards.
ALIASES
You can search for a function using its alias.
For example, if the function date_diff has the alias datediff:
- When you type
datedi, Insights suggests thedate_difffunction. - When you type
datediff, Insights rewrites it asdate_diff.
NULL OPERANDS
Insights uses the following logic for expressions that include NULL values:
- If the expression contains a
NULLvalue, the result returned in the calculated column is also aNULLvalue.
For example, if one of the rows in the Start date column is NULL, then the result in the calculated column for that row is also NULL:
= date_diff("day",Start date, current_date())
current_date
Returns the current date.
- Return type:
DATE
current_date()Example
This example obtains the current date to determine the time elapsed from a project start date:
= date_diff("day",Start date, current_date())
You can use this function to visualize the duration of ongoing projects.
current_time
Returns the current timestamp.
- Return type:
TIMESTAMP - Aliases:
now(),current_timestamp()
current_time()Example
This example obtains the current timestamp to determine the time that an item has remained in cart:
= date_diff("hour",Cart timestamp, current_time())
You can use this function to visualize how long an item has remained in a shopping cart.
date_add
Add a date or time interval to a date. Also works with timestamps.
- Return type:
DATE,TIMESTAMP - Aliases:
dateadd,timeadd,timestampadd
date_add(interval_unit, interval_value, timestamp)Parameters
| Parameter | Type | Description |
|---|---|---|
interval_unit | VARCHAR | The unit of the amount to add. Allowed values include "second", "minute", "hour", "day", "month", and "year". |
interval_value | BIGINT | The quantity of the time to add. Use negative values to perform subtraction. |
timestamp | DATE, TIMESTAMP | The date or timestamp to add to. |
Example
This example adds contract duration to the start date to determine the contract end date:
= date_add("day", 45,Contract start date)
You can use this function to visualize contract end dates.
HANDLING END-OF-MONTH DATES
If the target month has fewer days than the original date, the result is the last day of the target month.
- For example,
date_add("month", 1, "2024-01-31")returns2024-02-29.
- For example,
If the original date is the last day of the month and the target month has more days, the result remains the last day of the target month.
- For example,
date_add("month", 1, "2024-02-29")returns2024-03-31.
- For example,
date_diff
Returns the difference between two timestamps expressed in the specified unit.
- Return type:
BIGINT - Aliases:
datediff,timediff,timestampdiff,date_subtract
date_diff(interval_unit, start, end)Parameters
| Parameter | Type | Description |
|---|---|---|
interval_unit | VARCHAR | The unit to use for the difference. Allowed values include "second", "minute", "hour", "day", "month", and "year". |
start | DATE, TIMESTAMP | The starting date or timestamp. |
end | DATE, TIMESTAMP | The ending date or timestamp. |
Example
This example finds the difference between start date and end date:
= date_diff("day",Start date,End date)
You can use this function to visualize project durations.
date_trunc
Truncates a timestamp to the specified unit.
- Return type:
DATE,TIMESTAMP - Alias:
trunc
date_trunc(interval_unit, timestamp)Parameters
| Parameter | Type | Description |
|---|---|---|
interval_unit | VARCHAR | The smallest date part to preserve. Allowed values include "second", "minute", "hour", "day", "month", "quarter", and "year". |
timestamp | DATE, TIMESTAMP | The date or timestamp to truncate. |
Example
This example truncates the order date to the month level:
= date_trunc("month",Order date)
You can use this function to visualize monthly order volume for different years.
date_part
Extracts the specified date part from a timestamp.
- Return type:
BIGINT - Alias:
extract
date_part(interval_unit, timestamp)Parameters
| Parameter | Type | Description |
|---|---|---|
interval_unit | VARCHAR | The date part to extract. Allowed values include "second", "minute", "hour", "day", "week", "month", "year", and "day_of_week".If you provide "week", the ISO week number (1–53) of the date is returned. A year can have 52 or 53 weeks. Each week starts on Monday and ends on Sunday.If you provide "day_of_week", Monday = 1 and Sunday = 7. |
timestamp | DATE, TIMESTAMP | The date or timestamp to extract from. |
Example
This example extracts the year from transaction timestamps:
= date_part("year",Transaction date)
You can use this function to calculate the total revenue for each year.
day_of_week
Returns the ISO day of the week where the value ranges from 1 (Monday) to 7 (Sunday).
- Return type:
BIGINT - Alias:
dayofweek
day_of_week(timestamp)Parameters
| Parameter | Type | Description |
|---|---|---|
timestamp | DATE, TIMESTAMP | The input date or timestamp. |
Example
This example identifies the day of the week when an order was placed:
= day_of_week(Order timestamp)
You can use this function to identify the day of the week with the highest order volume.
Related to
name_of_day
Returns the three-letter name of the weekday from the timestamp.
- Return type:
VARCHAR - Alias:
dayname
name_of_day(timestamp)Parameters
| Parameter | Type | Description |
|---|---|---|
timestamp | DATE, TIMESTAMP | The input date or timestamp. |
Example
This example identifies the day of the week when an order was placed:
= name_of_day(Order timestamp)
You can use this function to identify the day of the week with the highest order volume.
name_of_month
Returns the three-letter name of the month from the timestamp.
- Return type:
VARCHAR - Alias:
monthname
name_of_month(timestamp)Parameters
| Parameter | Type | Description |
|---|---|---|
timestamp | DATE, TIMESTAMP | The input date or timestamp. |
Example
This example identifies the month when an order was placed:
= name_of_month(Order timestamp)
You can use this function to identify months with highest order volumes.
hour
Extracts the hour from the timestamp.
- Return type:
BIGINT
hour(timestamp)Parameters
| Parameter | Type | Description |
|---|---|---|
timestamp | TIMESTAMP | The input timestamp. |
Example
This example extracts the hour from chat timestamps:
= hour(Session timestamp)
You can use this function to track peak customer support chat hours.
Related to
day
Extracts the day of the month from the timestamp.
- Return type:
BIGINT - Alias:
dayofmonth,day_of_month
day(timestamp)Parameters
| Parameter | Type | Description |
|---|---|---|
timestamp | DATE, TIMESTAMP | The input date or timestamp. |
Example
This example extracts the day from the visit date:
= day(Visit date)
You can use this function to identify days in a month with higher traffic.
Related to
week
Extracts the ISO week of the year from the timestamp.
- Return type:
BIGINT
week(timestamp)ISO WEEK NUMBER
The ISO week number of the date ranges from 1–53. A year can have 52 or 53 weeks. Each week starts on Monday and ends on Sunday.
Parameters
| Parameter | Type | Description |
|---|---|---|
timestamp | DATE, TIMESTAMP | The input date or timestamp. |
Example
This example extracts the week from timesheet submissions:
= week(Work date)
You can use this function to track total work hours per week.
Related to
month
Extracts the month from the timestamp.
- Return type:
BIGINT
month(timestamp)Parameters
| Parameter | Type | Description |
|---|---|---|
timestamp | DATE, TIMESTAMP | The input date or timestamp. |
Example
This example extracts the month from transaction timestamps:
= month(Transaction timestamp)
You can use this function to calculate the total revenue for each month.
Related to
year
Extracts the year from the timestamp.
- Return type:
BIGINT
year(timestamp)Parameters
| Parameter | Type | Description |
|---|---|---|
timestamp | DATE, TIMESTAMP | The input date or timestamp. |
Example
This example extracts the year of the enrollment date:
= year(Enrollment date)
You can use this function to analyze yearly growth in students for the course.
Related to
add_months
Adds or subtracts a specified number of months to the timestamp.
- Return type:
DATE,TIMESTAMP
add_months(timestamp, num_months)Parameters
| Parameter | Type | Description |
|---|---|---|
timestamp | DATE, TIMESTAMP | The date or timestamp to add to. |
num_months | INTEGER | The number of months to add. |
Example
This example obtains the date six months after a contract start date:
= add_months(Start date, 6)
You can use this function to determine and visualize contract end dates.
HANDLING END-OF-MONTH DATES
If the target month has fewer days than the original date, the result is the last day of the target month.
- For example,
add_month(1, "2024-01-31")returns2024-02-29.
- For example,
If the original date is the last day of the month and the target month has more days, the result remains the last day of the target month.
- For example,
add_month(1, "2024-02-29")returns2024-03-31.
- For example,
Related to
Last updated: