1st Use case: 1 row storage format in Jira Database

What is the value of Test-1 issue who has a Dates custom field where the "Date Name" = Feature Done, "Date Value" = 15.07.2023 ?

1. first create a test project with key = DEMO

2. second create a grid with 2 column only:

   • Date name = string column type

   • Date value = date column type

3. create an issue and grid data : 1 row =
   "Date Name" = Feature Done, "Date Value" = 15.07.2023

4. On the Jira issue you should have something like that:
   

   Image Added

5. connect to PostgreSQL database system, using Jira database, and run this SQL command:
   - issuenum=1 is the number of your issue

   Code Block
   language sql
   SELECT id, customfield, textvalue FROM customfieldvalue WHERE issue= (SELECT id FROM jiraissue WHERE issuenum=1 AND project= (SELECT id FROM project WHERE pkey='DEMO'));

6. Here is the result of this PostgreSQL command to show how data are stored in the database:

{
   "ducketId":"8",
   "rows":[
      {
         "rowId":"row_288c5098-de10-4257-9ed5-599dec6f497c",
         "columns":{
            "datevalue":1689379200000,
            "order":0,
            "datename":"Feature Done"
         },
         "order":0
      }
   ]
}

2nd Use case: 2 rows storage format in Jira Database

What is the value of Test-2 issue who has a Dates custom filed where the "Dates Name" = Feature Start Date and "Date Value" = 03.10.2023 and has another row with "Date Name" = Feature End Date and "Date Value" = 31.10.2023 ?

1. Following the first example, this is the issue with 2 rows:

   Image Added

2. connect to PostgreSQL database system, using Jira database, and run this SQL command:
   - issuenum=2 is the number of your issue

   Code Block
   language sql
   SELECT id, customfield, textvalue FROM customfieldvalue WHERE issue= (SELECT id FROM jiraissue WHERE issuenum=2 AND project= (SELECT id FROM project WHERE pkey='DEMO'));

3. Here is the result of this PostgreSQL command to show how data are stored in the database:

   Code Block
   language json
   { "ducketId":"8", "rows":[ { "rowId":"row_10639a9d-8600-418f-9939-a4b227485b3b", "columns":{ "datevalue":1696291200000, "order":0, "datename":"Feature End Date" }, "order":0 }, { "rowId":"row_f299d017-f29e-47e7-9801-f70cbfffa942", "columns":{ "datevalue":1698710400000, "order":1, "datename":"Feature Start Date" }, "order":1 } ] }

3rd Use case: 1 row and 2 rows storage format using Data Mirror feature

You can learn more about Data Mirror feature here: Data Mirror

All data are stored in a table called testdate in the DB of your choice (here i'm using testtge Database), using column name from the grid config.

To display the results, it a simple SQL command:

SELECT * from testdate;

Image Added