Performing operations on the elements of a date variable may sometimes result in nonsensical values for one or more elements. For example, consider the date and time of 6 December 2021, 3pm, which is stored in a date variable like this:
2021 - 12 - 06 15 : 00 : 00
Adding a value of 2 to the month element results in the following date variable:
2021 - 14 - 06 15 : 00 : 00
This is nonsensical as there is no 14th month.
FLOW handles this by adjusting the date value to a sensible one automatically. This is called normalisation. The following section provides examples of how normalisation works on the month, day, hour, minute, and second elements of a date variable.
Note: FLOW does not normalise dates earlier than 1st January 1970.
Normalisation is relative to December in the previous year.
In the following example, the 14th month counting forward from December 2020 is February 2022.
[2021] - [14] - [06] [15] : [00] : [00] normalises to [2022] - [02] - [06] [15] : [00] : [00]
In the following example of a negative month value, the second month counting backward from December 2020 is October 2020
[2021] - [-2] - [06] [15] : [00] : [00] normalises to [2020] - [10] - [06] [15] : [00] : [00]
Normalisation is relative to the last day of the previous month (this can be 28, 29, 30, or 31, depending on the month).
In the following example, the 45th day counting forward from 31 December 2020 is 14 February 2021.
[2021] - [01] - [45] [15] : [00] : [00] normalises to [2021] - [02] - [14] [15] : [00] : [00]
In the following example of a negative day value, the fifth day counting backward from 31 December 2020 is 26 December 2020.
[2021] - [01] - [-5] [15] : [00] : [00] normalises to [2020] - [12] - [26] [15] : [00] : [00]
Normalisation is relative to the preceding midnight.
In the following example, the 34th hour counting forward from the preceding midnight is 10am on 7 January.
[2021] - [01] - [06] [34] : [00] : [00] normalises to [2021] - [01] - [07] [10] : [00] : [00]
In the following example of a negative hour value, the tenth hour counting backward from the preceding midnight is 2pm on 5 January.
[2021] - [01] - [06] [-10] : [00] : [00] normalises to [2021] - [01] - [05] [14] : [00] : [00]
Normalisation is relative to the current hour.
In the following example, the 70th minute counting forward from the current hour of 3pm is 4:10pm.
[2021] - [01] - [06] [15] : [70] : [00] normalises to [2021] - [01] - [06] [16] : [10] : [00]
In the following example of a negative minute value, the tenth minute counting backward from the current hour of 3pm is 2:50pm.
[2021] - [01] - [06] [15] : [-10] : [00] normalises to [2021] - [01] - [06] [14] : [50] : [00]
Normalisation is relative to the current minute.
In the following example, the 70th second counting forward from the current minute of 3:00pm is 3:01:10pm
Example: [2021] - [01] - [06] [15] : [00] : [70] normalises to [2021] - [01] - [06] [15] : [01] : [10]
In the following example of a negative second value, the tenth second counting backward from the current minute of 3:00pm is 2:59:50pm
Example: [2021] - [01] - [06] [15] : [00] : [-10] normalises to [2021] - [01] - [06] [14] : [59] : [50]