====== JSON and Jira ====== ===== SQL objects ===== * Staging table for raw JSON, assume one row per issue populated using (in part) [[https://docs.atlassian.com/DAC/rest/jira/6.1.html#d2e4071|/rest/api/2/search]] for updated issues * SQL views that use OPENJSON, JSON_QUERY, JSON_VALUE to shred JSON for Jira responses DROP TABLE IF EXISTS dbo.IssueHistory_STAGED CREATE TABLE dbo.IssueHistory_STAGED ( IssueId AS CAST(JSON_VALUE(RawJson,'$.id') AS INT) , HistoryRecordId INT NOT NULL IDENTITY , StagedDateTime DATETIME NOT NULL DEFAULT GETDATE() , SourceModifiedDateTime AS CAST(LEFT(JSON_VALUE(RawJson,'$.fields.updated'),23) AS DATETIME) , RawJson NVARCHAR(MAX) ) DROP VIEW IF EXISTS dbo.uvw_IssueHistory DROP VIEW IF EXISTS dbo.uvw_Issue DROP VIEW IF EXISTS dbo.uvw_IssueComponent DROP VIEW IF EXISTS dbo.uvw_IssueComment DROP VIEW IF EXISTS dbo.uvw_IssueLabel GO CREATE VIEW dbo.uvw_IssueHistory AS WITH hx AS ( SELECT IssueId , RawJson , StagedDateTime AS VersionValidFromDateTime , COALESCE( DATEADD( MILLISECOND , -3 , LEAD(StagedDateTime) OVER ( PARTITION BY IssueId ORDER BY StagedDateTime , HistoryRecordId ) ) , '9000-12-31' ) AS VersionValidToDateTime FROM dbo.IssueHistory_STAGED ) SELECT * FROM hx WHERE VersionValidToDateTime >= VersionValidFromDateTime GO CREATE VIEW dbo.uvw_Issue AS SELECT IssueId , JSON_VALUE(RawJson,'$.key') AS IssueCode , CAST(LEFT(JSON_VALUE(RawJson,'$.fields.created'),23) AS DATETIME) AS CreatedDateTime , JSON_VALUE(RawJson,'$.fields.project.name') AS IssueProject --, CAST(JSON_VALUE(RawJson,'$.fields.project.id') AS INT) AS IssueProjectId , JSON_VALUE(RawJson,'$.fields.issuetype.name') AS IssueType --, CAST(JSON_VALUE(RawJson,'$.fields.issuetype.id') AS INT) AS IssueTypeId , JSON_VALUE(RawJson,'$.fields.summary') AS Summary --, JSON_VALUE(RawJson,'$.fields.description') AS [Description] , JSON_VALUE(RawJson,'$.fields.status.name') AS IssueStatus --, CAST(JSON_VALUE(RawJson,'$.fields.status.id') AS INT) AS IssueStatusId , RawJson FROM dbo.uvw_IssueHistory WHERE GETDATE() BETWEEN VersionValidFromDateTime AND VersionValidToDateTime GO CREATE VIEW dbo.uvw_IssueComponent AS SELECT src.IssueId , JSON_VALUE(shred.[value], '$.name') AS ComponentName --, CAST(JSON_VALUE(shred.[value], '$.id') AS INT) AS ComponentId FROM dbo.uvw_Issue src CROSS APPLY OPENJSON(JSON_QUERY(src.RawJson,'$.fields.components')) shred GO CREATE VIEW dbo.uvw_IssueComment AS SELECT src.IssueId , JSON_VALUE(shred.[value], '$.body') AS Comment , CAST(LEFT(JSON_VALUE(shred.[value],'$.created'),23) AS DATETIME) AS CreatedDateTime , JSON_VALUE(shred.[value], '$.author.name') AS Author FROM dbo.uvw_Issue src CROSS APPLY OPENJSON(JSON_QUERY(src.RawJson,'$.fields.comment.comments')) shred GO CREATE VIEW dbo.uvw_IssueLabel AS SELECT src.IssueId , shred.[value] AS Label FROM dbo.uvw_Issue src CROSS APPLY OPENJSON(JSON_QUERY(src.RawJson,'$.fields.labels')) shred GO ===== Testing ===== * Populate staging table with three copies of issue JSON * inspect views DECLARE @json NVARCHAR(MAX) = '{ "expand":"renderedFields,names,schema,operations,editmeta,changelog,versionedRepresentations", "id":"10148", "self":"https://jira.atlassian.com/rest/api/latest/issue/10148", "key":"JRASERVER-9", "fields":{ "customfield_18232":null, "customfield_12130":null, "customfield_18230":null, "customfield_18231":null, "customfield_16732":null, "customfield_12131":null, "customfield_14430":null, "customfield_10230":null, "customfield_16730":null, "fixVersions":[ { "self":"https://jira.atlassian.com/rest/api/2/version/15918", "id":"15918", "description":"", "name":"4.4", "archived":false, "released":true, "releaseDate":"2011-08-02" } ], "resolution":{ "self":"https://jira.atlassian.com/rest/api/2/resolution/1", "id":"1", "description":"A fix for this issue is checked into the tree and tested.", "name":"Fixed" }, "customfield_12531":"Not Started", "customfield_14830":"
\r\n

Thanks for taking the time to raise a Suggestion for JIRA, Atlassian values your input.\r\n\t\t\t
\r\n\t\t\t
\r\n\t\t\tWe receive feedback from a number of different sources and evaluate those when we plan our product roadmap. If you would like to know more about how the JIRA Product Management team uses your input in our planning process, please see our post on Atlassian Answers.\r\n\t\t\t
\r\n\t\t\t
\r\n\t\t\tBefore creating a new Suggestion, please search the existing issues to see if your suggestion already exists. Otherwise, please fill in the fields below with the use case for your suggestion.\r\n\t\t\t
\r\n\t\t\t
\r\n\t\t\tKind Regards,\r\n\t\t\t
\r\n\t\t\tJIRA Product Management Team

\r\n\r\n
", "customfield_12930":null, "customfield_11436":"2809", "customfield_11435":"2814", "customfield_11437":"2622", "customfield_17130":null, "lastViewed":null, "customfield_10180":null, "customfield_17531":null, "customfield_17530":null, "customfield_17930":null, "customfield_11431":"36994", "customfield_11434":"2657", "customfield_17933":null, "labels":[ "affects-cloud", "affects-server" ], "customfield_11433":"36429", "customfield_10610":43.0, "aggregatetimeoriginalestimate":2160000, "timeestimate":2160000, "versions":[ ], "issuelinks":[ { "id":"50308", "self":"https://jira.atlassian.com/rest/api/2/issueLink/50308", "type":{ "id":"10020", "name":"Blocker", "inward":"is blocked by", "outward":"blocks", "self":"https://jira.atlassian.com/rest/api/2/issueLinkType/10020" }, "outwardIssue":{ "id":"126823", "key":"JSWSERVER-2855", "self":"https://jira.atlassian.com/rest/api/2/issue/126823", "fields":{ "summary":"GreenHopper Hourly Burndown Gadget - Update Every 15 Minutes or as Scheduled", "status":{ "self":"https://jira.atlassian.com/rest/api/2/status/6", "description":"The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.", "iconUrl":"https://jira.atlassian.com/images/icons/statuses/closed.png", "name":"Closed", "id":"6", "statusCategory":{ "self":"https://jira.atlassian.com/rest/api/2/statuscategory/3", "id":3, "key":"done", "colorName":"green", "name":"Done" } }, "issuetype":{ "self":"https://jira.atlassian.com/rest/api/2/issuetype/10000", "id":"10000", "description":"", "iconUrl":"https://jira.atlassian.com/secure/viewavatar?size=xsmall&avatarId=51505&avatarType=issuetype", "name":"Suggestion", "subtask":false, "avatarId":51505 } } } }, { "id":"37511", "self":"https://jira.atlassian.com/rest/api/2/issueLink/37511", "type":{ "id":"10050", "name":"Cause", "inward":"causes", "outward":"is caused by", "self":"https://jira.atlassian.com/rest/api/2/issueLinkType/10050" }, "inwardIssue":{ "id":"85626", "key":"JRASERVER-17359", "self":"https://jira.atlassian.com/rest/api/2/issue/85626", "fields":{ "summary":"Add timezone support to JQL dates.", "status":{ "self":"https://jira.atlassian.com/rest/api/2/status/6", "description":"The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.", "iconUrl":"https://jira.atlassian.com/images/icons/statuses/closed.png", "name":"Closed", "id":"6", "statusCategory":{ "self":"https://jira.atlassian.com/rest/api/2/statuscategory/3", "id":3, "key":"done", "colorName":"green", "name":"Done" } }, "issuetype":{ "self":"https://jira.atlassian.com/rest/api/2/issuetype/10000", "id":"10000", "description":"", "iconUrl":"https://jira.atlassian.com/secure/viewavatar?size=xsmall&avatarId=51505&avatarType=issuetype", "name":"Suggestion", "subtask":false, "avatarId":51505 } } } } ], "assignee":null, "status":{ "self":"https://jira.atlassian.com/rest/api/2/status/6", "description":"The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.", "iconUrl":"https://jira.atlassian.com/images/icons/statuses/closed.png", "name":"Closed", "id":"6", "statusCategory":{ "self":"https://jira.atlassian.com/rest/api/2/statuscategory/3", "id":3, "key":"done", "colorName":"green", "name":"Done" } }, "customfield_16030":null, "components":[ { "self":"https://jira.atlassian.com/rest/api/2/component/10125", "id":"10125", "name":"User Management - Others" } ], "customfield_14130":null, "customfield_14532":null, "customfield_10571":null, "customfield_12630":null, "customfield_10575":null, "customfield_14930":"
\r\n

JIRA feedback is collected from a number of different sources and is evaluated when planning the product roadmap. If you would like to know more about how JIRA Product Management uses customer input during the planning process, please see our post on Atlassian Answers.\r\n

\r\n\r\n
", "customfield_11930":null, "customfield_10723":null, "aggregatetimeestimate":2160000, "customfield_17230":null, "creator":{ "self":"https://jira.atlassian.com/rest/api/2/user?username=mike%40atlassian.com", "name":"mike@atlassian.com", "key":"mike@atlassian.com", "emailAddress":"mike at atlassian dot com", "avatarUrls":{ "48x48":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=48", "24x24":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=24", "16x16":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=16", "32x32":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=32" }, "displayName":"Mike Cannon-Brookes", "active":true, "timeZone":"Australia/Sydney" }, "customfield_17630":null, "customfield_15331":null, "subtasks":[ ], "customfield_10160":"26179200", "customfield_10161":"true", "customfield_13430":null, "customfield_11130":"4514", "customfield_10680":null, "reporter":{ "self":"https://jira.atlassian.com/rest/api/2/user?username=mike%40atlassian.com", "name":"mike@atlassian.com", "key":"mike@atlassian.com", "emailAddress":"mike at atlassian dot com", "avatarUrls":{ "48x48":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=48", "24x24":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=24", "16x16":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=16", "32x32":"https://avatar-cdn.atlassian.com/b1a3871fb560dc486b6636467e9802fe?d=mm&s=32" }, "displayName":"Mike Cannon-Brookes", "active":true, "timeZone":"Australia/Sydney" }, "customfield_10681":null, "customfield_17631":null, "aggregateprogress":{ "progress":1500, "total":2161500, "percent":0 }, "progress":{ "progress":1500, "total":2161500, "percent":0 }, "votes":{ "self":"https://jira.atlassian.com/rest/api/2/issue/JRASERVER-9/votes", "votes":454, "hasVoted":false }, "worklog":{ "startAt":0, "maxResults":20, "total":1, "worklogs":[ { "self":"https://jira.atlassian.com/rest/api/2/issue/10148/worklog/96804", "author":{ "self":"https://jira.atlassian.com/rest/api/2/user?username=tim%40atlassian.com", "name":"tim@atlassian.com", "key":"tim@atlassian.com", "emailAddress":"tim at atlassian dot com", "avatarUrls":{ "48x48":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=48", "24x24":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=24", "16x16":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=16", "32x32":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=32" }, "displayName":"Tim Pettersen", "active":true, "timeZone":"America/Los_Angeles" }, "updateAuthor":{ "self":"https://jira.atlassian.com/rest/api/2/user?username=tim%40atlassian.com", "name":"tim@atlassian.com", "key":"tim@atlassian.com", "emailAddress":"tim at atlassian dot com", "avatarUrls":{ "48x48":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=48", "24x24":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=24", "16x16":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=16", "32x32":"https://avatar-cdn.atlassian.com/fd7b5dd1d45d737f66ba75b12c4cafcd?d=mm&s=32" }, "displayName":"Tim Pettersen", "active":true, "timeZone":"America/Los_Angeles" }, "comment":"Time submitted by matt for review CR-2", "created":"2011-05-20T05:55:22.952+0000", "updated":"2011-05-20T05:55:22.952+0000", "started":"2011-05-20T05:55:18.528+0000", "timeSpent":"25m", "timeSpentSeconds":1500, "id":"96804", "issueId":"10148" } ] }, "customfield_18430":null, "issuetype":{ "self":"https://jira.atlassian.com/rest/api/2/issuetype/10000", "id":"10000", "description":"", "iconUrl":"https://jira.atlassian.com/secure/viewavatar?size=xsmall&avatarId=51505&avatarType=issuetype", "name":"Suggestion", "subtask":false, "avatarId":51505 }, "customfield_16530":null, "customfield_18030":null, "customfield_18031":null, "timespent":1500, "customfield_10150":[ "asaintprix(asaintprix)", "afahmi@icomm.co.id(afahmi@icomm.co.id)", "paf(paf)", "alvincura(alvincura)", "akhalilian(akhalilian)", "acarrucan@morpheum.com.au(acarrucan@morpheum.com.au)", "afeury(afeury)", "azhdanov(azhdanov)", "aphan(aphan)", "anton@atlassian.com(anton@atlassian.com)", "anton_nix(anton_nix)", "armenb(armenb)", "becket_hackett@playstation.sony.com(becket_hackett@playstation.sony.com)", "bcfletcher(bcfletcher)", "br.bill(br.bill)", "bobjohnston(bobjohnston)", "blane(blane)", "bryanfe(bryanfe)", "brollins(brollins)", "cambromley(cambromley)", "cedricdj(cedricdj)", "cburke(cburke)", "surfichris(surfichris)", "cbecker(cbecker)", "nowaker(nowaker)", "rporfirio(rporfirio)", "darbystewart(darbystewart)", "dbfraser(dbfraser)", "dtollefson(dtollefson)", "david.birch@dstintl.com(david.birch@dstintl.com)", "dallardyce(dallardyce)", "david_n_m_bond(david_n_m_bond)", "heavydawson(heavydawson)", "arsenale(arsenale)", "abunetta(abunetta)", "eirik(eirik)", "erik.d.gustavson(erik.d.gustavson)", "fherelle(fherelle)", "sanmi(sanmi)", "gchattan(gchattan)", "gbrauer1(gbrauer1)", "grant.holland@db.com(grant.holland@db.com)", "grant.mckenzie@morganstanley.com(grant.mckenzie@morganstanley.com)", "gregory.kneller(gregory.kneller)", "ian.wells@trimble.co.nz(ian.wells@trimble.co.nz)", "ivar.sonstabo@bouvet.no(ivar.sonstabo@bouvet.no)", "jkleist(jkleist)", "jan_carlin(jan_carlin)", "jbcatte(jbcatte)", "jed(jed)", "jcadow(jcadow)", "op99323(op99323)", "jschnitter(jschnitter)", "jelam(jelam)", "jgarrido(jgarrido)", "jheller(jheller)", "john.price@peopleclick.com(john.price@peopleclick.com)", "jsloat(jsloat)", "jordan@ooyala.com(jordan@ooyala.com)", "joshua maciel(joshua maciel)", "woodj(woodj)", "ksilz(ksilz)", "lagoni(lagoni)", "radicsl(radicsl)", "lmiranda(lmiranda)", "jyrinx(jyrinx)", "m27315(m27315)", "konqi(konqi)", "mmazza(mmazza)", "sfinks(sfinks)", "mmoskovitz@shoplocal.com(mmoskovitz@shoplocal.com)", "mdoar(mdoar)", "igx89(igx89)", "micah(micah)", "firewater(firewater)", "michaelst(michaelst)", "atlassian@quartalfs.com(atlassian@quartalfs.com)", "chellejohn(chellejohn)", "mike@atlassian.com(mike@atlassian.com)", "mike.murphy(mike.murphy)", "nkutub(nkutub)", "napplebaum(napplebaum)", "nick.bence@bencefamily.com(nick.bence@bencefamily.com)", "nick.menere(nick.menere)", "nikhilg(nikhilg)", "oleksii.gnatkevych(oleksii.gnatkevych)", "oliver.b.fischer(oliver.b.fischer)", "owen@atlassian.com(owen@atlassian.com)", "pkwok(pkwok)", "wolfertp(wolfertp)", "paulw@outblaze.com(paulw@outblaze.com)", "dakisan(dakisan)", "pdarrow(pdarrow)", "redawson(redawson)", "rasawyer(rasawyer)", "mromain(mromain)", "rdonasco(rdonasco)", "rkrishna(rkrishna)", "scottb@oberon-media.com(scottb@oberon-media.com)", "sebbapache(sebbapache)", "lizenko(lizenko)", "shinta(shinta)", "rmsrini(rmsrini)", "stafford@customware.net(stafford@customware.net)", "stephan.born(stephan.born)", "stuartd(stuartd)", "dehli(dehli)", "timbos(timbos)", "wjohnston(wjohnston)", "voitek(voitek)", "ykhatri(ykhatri)", "zcraven(zcraven)" ], "customfield_16930":null, "project":{ "self":"https://jira.atlassian.com/rest/api/2/project/10240", "id":"10240", "key":"JRASERVER", "name":"JIRA Server (including JIRA Core)", "avatarUrls":{ "48x48":"https://jira.atlassian.com/secure/projectavatar?pid=10240&avatarId=105190", "24x24":"https://jira.atlassian.com/secure/projectavatar?size=small&pid=10240&avatarId=105190", "16x16":"https://jira.atlassian.com/secure/projectavatar?size=xsmall&pid=10240&avatarId=105190", "32x32":"https://jira.atlassian.com/secure/projectavatar?size=medium&pid=10240&avatarId=105190" }, "projectCategory":{ "self":"https://jira.atlassian.com/rest/api/2/projectCategory/10031", "id":"10031", "description":"", "name":"Atlassian Products" } }, "customfield_16532":null, "customfield_16531":null, "customfield_12730":null, "aggregatetimespent":1500, "customfield_16933":null, "customfield_16932":null, "customfield_16535":null, "resolutiondate":"2011-06-07T15:31:26.782+0000", "workratio":0, "watches":{ "self":"https://jira.atlassian.com/rest/api/2/issue/JRASERVER-9/watchers", "watchCount":213, "isWatching":false }, "customfield_17333":null, "customfield_15430":null, "created":"2002-02-08T05:08:00.000+0000", "customfield_15830":null, "customfield_17335":null, "customfield_17334":null, "customfield_11631":null, "customfield_12833":null, "customfield_12832":null, "customfield_14735":"0|100hc0:", "updated":"2017-03-29T04:49:41.515+0000", "customfield_14330":null, "customfield_18130":null, "timeoriginalestimate":2160000, "customfield_10250":"
\r\nHelp us fix bugs faster\r\n
\r\n
To help us get the right details and fix bugs as quickly as possible, submit your bug report in our support system.\r\n
\r\n
You can still view, watch, comment on, and vote for existing bugs by\r\n \r\nsearching for them in this project.\r\n

\r\nReport a new bug Search for an existing bug\r\n
\r\n
", "description":"Add time zones to user profile. That way the dates displayed to a user are always contiguous with their local time zone, rather than the server''s time zone.", "customfield_16632":null, "customfield_12431":null, "customfield_12430":null, "customfield_16630":null, "customfield_14733":"1|hyba9z:", "customfield_10650":null, "customfield_12433":null, "customfield_10651":null, "customfield_14734":"0|100g6g:", "customfield_12432":null, "customfield_12435":null, "customfield_12831":null, "customfield_14731":"0|i09onz:", "timetracking":{ "originalEstimate":"600h", "remainingEstimate":"600h", "timeSpent":"25m", "originalEstimateSeconds":2160000, "remainingEstimateSeconds":2160000, "timeSpentSeconds":1500 }, "customfield_14732":"0|1068ls:", "customfield_12830":null, "customfield_12434":null, "customfield_10653":null, "customfield_10401":null, "attachment":[ { "self":"https://jira.atlassian.com/rest/api/2/attachment/45565", "id":"45565", "filename":"log_110314_115720______.csv", "author":{ "self":"https://jira.atlassian.com/rest/api/2/user?username=jennis", "name":"jennis", "key":"jennis", "emailAddress":"jennis at infoblox dot com", "avatarUrls":{ "48x48":"https://avatar-cdn.atlassian.com/508bfb45b6fbd9f3930a394f8e3b979b?d=mm&s=48", "24x24":"https://avatar-cdn.atlassian.com/508bfb45b6fbd9f3930a394f8e3b979b?d=mm&s=24", "16x16":"https://avatar-cdn.atlassian.com/508bfb45b6fbd9f3930a394f8e3b979b?d=mm&s=16", "32x32":"https://avatar-cdn.atlassian.com/508bfb45b6fbd9f3930a394f8e3b979b?d=mm&s=32" }, "displayName":"Jay Ennis", "active":true, "timeZone":"Etc/UTC" }, "created":"2011-03-26T15:38:11.072+0000", "size":24576, "mimeType":"text/csv", "content":"https://jira.atlassian.com/secure/attachment/45565/log_110314_115720______.csv" }, { "self":"https://jira.atlassian.com/rest/api/2/attachment/40875", "id":"40875", "filename":"times.png", "author":{ "self":"https://jira.atlassian.com/rest/api/2/user?username=rkrishna", "name":"rkrishna", "key":"rkrishna", "emailAddress":"rkrishna at atlassian dot com", "avatarUrls":{ "48x48":"https://jira.atlassian.com/secure/useravatar?ownerId=rkrishna&avatarId=66525", "24x24":"https://jira.atlassian.com/secure/useravatar?size=small&ownerId=rkrishna&avatarId=66525", "16x16":"https://jira.atlassian.com/secure/useravatar?size=xsmall&ownerId=rkrishna&avatarId=66525", "32x32":"https://jira.atlassian.com/secure/useravatar?size=medium&ownerId=rkrishna&avatarId=66525" }, "displayName":"Roy Krishna", "active":true, "timeZone":"Australia/Sydney" }, "created":"2010-09-21T05:31:32.198+0000", "size":39226, "mimeType":"image/png", "content":"https://jira.atlassian.com/secure/attachment/40875/times.png", "thumbnail":"https://jira.atlassian.com/secure/thumbnail/40875/_thumb_40875.png" } ], "summary":"User Preference: User Time Zones", "customfield_17432":null, "customfield_17431":null, "customfield_13230":"2003-11-17 01:55:10.76", "customfield_15532":null, "customfield_13231":"1_*:*_2_*:*_244284876561_*|*_5_*:*_1_*:*_0_*|*_10032_*:*_3_*:*_27387070956_*|*_10031_*:*_2_*:*_22643859294", "customfield_17831":null, "customfield_15530":null, "customfield_17830":null, "customfield_17433":null, "customfield_15932":null, "customfield_15930":null, "customfield_11730":null, "customfield_15931":null, "customfield_12931":null, "environment":null, "duedate":null, "comment":{ "comments":[ { "self":"https://jira.atlassian.com/rest/api/2/issue/10148/comment/17800", "id":"17800", "author":{ "self":"https://jira.atlassian.com/rest/api/2/user?username=owen%40atlassian.com", "name":"owen@atlassian.com", "key":"owen@atlassian.com", "emailAddress":"owen at atlassian dot com", "avatarUrls":{ "48x48":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=48", "24x24":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=24", "16x16":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=16", "32x32":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=32" }, "displayName":"Owen Fellows", "active":true, "timeZone":"Etc/UTC" }, "body":"This is due to users not having a timezone set, see linked Issue.", "updateAuthor":{ "self":"https://jira.atlassian.com/rest/api/2/user?username=owen%40atlassian.com", "name":"owen@atlassian.com", "key":"owen@atlassian.com", "emailAddress":"owen at atlassian dot com", "avatarUrls":{ "48x48":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=48", "24x24":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=24", "16x16":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=16", "32x32":"https://avatar-cdn.atlassian.com/5aa96f52989b58639779e66325f190a0?d=mm&s=32" }, "displayName":"Owen Fellows", "active":true, "timeZone":"Etc/UTC" }, "created":"2003-11-17T01:55:10.760+0000", "updated":"2003-11-17T01:55:10.760+0000" }, { "self":"https://jira.atlassian.com/rest/api/2/issue/10148/comment/19723", "id":"19723", "author":{ "self":"https://jira.atlassian.com/rest/api/2/user?username=firewater", "name":"firewater", "key":"firewater", "emailAddress":"michael dot phillimore-brown at rbs dot com", "avatarUrls":{ "48x48":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=48", "24x24":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=24", "16x16":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=16", "32x32":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=32" }, "displayName":"Michael Phillimore-Brown", "active":true, "timeZone":"Europe/London" }, "body":"You could presumably identify the user''s locale from the request too...", "updateAuthor":{ "self":"https://jira.atlassian.com/rest/api/2/user?username=firewater", "name":"firewater", "key":"firewater", "emailAddress":"michael dot phillimore-brown at rbs dot com", "avatarUrls":{ "48x48":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=48", "24x24":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=24", "16x16":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=16", "32x32":"https://avatar-cdn.atlassian.com/541855c634ee3b570bc13deb97d5923b?d=mm&s=32" }, "displayName":"Michael Phillimore-Brown", "active":true, "timeZone":"Europe/London" }, "created":"2004-02-20T09:23:01.680+0000", "updated":"2004-02-20T09:23:01.680+0000" }, { "self":"https://jira.atlassian.com/rest/api/2/issue/10148/comment/28861", "id":"28861", "author":{ "self":"https://jira.atlassian.com/rest/api/2/user?username=grant.mckenzie%40morganstanley.com", "name":"grant.mckenzie@morganstanley.com", "key":"grant.mckenzie@morganstanley.com", "emailAddress":"grant dot mckenzie at morganstanley dot com", "avatarUrls":{ "48x48":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=48", "24x24":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=24", "16x16":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=16", "32x32":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=32" }, "displayName":"Grant McKenzie", "active":true, "timeZone":"Etc/UTC" }, "body":"I''d like to see this feature aswell. In a centralized server that is shared internationally, people want to see local times not EST or GMT.\n\nThis cannot be inferred from the locale in my opinion. I might live in the US and have a timezone of EST but want to display the German language interface.\n", "updateAuthor":{ "self":"https://jira.atlassian.com/rest/api/2/user?username=grant.mckenzie%40morganstanley.com", "name":"grant.mckenzie@morganstanley.com", "key":"grant.mckenzie@morganstanley.com", "emailAddress":"grant dot mckenzie at morganstanley dot com", "avatarUrls":{ "48x48":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=48", "24x24":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=24", "16x16":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=16", "32x32":"https://avatar-cdn.atlassian.com/aef7a23c6701e190b93a6fa14f972f85?d=mm&s=32" }, "displayName":"Grant McKenzie", "active":true, "timeZone":"Etc/UTC" }, "created":"2004-12-21T22:40:09.316+0000", "updated":"2004-12-21T22:40:09.316+0000" } ], "maxResults":141, "total":141, "startAt":0 } } }' DECLARE @i INT = 0 WHILE @i < 3 BEGIN INSERT INTO dbo.IssueHistory_STAGED ( RawJson ) VALUES ( @json ) SET @i += 1 WAITFOR DELAY '00:00:01' END SELECT * FROM dbo.uvw_IssueHistory SELECT * FROM dbo.uvw_Issue SELECT * FROM dbo.uvw_IssueComponent SELECT * FROM dbo.uvw_IssueComment SELECT * FROM dbo.uvw_IssueLabel {{tag> }}