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.
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