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.
Atlassian Analytics is in open beta
Only customers with a Cloud Enterprise plan for Jira Software or Jira Service Management have access to Atlassian Analytics.
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:
Day of the month
It returns a number from 01-31 (up to 31 days).
For example, it could return a value like 11.111 seconds.
It returns a number between and including 00-24 (24 hours).
Day of the year
It returns a number between and including 01-366 (366 if it’s a leap year).
Julian day number
It’s a continuous count of days since the beginning of the Julian period.
It returns a number between and including 01-12, where 01 is January and 12 is December.
It returns a number between and including 00-59 (60 mintues)
It converts the datetime value to seconds since 1970-01-01.
It returns a number between and including 00-59 (60 seconds).
Day of the week
It returns a number between and including 0-6, where 0 is Sunday.
Week of the year
It returns a number between and including 00-53 (ISO weeks).
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:
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?