In this article, we will cover some interesting operations involving date and time data values in SQL. The SQL standard specifies the following date and time-related data types that must be present in relational databases: date, time, timestamp and interval.
– date: Represents a single day. Example 15/12/2018
– time: Represents the time with hour, minutes, seconds. Example 09:14:30
– timestamp: Represents a point in the timeline. Example 2018-12-15 09:14:30
– interval: Represents an elapsed time. Can have different levels of precision. Example
132:20:40 (132 hours, 20 minutes, 40 seconds)
REGISTERING WORK TIMES
We will exercise the use of these data types, with the following example table used by a company to register the starting time and end time of each employee. The table stores scheduled times and real times, and as we can see some employees are working extra time, while others are usually late.
The SQL standard specifies more than data types. For example, the SQL standard specifies that certain arithmetic operations between date related data types must return some specific data type. In the next paragraphs, we will execute some date time arithmetic operations to detect who are working extra time, who are usually starting late.
IT IS POSSIBLE TO ADD DATE + INTEGER?
We will start with a simple operation of adding an integer number to a date data type. When we add a positive integer N to a date, we will obtain another date N days in the future (or in the past if we subtract instead of adding). Let’s do a query to obtain all employees who worked in the previous 5 days (assuming today is 2018 April 10).
We can do a similar operation using an interval value instead of an integer. Moreover, using the interval approach we can add different units of time, not only days. We can add a number of months, a number of years or a mix. Let’s see how we can obtain the employees who worked in the last 96 hours previous to 2018, April 10.
An important difference between the first and the previous example is the resulting data type in the where expression. In the first example, the resulting data type is DATE, while in the previous example the resulting data type is TIMESTAMP. The reason is: Every time we add an interval to a date the result must be a timestamp. Moreover, before adding the interval value the date value is cast to timestamp by adding the hour ’00:00:00′ to the original date.
WHAT ARE BETWEEN TWO DATES?
Let’s try another kind of arithmetic operation. What if we subtract one date from another date? Moreover, what if we subtract a timestamp from another timestamp?. In both cases, the result will be an interval, representing the elapsed time between two dates or timestamps. Let’s see an example using our sample table:
Let’s suppose we want to obtain the employees who started with a delay of 2 minutes or more on day 02/04/2018. We can execute the following SELECT:
AGGREGATION WITH INTERVAL DATA TYPES
We can also use aggregation functions with arithmetic operations like max, min or average. Let’s suppose now that after seeing all the employees starting late on one specific day, our boss asked for a complete report with the average delay and the total accumulative delay incurred during April for all the employees. Let’s do it with the following SQL query.
With similar operations, we can also execute a query to obtain the employee who did the maximum extra work, in this case, we need to calculate the amount of extra work, and show the results ordered by this metric.
In the previous example, we are also adding intervals. The extra work amount for a specific day is an interval, then the SUM function is being used to add intervals. It is clear that the result of adding two or more intervals is another interval.
We showed in this article several arithmetic operations that we can execute with date, timestamp or interval values. We also showed that the result of an operation, can be of another data type different to the operands: This is a really interesting fact. We didn’t cover all operations between date related data types, for example, we didn’t cover the multiplication of an interval with an integer.