Date and time functions using SQLite

Data Pipeline

Customizing date formats

Because date and time can be broken down into individual components like month, day, year, hour, minute, etc., you can format your date or time to show what you want.

You can use these modifiers in the Pipeline. If you want to use modifiers in your query against your datasource, check your corresponding data source SQL syntax to make sure you’re using the correct modifier associated with that data source.

Datetime info table

You can use these modifiers in format functions. For SQLite, that is:

STRFTIME(format, date/time_column)

For example,

Edit Column step in the Pipeline

For the most common variations like date or datetime, you can use functions that will apply the format:

strftime equivalents

Other things to do with dates and times

You can modify dates and times and do computations with them. Modifiers can be appended to any of the functions date(), time(), datetime (), and julianday()

NNN days

NNN hours

NNN minutes

NNN.NNNN seconds

NNN months

NNN years

These modifiers can be used to modify your date or time.

Add Column step in the Pipeline

For additional math functions, Chartio has built in functions in the Pipeline using SQLite.

  • dateadd( date_string, amount, ‘unit’ )
    • Adds a time interval to a date or datetime.
  • datesub( date_string, amount, ‘unit’ )
    • Substracts a time interval from a date or datetime.
  • datediff( date_string1, date_string2, ‘unit’ (optional) )
    • Calculates the difference between two dates or datetimes. The formula will default to days if no unit is specified.

Units: day, week (datediff only), month, year, hour, minute, second.

For more information on date and datetime functions in the Pipeline, see Date and Datetime functions in the Data Pipeline.

Related Data Pipeline Help Articles

See more