Examples of DateTime Variables Used in Processors

Modified on Wed, 06 Apr 2022 at 02:31 PM

In the following, we present different ways to use DateTime variables in processors.

Example Variables

For the following examples, we created these four DateTime variables, which represent the starting dates of each quarter of a year. Also, there is an additional fallback value that will be used later on.

Note that depending on the type of processor configuration element, either "Technical Name" or "Name" have to be used for selecting variables.

The table below shows the value of each variable plus the respective value type. (More information about creating DateTime variables and their possible Formats can be found here.)

Technical Name	Format	Value
first_quarter	SPARK_TIMESTAMP	2022-01-01 as firstQuarter
second_quarter	SPARK_TIMESTAMP	2022-04-01 as second
third_quarter	SPARK_TIMESTAMP	2022-07-01 third
fourth_quarter	ORACLE_TIMESTAMP	2022-10-01 fourth
fallback	SPARK_TIMESTAMP	2022-03-01 Fallback

Example Workflows

The following workflow corresponds to the examples 1-3

Example 1 - Custom Input Table

This processor provides the workflow with the input data. It contains two columns. The first column "Deadline" holds the different dates declared in the variable manager. The second column holds the project corresponding to the deadline.

The first column is declared as of a "datetime / interval" type, the technical name is used and the variable is placed between two "@".

The previous setting is valid for SPARK_TIMESTAMPs. In case the variable is declared as an ORACLE_TIMESTAMP, it needs to be cast to a SPARK_TIMESTAMP using the syntax "technical_name[SPARK_TIMESTAMP]" as shown in the example.

More information on the formatting of variables can be found here.

The result presents the dates in the format YYYY-MM-DD (SPARK_TIMESTAMP format) plus the corresponding time up to milliseconds.

Example 2 - Variable in Query

In this example we use a Query processor with the same input as in the first example and a simple query statement using a DateTime variable as follows.

When using variables in queries, the technical name needs to be between two "@". The variable needs to be put between simple quotes as well.

More information can be found here.

Contrarily to the previous example, SQL can support ORACLE_TIMESTAMPs in certain use cases.
If the ORACLE_TIMESTAMP variable is to be interacted with (compared for instance), then it needs to be cast to a SPARK_TIMESTAMP variable using the syntax "technical_name[SPARK_TIMESTAMP]". Otherwise, it can be referred to as a ORACLE_TIMESTAMP variable without the need for quotes, using the syntax @fourth_quarter@.

Example 3 - Variable in String Config Element

In this example we use a Data Filter processor with the same input as in the first example.

Using the processor to filter DateTimes, the corresponding variable needs to be entered in the "Value" configuration field. A dropdown with a list of available system variables and workflow variables is shown. Opposing to previous examples using the technical name of the variable, here, the user needs to select the regular name.

Important: Note that the variable needs to be of format SPARK_TIMESTAMP since no casting is possible.

The execution result is as follows.

Example 4 - Variable in DateTime Config Element

For this example a different input with the columns "Deadline" and "Project" is being used. Column "Deadline" is filled with strings containing dates. Plus, the fourth entry has a different format than the rest.

Using a Data Type Conversion processor the string column "Deadline" will be converted to a column of type DateTime.

In the "Convert To Datetime" configuration element, "Deadline" is selected as the column to be converted. For the fallback value, the previously defined variable "Fallback" is selected in the variables dropdown. Only variables of the type DateTime appear there.

All four entries of the result are of type DateTime and formatted as a SPARK_TIMESTAMP. As the fourth input entry is in a different format than defined in the processor configuration, it was not able to be converted and the fallback value was used instead.

The date used in the fallback variable lies in the past. When sorting the projects by their deadlines, this would stand out and tell the user that something in the input data could be wrong.

Note that the used value in a DateTime variable is considered as UTC. Also all ONE DATA workflows return timestamp results in UTC format.