In many SAS Event Stream Processing (ESP) models, the connector or adapter can convert a date or timestamp while reading data from the source. However, if the model reads in an opaque string of XML or JSON or a base64 encoded string, the date or timestamp might be in a string field, like 2018-05-28T09:33:55.670Z. To convert the string field into a date or timestamp field, use a functional window. Here is a simple example of how to implement the functional window. The complete model and input data are attached.
The model starts with a source window, which has two fields. The first field is int64 field named "id" which is the key for the window. The second field is a string field named "base64_string", which holds the base64 encoded string.
Next, a functional window processes the data. A string field named "timestamp_string" is used to hold the decoded string containing the timestamp.
<function name="timestamp_string"><![CDATA[base64Decode($base64_string)]]></function>
After this function runs, the timestamp_string field contains the timestamp to be parsed, 2018-05-28T09:33:55.670Z . Now, the functional window is ready to start parsing the timestamp.
First, let's look at the format SAS ESP uses to store time fields. SAS ESP supports two type of time fields:
- A DATETIME field with granularity to the second
- A TIMESTAMP field with granularity to the microsecond
Both field values are stored as an integer number. The DATETIME is the number of seconds since UNIX epoch. The TIMESTAMP field is the number of microseconds since UNIX epoch.
The goal of our functional window is to convert the string timestamp of "2018-05-28T09:33:55.670Z" into an integer number of 1527514435 seconds for a DATETIME field or 1527514435670000 microseconds for a TIMESTAMP field.
The functional window defines two variables for the time fields.
<field name="datetime" type="date"/>
<field name="timestamp" type="stamp"/>
To convert the string timestamp into a DATETIME field, we use the timeParse function. The timeParse function accepts a UNIX strptime function format to parse the timestamp value. For the string timestamp of "2018-05-28T09:33:55.670Z", the strptime format is '%Y-%m-%dT%H:%M:%S' . Here is the complete timeParse function:
<function name="datetime"><![CDATA[timeParse($timestamp_string,'%Y-%m-%dT%H:%M:%S')]]></function>
After this function runs, the field "datetime" contains the integer value 1527514435.
Converting the string timestamp "2018-05-28T09:33:55.670Z" into a microsecond value is a little more complicated. The value needs to be split into two parts, the value for the seconds and the milliseconds after the period in the string timestamp, then both are converted into microseconds and added together .
- For the value in seconds, you can reuse the timeParse function above.
timeParse($timestamp_string,'%Y-%m-%dT%H:%M:%S')
Note:If both a DATETIME field and a TIMESTAMP field are in the model, the result of the previous function stored in $datetime could be used instead of using the timeParse function a second time.
- The value in seconds needs to be converted to microseconds by multiplying the result by 1000000 using the product function.
product(timeParse($timestamp_string,'%Y-%m-%dT%H:%M:%S'),1000000)
- Then the value of the microseconds from the timestamp needs to be calculated. The timestamp string is using milliseconds. The number after the period needs to be parsed into a value using the substringBefore function to remove the Z at the end of the string and the substringAfter function to return only the value after the period:
substringAfter(substringBefore($timestamp_string,'Z'), '.')
This returns the string 670 for our example, which is the number of milliseconds.
- Now convert the milliseconds into microseconds by multiplying the result by 1000 using the product function
product(substringAfter(substringBefore($timestamp_string,'Z'), '.'),1000)
- Finally add the two parts together with the sum function to get the complete function
sum(product(timeParse($timestamp_string,'%Y-%m-%dT%H:%M:%S'),1000000),product(substringAfter(substringBefore($timestamp_string,'Z'), '.'),1000))
The final function is:
<function name="timestamp"><![CDATA[sum(product(timeParse($timestamp_string,'%Y-%m-%dT%H:%M:%S'),1000000),product(substringAfter(substringBefore($timestamp_string,'Z'), '.'),1000))]]></function>
The functional window outputs the following event
<event opcode='insert' window='timeParse_example/cq1/Functional1'>
<value name='datetime'>1527514435</value>
<value name='id'>0</value>
<value name='timestamp'>1527514435670000</value>
<value name='timestamp_string'>2018-05-28T09:33:55.670Z</value>
</event>
Now the datetime and timestamp fields can be used as actual time values in other windows in the model. This example shows how the functional window is a powerful tool for transforming and converting data inside an ESP window!
