Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root


1 badge earned


Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!


Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.


Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!


Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
Community Members
Community Events
Community Groups

Get the created date and last update date of the projects


Hello Team,

How to get the list projects with the project created date and when is the project issue last updated.

Database: Postgres


Thanks in Advance.

2 answers

Hey @Raju Anumula and @Eyal Gottlieb

I know it's been a long time, but I'll leave this query here for anyone that needs it.

This works in posgres and Jira 8.8+

** This one will return:

  • project name
  • project key
  • project lead
  • if that lead's account is active or inactive
  • last issue update date
  • last issue create date
  • total number of issues
  • date project was created
with rank_created as (
	select project,created,rank() over(partition by I.project order by I.created desc) as ranking
	from jiraissue I 
rank_updated as (
	select project,updated,rank() over(partition by I.project order by I.updated desc) as ranking
	from jiraissue I 
count_issues as (
	select project, count(*) as total 
	from jiraissue j group by project
users as (
	select user_name, lower_user_name, email_address, ,rank() over(partition by U.user_name order by D.directory_position) as ranking
	from cwd_user U
	join cwd_directory D on U.directory_id = and = 1
select P.pname, P.pkey, U.user_name, U.email_address,, RC.created, RU.updated,, to_timestamp(cast(A."ENTITY_TIMESTAMP" as bigint)/1000) as "Project created"
from project P
join "AO_C77861_AUDIT_ENTITY" A on "ACTION" = 'Project created' and A."PRIMARY_RESOURCE_ID" = cast( as varchar)
join app_user AU on AU.user_key = P."lead" 
join users U on U.lower_user_name = AU.lower_user_name and U.ranking = 1
left join rank_created RC on RC.project = and RC.ranking = 1
left join rank_updated RU on RU.project = and RU.ranking = 1
left join count_issues CI on CI.project =;



SELECT DISTINCT p.pkey,p.LEAD,MAX(i.UPDATED) as "Last Updated" FROM jiraissue i INNER JOIN project p ON p.ID = i.PROJECT GROUP BY p.pkey,p.LEAD ORDER BY MAX(i.UPDATED) ASC;

** In my case it's not returning the full list (274 project out of 359). I'm getting the actual number with this simple query:


I hope that helps


Hi @Ignacio A 

I also came across this task to pull Jira projects last update etc, but for some reason I get "SQL Error [42P01]: ERROR: relation "jiraissue" does not exist"

Its Jira 8.2 and postgresql. any thoughts?

Thank you


Please ignore me :) I was running the query on a wrong database

0 votes
Stephen Wright _Elabor8_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
Oct 03, 2019

Hi @Raju Anumula

One option for project created is to use the Audit Log, assuming it's activated - to access:

  • Go to Jira Admin (cog in top-right corner) and press System
  • Under System Support, select Audit Log

You can view when one project was created using this - or export the data (button in upper-right) and review it for more than one project.

For when it last had an issue updated, I would just export all the issues for the selected projects from the issue search and locate when the last issue update was performed for each.

Alternatively you might be able to query the database your instance sits on for this data - depends on your setup.


Hello @Stephen Wright _Elabor8_ ,


we have 700+ projects in our Jira instance, difficult to get details from the audit log.


I am looking for a database query to get the project list and last update in project.

we are using Postgres.


Thanks in advance.



Did you get a query for that?

Suggest an answer

Log in or Sign up to answer