• Products
  • Documentation
  • Resources

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.

If you want to use substitutions in a query, check the documentation for the SQL syntax of that data source type to ensure you’re using the correct substitutions.

Some of the most common substitutions include:

Substitution

Part of date or time

Example output

%d

Day of the month

01, 02, …, 31 (up to 31 days)

%f

Fractional seconds

11.111

%H

Hour

Returns a number between and including 00-24 (24 hours)

%j

Day of the year

01, 02, …, 366 (366 if it’s a leap year)

%J

Julian day number

2459290.5 (March 17, 2021)

A continuous count of days since the beginning of the Julian period

%m

Month

01, 02, …, 12

01 is January and 12 is December.

%M

Minute

00, 01, …, 59 (60 minutes)

%s

Seconds

1615939200 (March 17, 2021)

Converts the datetime value to seconds since 1970-01-01

%S

Seconds

00, 01, …, 59 (60 seconds)

%w

Day of the week

0, 1, …, 6

0 is Sunday and 6 is Saturday.

%W

Week of the year

00, 01, …, 53 (ISO weeks)

%Y

Year

0000, 0001, …, 9999

STRFTIME() to format datetime values

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

1 STRFTIME('format',"date/time column name")

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:

1 STRFTIME('%Y-%m-%d',"Second of Created At")

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:

1 DATE("Column Name",'modifier','modifier',…)

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:

1 DATE("Day of Created At",'+1 months','+2 days')
Using a custom formula to add one month and two days to date values in a column

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

Still need help?

The Atlassian Community is here for you.