Working with dates
This section shows how you work with dates in your automation using the convert date function.
To add a date formula, click Add formula > Date functions > Date in the input field.
Convert a date to a timestamp
Working with integers is easier than working with dates because they have a defined data type and Unix timestamps have a defined format. Unix timestamps represent a data as the number of seconds since 1-1-1970. You can convert dates to Unix timestamps by applying a data function formula. For example, this automation shows how to convert a Qlik Sense app creation date to a Unix timestamp.
The Get App Information block returns information about a Qlik Sense app. The output block outputs the createdDate key from the field mapped output of the preceding block. A formula is applied to the field mapping to covert the date to a Unix time stamp. The 'U' parameter in the formula specifies that the date value should be converted to a Unix timestamp.
Do the following to apply the formula in the Output block:
-
Drag the Output block and place it in the Editor.
-
In the Data to output filed, map the createdDate field from the Get App Information block.
-
Click on the field mapping and select Add formula.
-
Click on Date functions and search for Date in the search bar to select it.
-
Add output format as U. This converts the createdDate into a Unix timestamp.
The raw formula to convert the date looks like this:
{date:{$.GetAppInformation.info.attributes.createdDate},'U'}
Compare two dates
Using the example from the previous section, we can easily compare dates once they are converted to a Unix timestamp. For example, this automation uses a condition block to determine if the app was created recently or whether it was created more than five days ago.
The condition block converts the createdDate to a Unix timestamp and adds the condition greater than 5 days ago, as a Unix timestamp. Since the condition dates are both timestamps, they can easily be compared.
Do the following to apply the formula in the Condition block:
-
In the Input filed, map the createdDate field from the Get App Information block.
-
Click on the field mapping and select Add formula.
-
Click on Date functions and search for Date in the search bar to select it.
-
Add output format as U. This converts the createdDate into a Unix timestamp.
-
Select is greater than in the drop-down menu.
-
Click on the Value field > Add formula > Date functions > Date.
-
Add the Date as now-5. This subtracts 5 days from today's date.
-
Add output format as U. This converts the createdDate into a Unix timestamp.
The raw formula to convert the second condition date looks like this:
{date:'now - 5 days','U'}
You can also add or subtract months, hours, minutes, or seconds. Modifying the date with these operators looks like this:
{date:'now + 1 month','U'}
{date:'now + 5 hours','U'}
{date:'now - 120 minutes','U'}
{date:'now - 1500 seconds','U'}
For more information, see How to compare dates in an automation.
Calculate the difference between dates
Using the example from the first section, we can calculate the difference between two dates using a variable for the Unix timestamp. For example, this automation uses a variable to calculate the difference between the current date and the created date of the Qlik Sense app.
The variable block is set to the current date using a date formula to get the timestamp from now. This formula looks like this:
{date:'now','U'}
The variable has two more operations: one to substract the createdDate, which is the same formula as in the previous examples, and another to divide the result by the number of second in a day, which is 86400. This must be done because Unix timestamps are expressed in seconds. Configure the output block to show the difference in days.
To access the list of parameters when you are working on your automations, click on Show more in the Edit Formula dialog.
