Ask Your Question

Why does time:extractDateFromString add 10 minutes to a timestamp?

asked 2017-10-20 11:25:37 -0500

metadaddy gravatar image

updated 2017-10-20 11:25:47 -0500

When I format a timestamp back and forth, 10 minutes are added:

${time:extractStringFromDate(time:extractDateFromString(record:value("/ts"),"yyyy-MM-dd hh:mm:ss.SSSSSS"),"yyyy-MM-dd hh:mm:ss.SSS")}

in:  2017-10-20 13:15:33.611796
out: 2017-10-20 01:25:44.796
edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted

answered 2017-10-20 11:29:37 -0500

metadaddy gravatar image

You're giving it S in the format, which is defined by the underlying Java SimpleDateFormat to be milliseconds. Looks like the Java date parsing assumes you really want 611796 milliseconds, and adds 611 seconds, or just over 10 minutes to the time.

You can trim the milliseconds off the date string like this:

${time:extractDateFromString(str:substring(record:value("/ts"), 0, 23),"yyyy-MM-dd hh:mm:ss.SSS")}

Capture them if you need them like this:

${str:substring(record:value("/ts"), 23, 26)}

This gives you the microseconds portion as a string - you would need to use a Field Type Converter stage to change it to a LONG or an INTEGER.

To get back what you started with:

${time:extractStringFromDate(time:extractDateFromString(str:substring(record:value("/ts"), 0, 23),"yyyy-MM-dd hh:mm:ss.SSS"), "yyyy-MM-dd hh:mm:ss.SSS")}${str:substring(record:value("/ts"), 23, 26)}

Note string concatenation in that last expression by simply doing ${expr1}${expr2}

edit flag offensive delete link more


In my scenario we are using JDBC multitable consumer and the source column in DB2 is timestamp (microsecond precision). This source column is automatically converted to DATETIME in streamsets and the micorsecond value is stored in the field property 'nanoSeconds'. I am using expression evaluator w

Div gravatar imageDiv ( 2018-03-28 18:04:37 -0500 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-10-20 11:25:37 -0500

Seen: 1,005 times

Last updated: Oct 20 '17