Arithmetic
Perform arithmetic datetime operations:
- add time - Add specified amount of time units.
subtract time - Subtract specified amount of time units.
- time after - Measure time difference after.
time before - Measure time difference before.
The add time and subtract time operations add or subtract the specified amount of time units (day, hour, millisecond, minute, second, or week) to the original date and return a new date value.
The time after and time before operations measure the difference in the specified amount of time units (day, hour, millisecond, minute, second, or week) between the original date and the second date and return a numeric value in time units.
Launch the template by choosing a datetime field and then double-clicking the Arithmetic template. When the template launches we can specify options.
Arithmetic : add time
Add the specified amount of time units to the source date, and save into the specified Result destination.
Appears when the target field is a datetime data type. Given a source field that is a datetime field, adds the number of days specified by the Shift days parameter to create a new date. Positive values for the Shift days parameter add days to create a later date, and negative values subtract days to create an earlier date. Values for Shift days can have decimal fraction parts, to shift by part of a day. For example, a value of 10.5 will shift by ten days and 12 hours (half of a day).
We start with a table with a single field of type datetime, called Calendar. We have used the Layers pane to hide the mfd_id field, for a simpler illustration. Note that the date of the last record is December 25, 2016.
With the focus on the table window, in the Transform pane we choose the Datetimes field and then we double-click the Arithmetic template to launch it.
In the Arithmetic template, we choose add time as the Operation. For Unit we leave the default choice of day.
For the Value we enter 10.
If we prefer, for the Value parameter we can choose any datetime field in the table from the pull down menu, or we could choose Expression to provide a value that is computed from an SQL expression.
For the Result destination, we choose New Field and then enter Calendar added time as the name of the new field to add to the table. We could use whatever name we want, but it is wise to use names that remind us of what they are supposed to be. We leave the Result type at the default datetime.
If we preferred, we could have chosen some other existing numeric field in the table as the Result destination, or we could have chosen Same Field to update the Calendar field "in place."
Press Transform.
The template creates a new datetime type field called Calendar added time and populates it with datetime values exactly ten days after the value in the Calendar field for each record. Dates are automatically shifted into the next month and the next year as required.
We can also use fractional numbers for the time to be added.
We re-run the transform, using 10.6 for the Value. For the Result, from the pull down list in the box we choose the Calendar added time field we created in the prior run.
That will add 10 days and 14.4 hours to the datetimes in the Calendar field, updating the values in the Calendar added time field with the result.
Press Transform.
Dates in the Calendar added time field have been increased by ten days, shifted by the number of days, with times also shifted forward by 14.4 hours as well. The last two records, for example, have been pushed into the early morning of the next day, compared to just shifting dates by ten days.
Arithmetic : subtract time
Subtract the specified amount of time units from the source date, and save into the specified Result destination.
Arithmetic : time after
Measure the time difference after the specified datetime to the source datetime, and save into the specified numeric Result destination using the specified units of time measure. The time difference will be a positive number if the specified datetime is before the source datetime, and it will be negative if the specified datetime comes after the source datetime.
Available units of time measurement are day, hour, millisecond, minute, second, or week. If a unit other than millisecond is used, the time difference will have a decimal fractional part if the times in the two datetime values are not the same.
We start with a table with a single field of type datetime, called Datetimes. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
The last two records in the above view have datetimes for the first day and the last day in the year 2020.
With the focus on the table window, in the Transform pane we choose the Datetimes field and then we double-click the Arithmetic template to launch it.
In the Arithmetic template, we choose time after as the Operation. For Unit we leave the default choice of day.
For the Value we leave the default choice of 1-Jan-2020. The default choice for Value shows the format to use when entering dates. Not specifying any time portion for the datetime, that means we have specified a datetime of January 1, 2020, at 0:00:00. We could have entered a different date if we wanted.
If we prefer, for the Value parameter we can choose any datetime field in the table from the pull down menu, or we could choose Expression to provide a value that is computed from an SQL expression.
For the Result destination, we choose New Field and then enter Time after as the name of the new field to add to the table. We could use whatever name we want, but it is wise to use names that remind us of what they are supposed to be. We leave the Result type at the default float64. The result will be the number of days after the source datetime to the given 1-Jan-2020 datetime, so the use of float64 will allow fractional days to account for different time parts of the source date and the Value date.
If we preferred, we could have chosen some other existing numeric field in the table as the Result destination.
Press Transform.
The template creates a new float64 type field called Time after and populates it with the number of days after the specified datetime, January 1, 2020, at 0:00:00, until the datetime value in the Datetimes field.
All records that have a Datetimes date and time before January 1, 2020, at 0:00:00 have a negative number in the Time after field, giving the number of days between those prior datetimes and January 1, 2020, at 0:00:00.
All records that have a Datetimes date and time after January 1, 2020, at 0:00:00 have a positive number in the Time after field, giving the number of days between January 1, 2020, at 0:00:00 and those subsequent datetimes.
For example, the next to last record in the view above has a Datetimes value of 1/1/2020 0:25:13. That is 25 minutes and 13 seconds after the specified date of 1/1/2020 0:00:00 (writing our specified date in the same style), so the Time after value is zero days and fraction of a day: 0.017511574071249925.
The last record in the view above has a Datetimes value of 12/31/2020 6:53:37. That is 365 days, six hours, 53 minutes and 37 seconds after the specified date of 1/1/2020 0:00:00, so the Time after value is 365 days and a fraction of a day, 365.2872337962981.
Looking at the other records, we can see that September 2, 2018 is a date about 485 days before January 1, 2020, and so on.
If we want to compare to date value with a specific time, we can do that as well.
We re-run the transform, using an extended format for the date time, entering a value of 4-Jan 2020 5:45:41, using the same time as the seventh record in the table in the Datetimes column, which is 1/4/2017 5:45:41.
For the Result destination, in the pull down menu we choose the Time after field created previously.
Press Transform.
We have selected the record in the illustration above to highlight the result. The result saved into the Time after field is exactly -1095 days, with no fractional part, since the time parts for the datetime values in the Datetimes field and in the Value box are identical.
We can use date calculations to find time spans of interest. For example, how many days did legendary rock guitarist Eddie Van Halen live?
We can add a date of 1/26/1955 0:00:00 to our table, Van Halen's date of birth, and then run the time after operation using the date of his passing as the Value.
We re-run the transform using 6-Oct-2020 as the Value, saving the Result to the Time after field created before.
Press Transform.
The Time after field updates, and we see that Eddie Van Halen lived 23995 days, rocking and rolling every day. Try this with your birthday and today's date and see how many days, hours, or minutes you have lived.
Arithmetic : time before
Measure the time difference before the specified date to the source date, and save into the specified numeric Result destination using the specified units of time measure.
The time before operation is like the time after, except that positive and negative values in the result are reversed.