SQLite date and time functions

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.

Substitutions for formatting

Use substitutions to create format strings, which you’ll include in formatting functions in custom formulas. Learn more about custom formulas.

Some of the most common substitutions include:

STRFTIME() to format datetime values

You can use the previously mentioned substitutions to format datetimes using STRFTIME() SQLite function. The function follows this format:

Replace format with the format string of substitutions and date/time column name with the name of the datetime column that you want to format.

For example, if you have a column named Second of Created At that includes information too granular for your needs (for example, it contains time information like hours, minutes, seconds), you can refine it to only include the year, month, and day in your preferred order using the formula below:

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

• The equivalent format for the DATE() function is STRFTIME(‘%Y-%m-%d’,…).

• The equivalent format for the TIME() function is STRFTIME(‘%H:%M:%S’,…).

• The equivalent format for the DATETIME() function is STRFTIME(‘%Y-%m-%d’,…).

• The equivalent format for the JULIANDAY() function is STRFTIME(‘%J’,…).

Modifiers for date and time functions

Use modifiers on dates and times and do computations with them. Use them in any of the following functions: DATE(), TIME(), DATETIME(), and JULIANDAY(). You can also add or subtract whole values of the following units as part of those functions: days, hours, minutes, seconds, months, years.

For example, use the following syntax for the DATE() function:

Replace modifier with either +interval unit or -interval unit to add or subtract that interval of units from the datetime, respectively—for example, +1 month. The modifiers are optional, and you can use multiple of them in the function.

In this example, we add one month and two days to date values in a column named Day of Created At:

Built-in date and time functions

Custom formulas have built-in functions that you can also use to modify dates and times. Some of those include:

DATEADD(date_string, amount, 'unit')

Adds a time interval to a date or datetme

DATESUB(date_string, amount, 'unit')

Subtracts a time interval from a date or datetime

DATEDIFF(date_string1, date_string2, 'unit')

Calculates the difference in the specified units between two dates or datetimes

DATEPART(date_string, 'unit')

Extracts a part of the date string

Use any of the following units in the above built-in functions:

• day

• week (DATEDIFF() only)

• month

• year

• hour

• minute

• second