Anton Grening
Author: Anton Grening

Time­stamp func­tions in ACL™

In this blog post everything is about fields for date and time entries, and the DATETIME data type intended for this purpose. The latter has been in the functionality of ACL™ since version 10.0, offering numerous advantages that the DATE data type cannot give you in earlier versions. The new data type is specially intended for working with timestamps, and saves both date and time in a uniform format. This way of collecting points in time often plays an important role in data analytics because as a rule these are registered automatically, and thus show very accurately when something happened. In our numerous analyses we consider such timestamps, for example, when analyzing weblogs or postings outside normal business hours. These values are written by the system, for which reason they cannot be altered or manipulated without further ado.

Nevertheless, such entries are often saved as text fields, which makes it somewhat difficult to continue working with these values. If you want to format date and time or a combination of the two in one field as what is called a timestamp so that date and time calculations or functions can be applied, this is easy to implement in ACL™ by the new function:


Converts characters or numerics into date format


Return value*


31 Dec 2014
CTODT(string/number)CTODT("20141231 235959")
31 Dec 2014 23:59:59

*The display format of the return value can always be adapted in the ACL™ options. In this example the following format DD MMM YYYY hh:mm:ss was used.


Details can be found in the ACL™ documentation, with application examples for a close look at functionality and different formats.
Once you have date and time information in the required data type (DATETIME), a number of different questions can be answered. As already indicated, we use timestamps to determine postings outside normal business hours, for example. That can be implemented by simple logic queries with the information in DATETIME format, because the field is seen as a calendar date or time by the data type, and queries are enabled that are not possible with a text field. Thus, with the fast filter it is even possible to select entries before or after a particular point in time.

Figure 1: Filter options for timestamps

ACL™ also offers a large variety of functions that can be used on timestamp fields. There are too many to go into all of them here, but I can assure you that there is a matching function for virtually every question or problem.

But what is it like when you want to carry out calculations with these fields? What is the difference between two timestamps, and how do you interpret the result? Questions like these appear when you need time spans between two timestamps. For example you want to calculate how much time elapses after creating a sales order until the particular items are entered. There may be various reasons for this, like searching for unusual transactions or optimizing processes.

Usually a sales order is administered in the system in two tables: a header table for general information about the order, and a further table for the particular item data. To enable us to analyze the date and time figures from both tables (timestamp of creating the order and timestamp of entering the item), we must first put them into one table by a relation. How you proceed is explained by another example in this blog post.
Once you have the two timestamps on a common level, you can determine the difference between the two. The result is a serial value that is used by computer programs for operations with date figures. Each day has an integer assigned that shows how many days have elapsed starting from the base date 1 January 1900. (In ACL™ for example the serial date with the value 1 corresponds to 2 January 1990, and that with the value 42004 to 1 January 2015.)
The time is calculated similarly, using the places after the point of the serial date. A time is seen as an incomplete day and stated as a fraction. (12:00:00 h corresponds to 12 elapsed hours, i.e. half a day, and is stated as a serial value by 0.5; 01:00:00 h corresponds to one hour and 0.04167 as a fraction, etc.)
Once you understand how a serial date is composed and how it can be interpreted, it is quite easy to continue working with the result, e.g. from a subtraction. For this purpose we create a new calculated field (c_difference) and subtract the timestamp of the item data from the header data timestamp. The value before the point is the number of days between the two timestamps, and the value after the point the time as a fraction in relation (1 day = 24 hours).

Figure 2: Difference between two timestamps and conversion into days, hours, minutes and seconds

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 info@dab-gmbh.de.

Comments (0)
Be the first who comments this blog entry.
Blog login

You are not logged in. Please log in to comment this blog entry.

go to Login