Date and Datetime functions in the Data Pipeline

Chartio has date functions that are available to use in Custom formulas in the Add Column and Edit Column Data Pipeline steps.

Formulas

dateadd( date_string, amount, 'unit' )

Adds a time interval to a date or datetime

datesub( date_string, amount, 'unit' )

Subtracts a time interval from a date or datetime

datediff( date_string1, date_string2, 'unit' (optional) )

Calculates the difference between two dates or datetimes. Formula will default to days if no unit is specified.

datepart( date_string, 'unit' )

Returns part of a date or datetime as an integer.

Units

  • day
  • week (datediff only)
  • month
  • year
  • hour
  • minute
  • second
  • quarter (datepart only)
  • dayofyear (datepart only)

Example

I've added a new Add Column Step in the Data Pipeline to calculate the date difference between two date columns: Start Date and End Date.

We'll start by clicking the Add Step button in the Data Pipeline, and we'll choose Add Column.

Select Custom formula for the Formula type, and enter the following formula:

datediff("Start Date", "End Date")

 Compare our query results before and after the pipeline step.