About date formulas
Learn about date formulas.
Date functions are useful when you want to compare data collected between two date periods. Date formulas allow you to apply date related functions to your formulas.
Date formulas
The date formulas include:
add_days
-
Returns the result of adding the specified number of days to the given date.
- Examples
-
add_days (01/30/2015, 5) = 02/04/2015 add_days (invoiced, 30)
add_minutes
-
Returns the result of adding the specified number of minutes to the given date, datetime, or time.
- Examples
-
add_minutes ( 01/30/2015 00:10:20 , 5 ) = 01/30/2015 00:15:20 add_minutes ( invoiced , 30 )
add_months
-
Returns the result of adding the specified number of months to the given date.
- Examples
-
add_months ( 01/30/2015, 5 ) = 06/30/2015 add_months ( invoiced_date , 5 )
add_seconds
-
Returns the result of adding the specified number of seconds to the given date/ datetime/ time.
- Examples
-
add_seconds ( 01/30/2015 00:00:00, 5 ) = 06/30/2015 00:00:05 add_seconds ( invoiced_date , 5 )
add_weeks
-
Returns the result of adding the specified number of weeks to the given date.
- Examples
-
add_weeks ( 01/30/2015, 2 ) = 02/13/2015 add_weeks ( invoiced_date , 2 )
add_years
-
Returns the result of adding the specified number of years to the given date.
- Examples
-
add_years ( 01/30/2015, 5 ) = 01/30/2020 add_years ( invoiced_date , 5 )
date
-
Returns the date portion of a date.
- Examples
-
date (home visit)
day
-
Returns the number (1-31) of the day of the month for a date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
day (01/15/2014) = 15 day (date ordered)
In the following example, the 15th of the month is the start of the fiscal month.
day (01/17/2019, fiscal) = 3
day_number_of_quarter
-
Returns the number of the day in a quarter for a date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
day_number_of_quarter (01/30/2015) = 30
In the following example, May 1 is the first day of the fiscal year.
day_number_of_quarter (01/30/2015, fiscal) = 91
day_number_of_week
-
Returns the number (1-7) of the day in a week for a given date. Monday is 1, and Sunday is 7. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
day_number_of_week(01/15/2014) = 3 day_number_of_week (shipped)
In the following example, Wednesday is the start of the fiscal week.
day_number_of_week(04/28/2022, fiscal) = 2
day_number_of_year
-
Returns the number (1-366) of the day in a year from a date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
day_number_of_year (01/30/2015) = 30 day_number_of_year (invoiced)
In the following example, May 1 is the first day of the fiscal year.
day_number_of_year ( 01/30/2015, fiscal ) = 275
day_of_week
-
Returns the day of the week for the given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
day_of week (01/30/2015) = Friday day_of_week (serviced)
In the following example, the days of the week are in French in the fiscal calendar.
day_of_week(04/28/2022, fiscal) = jeudi
diff_days
-
Subtracts the second date from the first date and returns the result in number of days.
- Examples
-
diff_days (01/15/2014, 01/17/2014) = -2 diff_days (01/15/2014 01:00:00, 01/14/2014 23:00:00) = 1 diff_days (purchased, shipped)
diff_hours
-
Subtracts the hour of the second date from the hour of the first date and returns the result in number of hours.
- Examples
-
diff_hours (01/15/2014 01:59:59, 01/15/2014 02:00:00) = -1 diff_hours (01/15/2014 01:00:00, 01/15/2014 01:59:59) = 0 diff_hours (clicked, submitted)
diff_minutes
-
Subtracts the minute of the second date from the minute of the first date and returns the result in number of minutes.
- Examples
-
diff_minutes (01/15/2014 01:59:59, 01/15/2014 02:00:00) = -1 diff_minutes (01/15/2014 01:00:00, 01/15/2014 01:00:59) = 0 diff_minutes (clicked, submitted)
diff_months
-
Subtracts the month of the second date from the month of the first date and returns the result in number of months.
The optional third parameter specifies the custom calendar the formula uses to calculate the result.
- Examples
-
diff_months (12/25/2013, 01/01/2014) = -1 diff_months (01/01/2014, 01/25/2014) = 0 diff_months (purchased, shipped) diff_months (purchased, shipped, fiscal)
diff_quarters
-
Subtracts the quarter of the second date from the quarter of the first date and returns the result in number of quarters.
The optional third parameter specifies the custom calendar the formula uses to calculate the result.
- Examples
-
diff_quarters (12/31/2013, 01/01/2014) = -1 diff_quarters (01/01/2014, 03/31/2014) = 0 diff_quarters (purchased, shipped) diff_quarters (purchased, shipped, fiscal)
diff_time
-
Subtracts the second date from the first date and returns the result in number of seconds.
- Examples
-
diff_time (01/30/2014, 01/31/2014) = -86,400 diff_time (clicked, submitted)
diff_weeks
-
Subtracts the week of the second date from the week of the first date and returns the result in number of weeks.
The optional third parameter specifies the custom calendar the formula uses to calculate the result.
- Examples
-
diff_weeks (01/05/2014, 01/06/2014) = -1 diff_weeks (01/06/2014, 01/12/2014) = 0 diff_weeks (purchased, shipped) diff_weeks (purchased, shipped, fiscal)
diff_years
-
Subtracts the year of the second date from the year of the first date and returns the result in number of years.
The optional third parameter specifies the custom calendar the formula uses to calculate the result.
- Examples
-
diff_years (12/25/2013, 01/01/2014) = -1 diff_years (01/01/2014, 12/25/2014) = 0 diff_years (purchased, shipped) diff_years (purchased, shipped, fiscal)
hour_of_day
-
Returns the hour of the day for the given date.
- Examples
-
hour_of_day (received)
is_weekend
-
Returns true if the given date falls on a Saturday or Sunday. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
is_weekend (01/31/2015) = true is_weekend (emailed)
In the following example, Wednesday and Thursday are the days of the weekend in the fiscal week.
is_weekend (04/28/2022, fiscal) = true
month
-
Returns the month from the given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
month (01/15/2014) = January month (date ordered)
In the following example, the months of the year are in Spanish in the fiscal calendar.
month ( 08/20/2014, fiscal ) = agosto
month_number
-
Returns the number (1-12) of the month from a given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
month_number (09/20/2014) = 9 month_number (purchased)
In the following example, May 1 is the first day of the fiscal year.
month_number ( 09/20/2014, fiscal ) = 5
month_number_of_quarter
-
Returns the month (1-3) number for the given date in a quarter. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
month_number_of_quarter (02/20/2018) = 2
In the following example, May 1st is the start of the fiscal year.
month_number_of_quarter (02/20/2018, fiscal ) = 1
now
-
Returns the current date and time in your locale’s standard date and time format. For example, if your locale is English (United States), it returns
MM/dd/yyyy hh:mm:ss
(04/27/2022 12:34:00).- Examples
-
now ()
quarter_number
-
Returns the number (1-4) of the quarter associated with the date. You can add an optional second parameter to specify fiscal or calendar dates.
- Default
-
calendar
- Examples
-
quarter_number ( 04/14/2014) = 2 quarter_number ( shipped )
In the following example, May 1 is the first day of the fiscal year.
quarter_number ( 04/14/2014, fiscal ) = 4
start_of_month
-
Returns
MMM yyyy
for the first day of the month. Your installation configuration can override this setting so that it returns a different format such asMM/dd/yyyy
. Speak with your ThoughtSpot administrator for information on doing this. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.- Default
-
calendar
- Examples
-
start_of_month ( 01/31/2015 ) = Jan FY 2015 start_of_month (shipped)
In the following example, the 15th is the start of the fiscal month.
start_of_month ( 01/14/2022, fiscal) = Dec 2021
start_of_quarter
-
Returns the date for the first day of the quarter for the date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
start_of_quarter ( 04/30/2014) = Apr 2014 start_of_quarter (sold)
In the following example, May 1 is the first day of the fiscal year.
start_of_quarter ( 04/30/2014, fiscal) = Feb 2014
start_of_week
-
Returns the date for the first day of the week for the given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
start_of_week ( 01/31/2020 ) = 01/27/2020 start_of_week (emailed)
In the following example, Wednesday is the start of the fiscal week.
start_of_week ( 04/28/2022, fiscal) = 04/27/2022
start_of_year
-
Returns the date for the first day of the year for the date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
start_of_year (04/30/2014) returns Jan 2014 start_of_year (joined)
In the following example, May 1 is the first day of the fiscal year.
start_of_year (04/30/2014, fiscal)
time
-
Returns the time portion of a date.
- Examples
-
time (1/31/2002 10:32) = 10:32 time (call began)
today
-
Returns the current date in your locale’s standard date format. For example, if your locale is English (United States), it returns
MM/dd/yyyy
(04/27/2022).- Examples
-
today ()
week_number_of_month
-
Returns the week number for the given date in a month. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
week_number_of_month(03/23/2017) = 3
In the following example, the 15th is the start of the fiscal month.
week_number_of_month (05/31/2020, fiscal) = 3
week_number_of_quarter
-
Returns the week number for the given date in a quarter. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
week_number_of_quarter (01/31/2020) = 5
In the following example, May 1 is the first day of the fiscal year.
week_number_of_quarter (05/31/2020, fiscal) = 5
week_number_of_year
-
Returns the week number for the date in a year. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
week_number_of_year (01/17/2014) = 3
In the following example, May 1 is the first day of the fiscal year.
week_number_of_year ( 01/17/2014, fiscal) = 38
year
-
Returns the year in integer format for a given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
year (01/15/2014) = 2014 year (date ordered)
In the following example, May 1 is the first day of the fiscal year.
year (12/15/2013, fiscal ) = 2014
year_name
-
Returns the year in string format for a given date. You can add an optional second parameter to specify whether a fiscal or calendar year is used to calculate the result.
- Default
-
calendar
- Examples
-
year (01/15/2014) = "2014" year (date ordered)
In the following example, May 1 is the first day of the fiscal year.
year (12/15/2013, fiscal ) = "FY_2014"
Calculate date formulas
Calculating date formulas is useful when you want to compare data from different date periods. Here are some examples of using date formulas:
Example 1
The following example shows you how to create formulas that you can use to compare data from this week to last week.
-
The formula for this week is:
week ( today () ) - week (date)
-
The formula for last week is:
diff_days ( week ( today () ) ) , week ( date ) )
Example 2
The following example shows you how to calculate the percent increase from the last date period to this period in terms of revenue.
-
Create the formula:
this week revenue = sum ( if ( this week ) then revenue else 0 )
-
Then create the formula:
last week revenue = sum ( if (last week ) then revenue else 0 )
-
Use nested formulas to calculate the percent increase by creating a parent formula:
percent increase = ( ( this week revenue - last week revenue) / last week revenue ) * 100
Fiscal and Gregorian calendars
For the following date formulas, you can further specify either fiscal
, Gregorian calendar
, or any other custom calendar on which to base date calculations.
(If you do not specify a calendar type, the formula defaults to the default cluster calendar.)
-
day_number_of_quarter
-
day_number_of_year
-
month_number
-
month_number_of_quarter
-
quarter_number
-
start_of_quarter
-
start_of_year
-
week_number_of_quarter
-
week_number_of_year
-
year
Your ThoughtSpot administrator and ThoughtSpot Support can set up a fiscal calendar year to start on any month.
If the fiscal
year is not explicitly configured in the system, fiscal
defaults to January, the same as the Gregorian calendar
.
For example, the formula month_number_of_quarter (05/31/2014)
would return 2
based on the default Gregorian calendar, whereas the formula month_number_of_quarter (05/31/2014, 'fiscal')
would return 1
if your administrator has configured the fiscal calendar to start in May.
Related information
Show content