How to find the created and updated date for all repositories in Bitbucket Data Center
Important Note on DB Queries
This Database Query / Functionality is not officially supported!
- Atlassian is not responsible for translating queries for different databases.
- This query should only be used as an example. Any desired adjustments should be checked with your local Database Administrator, including queries related to different DBMS that are not present/listed in this KB article, like Oracle Database.
- Atlassian will not be providing support for changes, adjustments, or inquiries regarding the below queries, as this is not part of our Atlassian Support Offerings.
- Atlassian does not provide any support to sample database queries provided in our Knowledge Base documents. Extraction of data from Bitbucket should be performed using our REST API Documentation because we do not keep all data relationships in the DB. Many connections to data are handled in the code. The REST API can go through the code paths to get the data. A SQL would have to do that manually or, in some cases, it can’t be done at all.
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Purpose
This knowledge base article covers how to find the created and last updated date of recent activity for all repositories within the Bitbucket Data Center.
Environment
The KB is applicable to Bitbucket 7.13 and above where we introduced Advanced Repository Management which lists the last modification date of each repository
Solution
Solution 1: From the UI
Since Bitbucket 7.13 we introduce Advanced Repository Management which lists the last modification date of each repository and can be filtered for specific date ranges.
Solution 2: Database query
Note
The database schema can change at any time without prior communication. Please make sure you take this into account prior to adopting this method.
The below query is provided on a best-effort basis, and Atlassian Support is unable to directly support this query or any customizations made to it to achieve your team's business needs.
Bitbucket will store an activity entry in the database whenever a repository or pull request is modified. The following SQL will provide a sorted list of Project, Repository, and Last updated date. This query is for PostgreSQL and may need to be modified for alternate database engines.
SELECT p.name as project_name,repo.id as repo_id, repo.name as repository, MAX(a.created_timestamp) AS lastupdated_date
FROM sta_repo_activity r
JOIN sta_activity a ON r.activity_id = a.id
JOIN repository repo ON r.repository_id = repo.id
JOIN project p on p.id = repo.project_id
GROUP BY p.name,repo.name,repo.id
ORDER BY lastupdated_date ASC;The below query will provide the created and updated date of all repositories in the Bitbucket instance:
SELECT p.name as project_name, repo.id as repo_id,repo.name as repository, MIN(a.created_timestamp) AS created_date,
MAX(a.created_timestamp) AS lastupdated_date
FROM sta_repo_activity r
full JOIN sta_activity a ON r.activity_id = a.id
full JOIN repository repo ON r.repository_id = repo.id
full JOIN project p on p.id = repo.project_id
where repo.name IS NOT NULL
GROUP BY p.name,repo.name, repo.id
ORDER BY lastupdated_date desc;