• Products
  • Documentation
  • Resources

Atlassian Analytics is in an early access program

Only people who are part of this invite-only early access program have access to Atlassian Analytics.

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.

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:

%d

Day of the month

It returns a number from 01-31 (up to 31 days).

%f

Fractional seconds

For example, it could return a value like 11.111 seconds.

%H

Hour

It returns a number between and including 00-24 (24 hours).

%j

Day of the year

It returns a number between and including 01-366 (366 if it’s a leap year).

%J

Julian day number

It’s a continuous count of days since the beginning of the Julian period.

%m

Month

It returns a number between and including 01-12, where 01 is January and 12 is December.

%M

Minute

It returns a number between and including 00-59 (60 mintues)

%s

Seconds

It converts the datetime value to seconds since 1970-01-01.

%S

Seconds

It returns a number between and including 00-59 (60 seconds).

%w

Day of the week

It returns a number between and including 0-6, where 0 is Sunday.

%W

Week of the year

It returns a number between and including 00-53 (ISO weeks).

%Y

Year

It returns a number between and including 0000-9999.

STRFTIME() to format datetime values

You can use the previously mentioned modifiers 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

Additional Help