Content

1. Intro
2. DateTime-Variable in ONE DATA
3. Data Type Conversion-Processor
4. Modifiers
5. DateTime Variables in Spark SQL
   • Syntax
   • Conversions and Manipulation

Intro

To be able to properly work with DateTimes, ONE DATA has an integrated DateTime variable type. It comes with options to save your values in different formats that fit best for your use case and includes features to display the variables in certain ways, using modifiers. The table below contains all existing DateTime formats in OD at the moment:

DateTime-Variable in ONE DATA

In the Variable Manager of a workflow, you can create a new DateTime variable and select your desired format.

Then you can define its value in the Variable Manager by using the date- and time-pickers that show up when clicking on the respective boxes.

Data Type Conversion-Processor

When using a Data Type Conversion Processor to convert a string to DateTime, you will get the value in SPARK_TIMESTAMP format.

This means in order to compare "datetimes" obtained via a workflow variable to "datetimes" in a dataset you need to convert either one of them. 

Modifiers

Within query config elements of processors (e.g. for the Query Processor), the display pattern of DateTime variables can be changed by using modifiers. When referencing variables in queries, the syntax is @variableName@. To apply modifiers you just need to put them in brackets behind the variable name. Some examples are shown below:

-- 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.

DateTime Variables in Spark SQL

When you want to use a DateTime variable in Spark SQL, for example in a Query Processor, there are some useful conversions and some things to note, that will make your work with DateTime variables easier.

Syntax

A very crucial point to mention is, that all variables in the format 'SPARK_TIMESTAMP' and 'STRING' need to be put in apostrophes when referenced within a Query. Here's an example:

SELECT '@stringDateTimeVariable@'

Note that this also applies to e.g. a variable of format 'LONG' that is converted to 'STRING' with a modifier. The expression also needs to be put in single apostrophes like this: '@longDateTime[STRING]@'

-- Subtract three hours from a DateTime variable of format SPARK_TIMESTAMP
SELECT '@dt_var@' - INTERVAL 3 HOURS as exampleValue

-- Subtract two hours from a DateTime variable of any other format type
SELECT '@dt_var[SPARK_TIMESTAMP]@' - INTERVAL 3 HOURS as exampleValue