Relative Date Variables

Chartio offers built-in date variables that can be used across the application. They each resolve to a single date string, so you can use them anywhere you would otherwise enter a date value. For example, you can:

  • set default dates for dashboard date filters, such as a Calendar filter or a Date Slider
  • use in custom formulas in the Data Pipeline
  • use in SQL Mode
  • use in Interactive Mode filters

Relative date variables follow the Time Zone your dashboard uses.

View examples

Available Variables

  • {TODAY}


You can perform functions on date variables that will allow you to customize the date returned to fit your needs.

Multiple functions can be chained together; see examples below.

To try these out in Chartio, open the Explore tab in SQL Mode and add SELECT before your date formula. For example:



Units are used to perform calculations on dates (add or subtract) and extract part of a date (for example, return only the year part of a date). They can be used in either singular or plural form and include:

  • day
  • week
  • month
  • year
  • quarter (PART() only)

Add and subtract

Add/subtract from relative date variables to create other dates. Use the following format:


For example, to get the first day of the previous month:


Add and subtract functions can be combined to customize dates. View examples here.

Calculating the last day of the previous month

Let’s say it’s currently June. {CURRENT_MONTH.END.SUB(1, 'month')} will return 2016-05-30, because we’re subtracting one month from 2016-06-30.

To get the last day of the previous month, use the following instead:


Extract part of a date

We have two functions for extracting part of a date: PART() and BUCKET(). PART() returns a number, and BUCKET() returns a date string.


PART() allows you to extract part of a date and returns it as a number that can be used in calculations. Use the following format:


PART() examples

Return the quarter number of the current day:


Return the last day of the current month:


Return the current year in number form:



BUCKET() allows you to extract part of a date and returns it as a date string. Use the following format:


BUCKET() examples

Return the month and year of the previous month:


Return the ISO week date of the current day:


Return the quarter date of the current day: