Date and time functions using SQLite

Data Pipeline

Customizing date formats

As date and time can be broken down into individual components like month, day, year, hour, minute, etc., you can format your date or time values to only show the information you want within your charts.

You can use these modifiers in the Pipeline using the Custom Formula option in the Add Column or Edit Column Pipeline steps. If you want to use modifiers in your initial query against your data source, check your corresponding data source SQL syntax to make sure you’re using the correct modifier associated with that data source type.

Datetime info table

You can use the modifiers listed above in format functions using the following SQLite syntax:

strftime('format',"date/time_column_name")

For example, if I have a column named “Day of Created Date” which includes information that’s too granular for my needs, I can refine it to only include the year, month, and day in my preferred order using the formula below:

strftime('%Y-%m-%d',"Day of Created Date")

Edit Column step in the Pipeline

For the most common variations like date or datetime, you can use functions that will apply the format to your whole column such as:

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 following functions: date(), time(), datetime(), and julianday(). The units below can be used as part of the formulas.

NNN days NNN hours NNN minutes NNN.NNNN seconds NNN months NNN years

These modifiers can be used to modify your date or time. For example, in the screenshot below, we’re adding 1 month and 2 days to the “Day of Created Date column” using the following formula structure:

date("Column Name",'+interval unit','+interval unit')

Add Column step in the Pipeline

Note: The modifiers can also be negative values if you’d like to subtract values from your date or datetime columns.

For additional math functions, Chartio has the following built-in functions available 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.

datepart(date_string,'unit')

Extracts a part of the date string.

The units available to be used within the above functions are:

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


Related Data Pipeline Help Articles

See more