-- Get the time zone from the server
DECLARE @TZName AS NVARCHAR(256);
EXEC master.dbo.xp_regread
    N'HKEY_LOCAL_MACHINE'
    ,N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
    ,N'TimeZoneKeyName'
    ,@TZName OUT;

-- Time zone can also be set manually but the above method is more reliable if your system allows you to read the value.
--SET @TZName = N'W. Europe Standard Time';

;WITH
    -- Extract JSON data
    ctePr (ID, importSourceId, result, ENDED) AS
    (
        SELECT
            pr.ID
            ,ISNULL(TRY_CAST(JSON_VALUE(pr.RESULT_DATA, N'strict $.importSourceId') AS INT), -1)    AS importSourceId
            ,ISNULL(TRY_CAST(JSON_VALUE(pr.RESULT_DATA, N'strict $.result') AS NVARCHAR(256)), N'') AS result
            ,pr.ENDED
        FROM dbo.AO_8542F1_IFJ_PRG_RES AS pr
    )
SELECT
    ios.NAME                                                                    AS objectschema_name
    ,isrc.NAME                                                                  AS import_name
    ,DATEDIFF_BIG(SECOND, MAX(pr.ENDED)AT TIME ZONE @TZName, CURRENT_TIMESTAMP) AS age_seconds
    ,MAX(pr.ENDED)AT TIME ZONE @TZName                                          AS LastSuccessfulImport
FROM dbo.AO_8542F1_IFJ_IMPORT_SRC           AS isrc
    INNER JOIN ctePr                        AS pr ON isrc.ID = pr.importSourceId
    INNER JOIN dbo.AO_8542F1_IFJ_OBJ_SCHEMA AS ios ON ios.ID = ISNULL(isrc.OBJECT_SCHEMA_ID, -1)
WHERE
    ISNULL(isrc.SYNCHRONIZATION_ENABLED, 0) = 1
    AND ISNULL(pr.result, N'')              = N'OK'
GROUP BY
    ios.NAME
    ,isrc.NAME
ORDER BY
    objectschema_name
    ,import_name;

SET TIME ZONE 'Europe/Berlin';
SELECT
    ios."NAME" AS objectschema_name,
    isrc."NAME" AS import_name,
    EXTRACT(epoch FROM(NOW() - MAX(pr."ENDED")))::INTEGER AS age_seconds
FROM
    "AO_8542F1_IFJ_IMPORT_SRC" isrc
JOIN public."AO_8542F1_IFJ_PRG_RES" AS pr ON
    json_extract_path_text(pr."RESULT_DATA"::JSON, 'importSourceId') = isrc."ID"::TEXT
JOIN "AO_8542F1_IFJ_OBJ_SCHEMA" ios ON
    ios."ID" = isrc."OBJECT_SCHEMA_ID"
WHERE
    isrc."SYNCHRONIZATION_ENABLED" = TRUE
    AND json_extract_path_text(pr."RESULT_DATA"::JSON, 'result') = 'OK'
GROUP BY
    ios."NAME",
    isrc."NAME"
ORDER BY
    objectschema_name ASC,
    import_name ASC;