What was changed?

The previously existing Timestamp variables were reworked to DateTime variables. Changes were done for the variables to be less cumbersome to use, as for example timestamps had to be manually converted before being able to be compared. So to say, the newly introduced variable type enables to deal with time values in a more flexible way than before.

Changes were introduced in ONE DATA version 3.13.0. (Client version 1.173.0, Server version 46.31.0)

There are now five formats for DateTime variables, which decide how a variable should be parsed:

The default parsing type is SPARK_TIMESTAMP.

How does this affect working with ONE DATA?

Creating Workflow Variables in Variable Manager

The way to create time-related variables for workflows changed a little. The possibility to create a "Timestamp" variable was replaced with the new "DateTime" type. That comes with some new options in the Variable Manager Interface. There you can select which format the newly created variable has as default. (If not selected, the above mentioned default is set).

In addition to that, you can now select date and time for a defined variable. In comparison to the old version, it was only possible to select a date. The selected time is in the users current time-zone.

Processors

The default parsing type being SPARK_TIMESTAMP enables DateTime variables to be compared natively to any timestamp column in a workflow. A manual conversion using the Data Type Conversion processor is not necessary anymore.

In String config elements of processors, previously only usable with String variables, the selection of DateTime variables is now possible. This change includes for example "Input String Value" config element in the Add Constant String processor or "Value" config element in the Data Filter processor.

In Timestamp config elements, selection of DateTime variables is now possible. For example, in the Fallback Value config element of the Data Type Conversion processor a DateTime variable can now be selected.

Also for query config elements, there are some helpful features introduced. It is now possible to provide a modifier in the '@varName@' annotation to overwrite the used format of a DateTime variable. The modifiers are equal to the time formats listed in the table above. Beneath changing the format, you can also specify a string pattern, that defines how the DateTime value is displayed.

You can apply the options to a variable like this:

-- Modifier
@varName[MODIFIER]@

-- Modifier and string pattern
@varName[MODIFIER|yyyy-MM-dd]@

-- Example
@varName[SPARK_TIMESTAMP|yyyy-MM-dd]@

Note that the given pattern needs to follow the requirements of the java DateTime.toString() method.
Also it is not possible to apply all patterns to the types 'ORACLE_DATE' and 'ORACLE_TIME' because Oracle uses some slightly different patterns sometimes.

Examples and Tips

DateTime Variables within Spark-SQL Queries

When working with DateTime variables within a Query Processor, it is possible to perform filtering and some calculations with the values.

Here are some examples how to do that:

-- Subtract 2 Hours from the datetime value of the variable
'@dateTimeVariable[SPARK_TIMESTAMP]@' - INTERVAL 2 HOURS

-- Take all values where the timestamp of the column is after the specified timestamp of the datetime variable
SELECT * FROM inputTable i
WHERE '@dateTimeVariable@' < i.timestamp_column

Troubleshooting

Note that when using Query processors (e.g. Double Input Query processor), annotations in the formats STRING and SPARK_TIMESTAMP need to be put into apostrophes, like for example '@varName@'.

This is not necessary inside the Database Connection Load Processor when targeting an ORACLE database, as Oracle variables get replaced with functions, which the data base can interpret. For LONG it depends on how the variable will be used. As seen in how the modifiers are replaced in the query.