Find the creation date of a deployment in database

Products

• CA Release Automation - Release Operations Center (Nolio)
• CA Release Automation - DataManagement Server (Nolio)

Issue/Introduction

How to find the creation date of the deployments in the database using a SQL Query ?

Environment

Release Automation - All Versions

Resolution

Hera are example of SQL Queries for Microsoft SQL Server and Oracle :

Microsoft SQL Server :

IF OBJECT_ID ('tempdb..#TMP1', 'U') IS NOT NULL DROP TABLE #TMP1
IF OBJECT_ID ('tempdb..#TMP2', 'U') IS NOT NULL DROP TABLE #TMP2

SELECT r.id
INTO #TMP1
FROM rc_releases r
WHERE r.intended_release_id is null

SELECT release_id, dateadd(ss,min(StartTime)/1000 + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Create Date'
INTO #TMP2
FROM rc_stages where release_id in (SELECT id FROM #TMP1 )
GROUP BY release_id

SELECT t.[Create Date], r.id, r.name, r.currentStage, r.release_result, r.created_by, a.APP_NAME 'Application Name'
FROM rc_releases r
INNER JOIN #TMP2 t ON r.id=t.release_id
INNER JOIN applications a ON a.ID=r.application

 

 

 

Oracle :

column CREATEDATE format a40
column NAME format a20
column CURRENTSTAGE format a20
column RELEASE_RESULT format a20
column CREATED_BY format a20
column APPLICATIONNAME format a20

set linesize 1000
alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

CREATE GLOBAL TEMPORARY TABLE release_tmp1 ON commit preserve rows AS SELECT r.id FROM rc_releases r WHERE r.intended_release_id is null ;
CREATE GLOBAL TEMPORARY TABLE release_tmp2 ON commit preserve rows AS SELECT release_id, CAST(date '1970-01-01' + min(starttime) /1000 * interval '1' second AS TIMESTAMP) AT TIME ZONE SESSIONTIMEZONE CreateDate FROM rc_stages where release_id in (SELECT id FROM release_tmp1) GROUP BY release_id ;

SELECT t.CreateDate, r.id, r.name, r.currentStage, r.release_result, r.created_by, a.APP_NAME ApplicationName FROM rc_releases r INNER JOIN release_tmp2 t ON r.id=t.release_id INNER JOIN applications a ON a.ID=r.application ;

TRUNCATE TABLE release_tmp1 ;
DROP TABLE release_tmp1 ;
TRUNCATE TABLE release_tmp2 ;
DROP TABLE release_tmp2 ;