Datetime Functions

Functions

You can perform functions on datetime controls and variables, which 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:

SELECT {CURRENT_CALENDAR_YEAR.START.SUB(1,'day')}

Units

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:

{DATE_VARIABLE.ADD(integer,'units')}
{DATE_VARIABLE.SUB(integer,'units')}

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

{CURRENT_MONTH.START.SUB(1,'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:

{CURRENT_MONTH.START.SUB(1,'day')}

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()

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:

{DATE_VARIABLE.PART('units')}

PART() examples

Return the quarter number of the current day:

{TODAY.PART('quarter')}

Return the last day of the current month:

{CURRENT_MONTH.END.PART('day')}

Return the current year in number form:

{TODAY.PART('year')}

BUCKET()

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

{DATE_VARIABLE.BUCKET('units')}

BUCKET() examples

Return the month and year of the previous month:

{CURRENT_MONTH.START.SUB(1,'month').BUCKET('month')}

Return the ISO week date of the current day:

{TODAY.BUCKET('week')}

Return the quarter date of the current day:

{TODAY.BUCKET('quarter')}