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

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

SQL: Last closed month

Hi,

we have got a direct connection to the database (SQL). At the start of every month we close the last month for time logging. For our report I need the information if the last month is closed or not. Where do I get the information? In which table is the

 

Kind regards

Robin

 

 

3 answers

Hi @Robin ,

In case you are using Tempo Timesheets to close periods, the information is stored in the  AO_86ED1B_PERIOD table. 
It is possible to retrieve the status of a Period via a (private) REST API, f.x. /rest/tempo-timesheets/3/period?key=1220 

A list of all DB tables that are provided by Add-ons can be seen in JIRA Administration, System, Plugin Data Storage (dependent on your JIRA version, there might be a problem with the display of this information, https://jira.atlassian.com/browse/JRASERVER-71733).

Best regards,
Susanne Götz
Tempo team

Thank you,

 

yes that is the table I was looking for. Some further question:

 

1. The key is build lieke MMYY?

2. And if I find an entry the month is closed?

 

Kind regards

Robin

Hi @Robin ,

The key is in the format MMYY (as it is listed in "Period Number" in Tempo Settings,  Period Management)

When you find an entry, it means that the period has been edited at some time, but not necessarily that it is closed. 

It might also be open for approvers and closed for team members. 
Adding here an example how the response can look like:
{
"id": 2,
"key": "0120",
"startDate": "2020-12-01",
"endDate": "2020-12-31",
"openForApprover": true,
"openForTeamMember": false,
"periodConfiguration": {
"id": 1,
"startDay": 1,
"startMonth": 1,
"defaultPeriod": true
}
}

Thanks a lot.

 

This is the information I need. 

0 votes

SQL  is a terribe way to try to report on Jira data, the database is almost as far away as it can be set up for reporting and most of the queries you'll want to make are torturous and slow, as well as, in some cases, simply not there.  Some of the stuff you see on screen is not in the database at all.

Ignoring that and assuming you're happy with painful SQL, I'm afraid you have not given us enough detail to be able to help you find the tables you might need.

How are you "closing" a month?  I would guess you are using Tempo timesheets - can you confirm that?  Or tell us if you are using another app for timesheets, or explain what "close" means in plain Jira?

Thank you @Susanne Götz  for saving me have to look up the tables, and explaining it better than I could!

Hi,

thank you very much for the fast answer. Yes SQl is a terible way ;). But we have got several databases and need to combine the data.

Yes I can confirm, we are using the "Tempo" timesheet. Close means: After closing the month no one is able to log time in the "Tempo" sheet.

 

Kind regards

Robin 

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you