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.
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:
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:
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 .
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.
substringAfter(substringBefore($timestamp_string,'Z'), '.')This returns the string 670 for our example, which is the number of milliseconds.
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'>
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!
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.