sqldb2

How to format Db2 timestamp with UTC consideration


Given the following DB2 Script:

WITH ranked_dataset AS (
    SELECT 
        D.INSTNB AS Id,
        D.INSTNB AS transferId,
        H.FRSTCD AS fromStoreId,
        H.TOSTCD AS toStoreId,
        D.SENDDT AS sendDate,
        D.STANTS AS time_stamp,
        CASE 
            WHEN C.INSTNB IS NOT NULL THEN 'RECIEVED'
            ELSE 'SENT'
        END AS direction,
        json_arrayagg(
            json_object(
                'plu' value D.PRODCD,
                'quantity' value D.TRANQT,
                'sentProcessedTimestamp' value D.STANTS                 
            )
        ) items,
        ROW_NUMBER() OVER (PARTITION BY D.INSTNB, H.DIRECT, H.FRSTCD, H.TOSTCD, D.SENDDT ORDER BY D.STANTS DESC) AS rn
        
    FROM 
        SBOSFILES.HUBISTDT D
    INNER JOIN 
        SBOSFILES.HUBISTHD H
    ON 
        D.INSTNB = H.INSTNB
        AND D.FRSTCD = H.FRSTCD
        AND D.TOSTCD = H.TOSTCD
    LEFT JOIN
        SBOPFILES.HUBISTC C
    ON
        D.INSTNB = C.INSTNB
    WHERE
        H.DIRECT = 'T'
        AND DATE(D.STANTS) = '2018-04-30'
    GROUP BY 
        D.INSTNB,
        H.DIRECT,
        H.FRSTCD,
        H.TOSTCD,
        D.SENDDT,
        D.STANTS,
        C.INSTNB
)
SELECT json_object(
    'id' value (Id || '-' || direction),
    'transferId' value transferId,
    'fromStoreId' value fromStoreId,
    'toStoreId' value toStoreId,
    'sendDate' value sendDate,
    'time_stamp' value time_stamp,
    'direction' value direction,
    'items' value items FORMAT JSON
    )
FROM ranked_dataset  
WHERE rn = 1          
LIMIT 100

Which currently produces an output like this:

{"id":"1-SENT","transferId":1,"fromStoreId":2,"toStoreId":3,"sendDate":"2018-04-30","time_stamp":"2018-04-30-06.55.47.349000","direction":"SENT","items":[{"plu":25,"quantity":10,"sentProcessedTimestamp":"2018-04-30-06.55.47.349000"}]}

I need to make a change so that the parent object 'time_stamp' property and the child objects 'sentProcessedTimestamp' properties would be in this format:

"timestamp": "2018-04-30T05:55:47.349+01:00"

I have tried doing:

TO_CHAR(D.STANTS, 'YYYY-MM-DD"T"HH24:MI:SS.FF3') || '+01:00' AS time_stamp,  

But I get the following error:

An error occurred while processing the results. - Format string not valid for function VARCHAR_FORMAT.. SQLCODE=-20447, SQLSTATE=22007, DRIVER=4.19.56

Further Context

This query will be run from an Azure Data Factory pipeline, and will send to a blob storage container, which an azure function will pick up and insert into mongo db. We expect everything inserted into mongo will be UTC.


Solution

  • Not ideal but this is the best I come up with:

    with xx as (
      select CURRENT TIMESTAMP as ats FROM sysibm.sysdummy1
    )
    SELECT replace(to_char(ats, 'YYYY-MM-DD HH24:MI:SS.FF3'),' ' ,'T')  from xx