Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

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

Date time information saved in Jira Database for transitions Edited

Hi All,

I have created an issue in Jira.Based on the date and time the Created Field is updated.This field takes system date and time.

Now I am updating the issue from one status to another and in the transitions I can see both Date and time are displayed.

I am trying to search for this issue in my Jira Database JiraIssue table and changegroup table, when I see the created column the data has only date information.It does not contains time information.

Could you please help me if this information is saved somewhere in database or direct to correct database tables or a way to retrieve this date and time information from database.

 

2 answers

0 votes

Created date and time is stored in the jiraissue table, and all changes to an issue are logged in changegroup.  Both fields used for storing when the action was taken are date/time fields and capture both date and time.

What tool are you using to read the database?  It sounds like the tool is set to ignore time stamps in date columns!

I am using Jira version 7.9 in production and 7.11 in test environment and using SQL database to retrieve the data .

Query used to fetch data is below:select row_number() over (PARTITION BY JI.ID order by CG.CREATED asc) row#,JI.ISSUENUM as ID,
CI.OLDSTRING as FromStatus, CI.NEWSTRING as ToStatus,
JI.CREATED as CREATED,CG.CREATED as EXECUTED
from jiraissue JI
join changegroup CG on CG.issueid = JI.ID
join changeitem CI on CI.groupid=CG.id and field = 'status'
LEFT JOIN nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
LEFT JOIN projectversion Y ON Y.ID = X.SINK_NODE_ID
JOIN project ON project.ID = ji.PROJECT AND project.id =
JOIN issuestatus s ON ji.issuestatus = s.ID
where JI.CREATED>'01-JAN-18' and JI.ISSUETYPE=

Attached is the screenshot from history tab that shows the data and time format for each transition change.

Also attached is the JIRAISSUE Database structure that shows that 'CREATED','UPDATED' column has data type as DATE.

Was this changed in the recent releases?

Is there some other table which I could join to my query to retrieve details related to date and time both?Jiraissue_Databasetable.pngTransition Date and time.png

Is there some update on the issue?

Thanks,

Mehak

>Was this changed in the recent releases?

No, it has not changed since Jira 1

>Is there some other table which I could join to my query to retrieve details related to date and time both?

No, because, as I said before, the data is stored in the tables you are reading, and there is no reason to duplicate it elsewhere.  The date and time you are pointing to in the change history in your screenshot is the date/time stored in the changegroup date/time field.

I do not know why your database tool is suppressing the time display.  Mine does not.  A changegroup in my Jira (was 7.6, now 8.0) looks like this:

Screenshot 2019-03-28 at 10.39.52.png

I know it's late, as the posting is from 2019, but I can provide an answer (I experienced the same issue).

Just run this command before running the SELECT query and you will see date and time information for [Created] field:

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

Regards,

M. R.

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Service Management

JSM June Challenge #2: Share how your business teams became ITSM rockstars

For JSM June Challenge #2, share how your non-technical teams like HR, legal, marketing, finance, and beyond started using Jira Service Management! Tell us: Did they ask to start using it or...

193 views 6 7
Read article

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