Dates, times, and timestamps - Connect_CDC - connect_cdc_mimix_share - Latest

The Expression Handler recognizes columns that have a JDBC type of DATE, TIME, or TIMESTAMP. However, even though these datatypes are distinct, they are processed in the Expression Handler as character string values, and are considered as type CHARACTER. Because of the way that JDBC retrieves these values, they are input to the Expression Handler in the following formats:

yyyy-MM-dd HH:mm:ss.SSSSSS for a timestamp
			yyyy-MM-dd for a date 

and

HH:mm:ss for a time.

In a Connect CDC model, the Director allows most any type of heterogeneous mapping from one DBMS type to another with any combination of source date, time, and timestamp to target date, time, and timestamp. For more information on datatype mappings, refer to Supported datatypes.  

This means that the kernel Preparer, when constructing a date, time, or timestamp target column value which is mapped from a source date, time, or timestamp, will normalize the target value appropriately, so that the output result is always in Java/JDBC timestamp format. For example, if a timestamp is mapped to a time, the date portion will be removed; if a time is mapped to a timestamp, the current date will be appended.

No expression needs to be coded regardless of the datatype or the source or target DBMS type. The normalization of date, time, or timestamp when mapped to a target date, time, or timestamp, occurs automatically.

In addition, if a date, time, or timestamp value is mapped to a character column, the Preparer automatically removes all punctuation except for numeric digits. This is the special behavior for these kinds of mappings:

timestamp -> character: yyyyMMddHHmmssSSSSSSdate      -> character: yyyyMMddtime      -> character: HHmmss

Assume an input date of '2010-12-25 08:36:01.020000':

timestamp -> character: substring(<timestamp>, 1);
			output result -> 2010-12-25 08:36:01.020000
			timestamp -> character: substring(<timestamp>, 1, 23);
			output result -> 2010-12-25 08:36:01.020
			timestamp -> character: substring(<timestamp>, 1, 19);
			output result -> 2010-12-25 08:36:01
			timestamp -> character: substring(<timestamp>, 1, 10);
			output result -> 2010-12-25

If you need to edit the date, time, or timestamp values, you can use the “dateformat” method. There are 2 forms:

CHAR dateformat (CHAR inputDate, CHAR outFormat);
			CHAR dateformat (CHAR inputDate, CHAR outFormat, CHAR inFormat);

In the first form, the assumption is that the date is in standard JDBC timestamp format. The 1st parameter is the input date and the 2nd parameter is the format that you want for the date output. The format characters are those allowed in java.text.SimpleDateFormat.

Refer to the Javadoc HTML documentation for the complete list of format characters. The table shown below contains a list of the more commonly used formatting characters:

If the input date is not in standard JDBC timestamp format, then the second form of dateformat can be used. This enables you to specify the input format. As in the output format, the format characters you can specify are those allowed in SimpleDateFormat. Assume that the column S"Ctimestamp" contains the value '2010-12-25 08:36:01.020000' then what follows are some sample transformations:

Expression = dateformat("Ctimestamp",'yyyyMMdd');
			Result Value = 20101225
			 
			Expression = dateformat("Ctimestamp",'ddMMMyyyy');
			Result Value = 25Dec2010
			 
			Expressions = dateformat("Ctimestamp",'yyyyDDD');
			Result Value = 2010359