Learn how to add Atlassian Analytics to a site and understand what you need to query data and create charts.
Learn how to connect and manage your connected Atlassian data and external data.
Learn how to create insightful charts and dashboards with your organization’s data.
Learn how to use Visual SQL to create queries and transform data for making charts.
Need feedback on data? Learn how to share charts and dashboards with teammates so they can leave comments.
Learn how to grant resource access to the people who need it.
Learn how to manage your workspace and how to monitor your workspace activity.
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:
Part of date or time
Day of the month
01, 02, …, 31 (up to 31 days)
Returns a number between and including 00-24 (24 hours)
Day of the year
01, 02, …, 366 (366 if it’s a leap year)
Julian day number
2459290.5 (March 17, 2021)
A continuous count of days since the beginning of the Julian period
01, 02, …, 12
01 is January and 12 is December.
00, 01, …, 59 (60 minutes)
1615939200 (March 17, 2021)
Converts the datetime value to seconds since 1970-01-01
00, 01, …, 59 (60 seconds)
Day of the week
0, 1, …, 6
0 is Sunday and 6 is Saturday.
Week of the year
00, 01, …, 53 (ISO weeks)
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:
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
Extracts a part of the date string
Use any of the following units in the above built-in functions:
week (DATEDIFF() only)
Was this helpful?