How to convert time in numeric to TIME field in SQL Script?

[haneeshmarella]

Hi, I am using AS400 DB2 SQL script. I am trying to figure out how can I convert numeric time field to a TIME format. I have numeric timefield of different lengths.

Example,

5 which is 00:00:05

10 which is 00:00:10

2529 which is 00:25:29

123223 which is 12:32:23

How do I convert it into TIME within script and not the LOAD since I would like to create QVD based on script, rather than making transformations later based on QVD which takes even longer time due to the large amount of data?

Thank you.

[sunny_talwar]

You can do this

LOAD Interval(TIME/86400, 'hh:mm:ss') as TIMEField,

     *

SELECT ....

FROM ....;

Store ....;

LOAD Interval(Time#(Num(TIME, '000000'), 'hhmmss'), 'hh:mm:ss') as TIMEField,

     *

SELECT ....

FROM ....;

Store ....;

[haneeshmarella]

Hi Sunny,

I am looking to implement it within the SQL script and not the LOAD. The field B.PRTCR is the TIME in numeric form. I want to convert it as mentioned in the question to TIME FIELD in below script itself without using load.

SQL SELECT DISTINCT A.PHPKTN AS PICKTICKET#,

    A.PHSHCN AS SHIP_TO_COUNTRY,

    A.PHSVIA AS PLANNED_SHIP_VIA,

    A.PHTCT AS TOTAL_NO_OF_CARTONS,

    B.PRUSER AS PKMS_USERS,

    B.PRCASN AS CASE#,

    B.PRTLOC AS TO_LOCATION,

    B.PRMNOP AS MENU_OPTION_NAME,

    B.PRFLOC AS FROM_LOCATION,

    B.PRSTYL AS STYLE,

    B.PRUNTS AS UNITS#,

    B.PRDCR AS DATE_STRING,

    B.PRTCR AS TIME_STRING,

    CONCAT(B.PRDCR,B.PRTCR) AS DATE_TIME_STRING,

    C.USER_NAME AS USER_NAME

FROM CAPM01.WM0272PRDD.PHPICK00 A, CAPM01.WM0272PRDD.PRTRAN00 B, CAPM01.WM0272PRDD.USUSER00 C

[sunny_talwar]

Couple of things

1) If you really need this to be in SQL, its better to reach out to SQL guys... for starters you can look here

TO_CHAR (datetime)

2) Is there a problem doing this in LOAD statement? What do you not like about it?

[haneeshmarella]

Its currently in load statement. Since there is huge amount of data, although I follow incremental load, it has to load from QVD and apply transformations on it, and then add new records from QVD again with transformations, making it unoptimized and takes longer time.

I have searched on many DB2 SQL forums for conversion but am not able to find the right one. Hence, thought someone here would be able to help.

[sunny_talwar]

I think you have never used Preceding Load‌. You don't have to transform this while loading from the qvd... but you can transform this before saving this data to qvd like this

LOAD *,

    Interval(Time#(Num(TIME_STRING, '000000'), 'hhmmss'), 'hh:mm:ss') as TIME;

SQL SELECT DISTINCT A.PHPKTN AS PICKTICKET#,

    A.PHSHCN AS SHIP_TO_COUNTRY,

    A.PHSVIA AS PLANNED_SHIP_VIA,

    A.PHTCT AS TOTAL_NO_OF_CARTONS,

    B.PRUSER AS PKMS_USERS,

    B.PRCASN AS CASE#,

    B.PRTLOC AS TO_LOCATION,

    B.PRMNOP AS MENU_OPTION_NAME,

    B.PRFLOC AS FROM_LOCATION,

    B.PRSTYL AS STYLE,

    B.PRUNTS AS UNITS#,

    B.PRDCR AS DATE_STRING,

    B.PRTCR AS TIME_STRING,

    CONCAT(B.PRDCR,B.PRTCR) AS DATE_TIME_STRING,

    C.USER_NAME AS USER_NAME

FROM CAPM01.WM0272PRDD.PHPICK00 A, CAPM01.WM0272PRDD.PRTRAN00 B, CAPM01.WM0272PRDD.USUSER00 C

Notice I have a load on top of a SQL load to do my QlikView transformation... I am not doing this transformation when loading from the qvd

[sunny_talwar]

Or look here

https://bytes.com/topic/db2/answers/184829-number-time-conversion

[haneeshmarella]

Saw the same exact link but didn't help.

I did the same preceding load method Sunny, it's quick but not as fast, probably now I wonder if it's due to the speed of the DB connection/server. Thank you for your responses though, highly appreciate it.

I tagged you in one other post asking for your help, do you have an idea for that? Link below

How do I create Auto-Timer in Straight Table?

[sunny_talwar]

So, you see reload time difference between doing this

SQL SELECT DISTINCT A.PHPKTN AS PICKTICKET#,

    A.PHSHCN AS SHIP_TO_COUNTRY,

    A.PHSVIA AS PLANNED_SHIP_VIA,

    A.PHTCT AS TOTAL_NO_OF_CARTONS,

    B.PRUSER AS PKMS_USERS,

    B.PRCASN AS CASE#,

    B.PRTLOC AS TO_LOCATION,

    B.PRMNOP AS MENU_OPTION_NAME,

    B.PRFLOC AS FROM_LOCATION,

    B.PRSTYL AS STYLE,

    B.PRUNTS AS UNITS#,

    B.PRDCR AS DATE_STRING,

    B.PRTCR AS TIME_STRING,

    CONCAT(B.PRDCR,B.PRTCR) AS DATE_TIME_STRING,

    C.USER_NAME AS USER_NAME

FROM CAPM01.WM0272PRDD.PHPICK00 A, CAPM01.WM0272PRDD.PRTRAN00 B, CAPM01.WM0272PRDD.USUSER00 C

vs

LOAD *,

    Interval(Time#(Num(TIME_STRING, '000000'), 'hhmmss'), 'hh:mm:ss') as TIME;

SQL SELECT DISTINCT A.PHPKTN AS PICKTICKET#,

    A.PHSHCN AS SHIP_TO_COUNTRY,

    A.PHSVIA AS PLANNED_SHIP_VIA,

    A.PHTCT AS TOTAL_NO_OF_CARTONS,

    B.PRUSER AS PKMS_USERS,

    B.PRCASN AS CASE#,

    B.PRTLOC AS TO_LOCATION,

    B.PRMNOP AS MENU_OPTION_NAME,

    B.PRFLOC AS FROM_LOCATION,

    B.PRSTYL AS STYLE,

    B.PRUNTS AS UNITS#,

    B.PRDCR AS DATE_STRING,

    B.PRTCR AS TIME_STRING,

    CONCAT(B.PRDCR,B.PRTCR) AS DATE_TIME_STRING,

    C.USER_NAME AS USER_NAME

FROM CAPM01.WM0272PRDD.PHPICK00 A, CAPM01.WM0272PRDD.PRTRAN00 B, CAPM01.WM0272PRDD.USUSER00 C

How many rows are you pulling? and what is the time difference for the number of rows you just mentioned?

[sunny_talwar]

I tagged you in one other post asking for your help, do you have an idea for that? Link below

How do I create Auto-Timer in Straight Table?

Will check