RBA/LRSN to Timestamp and vice-versa

Bharath Nunepalli

RBA/LRSN to Timestamp and vice-versa

Hi All,

I did the following to convert RBA/LRSN to Timestamp and vive-versa.

1. Executed the below SQL query to get the minutes and converted that to microseconds
SELECT TIMESTAMPDIFF(4, CHAR(TIMESTAMP('2016-12-02-22.00.00.000000') - TIMESTAMP('1900-01-01-00.00.00.000000'))) FROM SYSIBM.SYSDUMMY1;

That will be 3686853600000000 micro seconds.

2. With LRSN=SUBSTR(D2X(MS), 1, 12), I got D192C7F2AB80

3. When I convert it back to Timestamp using BLSUXTOD, getting 10/30/2016 22:00:00.000000 but not 12/02/2016 22:00:00.000000

What did I do wrong here?

 

Thanks.

Venkat Srinivasan

RE: RBA/LRSN to Timestamp and vice-versa
(in response to Bharath Nunepalli)

Wrong choice of function as it assumes there are only 365 days in a year. This is documented in SQL reference.

Between the two timestamps there are 42704 days and 22 hours. Convert that to microseconds and its hex value is what you want. To get the STCK itself you multiply mic seconds by 4096. 

If you are doing these types of date time arithmetic to be used in recovery etc, you should also account for local time offsets. Some sites may be using leap seconds to steer the clock away. That may need to be accounted if precise estimate is required. If in zOS use CONVTOD service macro to do the conversion. If in distributed side use epoch. (its base is midnight of 1970 and not 1900).

Your subject says RBA/LRSN but RBA is not time derived and cannot be converted to timestamp. 

Venkat 

In Reply to Bharath Nunepalli:

Hi All,

I did the following to convert RBA/LRSN to Timestamp and vive-versa.

1. Executed the below SQL query to get the minutes and converted that to microseconds
SELECT TIMESTAMPDIFF(4, CHAR(TIMESTAMP('2016-12-02-22.00.00.000000') - TIMESTAMP('1900-01-01-00.00.00.000000'))) FROM SYSIBM.SYSDUMMY1;

That will be 3686853600000000 micro seconds.

2. With LRSN=SUBSTR(D2X(MS), 1, 12), I got D192C7F2AB80

3. When I convert it back to Timestamp using BLSUXTOD, getting 10/30/2016 22:00:00.000000 but not 12/02/2016 22:00:00.000000

What did I do wrong here?

 

Thanks.

Bharath Nunepalli

RE: RBA/LRSN to Timestamp and vice-versa
(in response to Venkat Srinivasan)

Venkat,

 

My bad, it shouldn't be RBA/LRSN to Timestamp but just LRSN to Timestamp.

Thanks for pointing out that.

 

Reg CONVTOD service macro, is there any sample REXX using that?

Jorge Martelanz

RE: RBA/LRSN to Timestamp and vice-versa
(in response to Bharath Nunepalli)

I wrote this REXX that gets a timestamp as an argument and convert it to LRSN (V10 format)

/* REXX */
/* Converts timestamp to DB2 LRSN */
numeric digits 31
parse args ts /* Get the timestamp as a parameter */
parse var ts yr '-' mt '-' dy '-' hr '.' mi '.' se '.' ms
dys = date('B', yr||mt||dy,'S') /* Days since 0001-01-01 */
dys = dys - 693595 /* Minus days from 0001-01-01 to 1900-01-01 */
tms = (dys * 86400000000) + (hr * 3600000000) + (mi * 60000000) ,
+ (se * 1000000) + ms /* Total microseconds for the timestamp */
say 'Timestamp:' ts
lrsn subsrt(d2x(tms),1,12)
say 'LRSN: ' lrsn

 

Hope this helps

Jorge Martelanz