This might sound a bit inconvenient, but is intended to show the technical background in greater detail. However, the actual conversion of serial date back to date format is very simple. Here you have the ACL™ functions STOD(), STOT() and STODT(). There is little sense in converting the difference between the two timestamps back into a date, but in this way the number of hours, minutes and seconds is very easy to determine. Starting from c_difference (c_timestamp_item - c_timestamp_header) we can first extract the number of days (value before the point) by using some functions, and then put the value after the point into the time format by the STOT() function. The new field with the days (c_duration_days) and the converted field with the duration (c_duration_time) indicate the duration between the two timestamps. (Note: The c_duration_time field is no longer a time as you would normally read it but a duration.)
If you need hours, minutes and seconds as separate fields however, they can be separated by the functions HOUR(), MINUTE() and SECOND(). The result is then easier to follow, and the fields can be taken singly for further analysis. Timestamps are an important element in data analysis and are used in very different applications. The next time you work with the DATETIME data type, remember the many functions that ACL™ offers you for this. And if you determine duration or have to calculate with date and time figures, think of converting into serial dates.
For any comments on this article, feel free to write us at firstname.lastname@example.org.