Back to articles list November 27, 2017 - 5 minutes read How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic Ignacio L. Bisso Ignacio is a database consultant from Buenos Aires, Argentina. He’s worked for 15 years as a database consultant for IT companies like Informix and IBM. These days, he teaches databases at Sarmiento University and works as a PostgreSQL independent SQL consultant. A proud father of four kids with 54 years in his backpack, Ignacio plays soccer every Saturday afternoon, enjoying every match as if it’s his last one. Tags: AVG date arithmetic SQL basics sql timestamps Computing Tardiness: Date, Time, and Interval SQL Arithmetic In this article, we’re going to discuss some interesting operations we can perform with date-related data types in SQL. The SQL standard, which most relational databases comply with these days, specifies the date-related data types that must be present in relational databases. The most important of such data types are date, time, timestamp, and interval. Here’s a brief rundown of the differences between these data types: date: represents a single day (e.g., 18/11/2017) time: represents time with hours, minutes, and floating-point seconds (e.g., 10:44:30.2) timestamp: represents a point in time, with complete information of date and time (e.g., 2017-11-18 10:44:30.2) interval: represents a time interval which can have different levels of precision (e.g., 20:10:30, which translates to 20 hours, 10 minutes, 30 seconds). Logging Employee Hours To check how often our employees are running late for work, we’ll use these data types with an example of a firehouse to log the start and end times of firefighters. The table stores scheduled (expected) and actual start/end times for employees. Examining the data in this table, you can tell that some firefighters arrived on time while others did not. The SQL standard also specifies that certain SQL arithmetic operations between date-related values must return specific data types. In the next few sections, we’ll perform some date-time SQL arithmetic operations to determine which firefighters arrived late to work. Operations Between Numerical and Date Values We’ll begin with the simple operation of adding an integer to a date. When we add an integer “N” to a date, we obtain another date that is “N” days in the future or past, depending on whether the integer is positive or negative, respectively. With this in mind, let’s write a query that narrows our focus to all firefighters who worked over the past five days: SELECT * FROM firestation_working_time WHERE SchedStartTime > '2017-11-10' - 5 We can perform the same operation using an interval instead of an integer. Moreover, using the interval approach, we can add/subtract not only the number of days but also the number of months, years, or a combination of all three from a particular date. Let’s see how we can obtain the firefighters who worked over the past 72 hours. Take a look at the following query: SELECT * FROM firestation_working_time WHERE "SchedStartTime" > '2017-11-08 08:00:00' - interval '72 hours' One difference between the two queries we’ve written so far is in the data types they return. While the first query returns a date, the second query returns a timestamp. When we add a date to an interval, the resulting data type must be a timestamp. Internally, SQL treats the date as a timestamp by simply appending to it the time “00:00:00”. Before we move on to other SQL arithmetic operations, I’d like to challenge you with a question: What would happen if we were to add 10 minutes to a date? I encourage you to write the appropriate SQL query to accomplish this task. Use the previous example as a model. Who’s Running Late for Work: Subtracting Timestamps Let’s now try another SQL arithmetic operation. What happens if we subtract one date from another date or a timestamp from another timestamp? In both cases, the result will be a SQL time interval, representing the elapsed time between the two dates or timestamps. Let’s see an example of a SQL time interval in action using the same table. Suppose we’d like to obtain data for the firefighters who arrived late on the date 2017-11-02. We can execute the following query: SELECT "FiremanFullName", "RealStartTime" - "SchedStartTime" "Starting Delay" FROM firestation_working_time WHERE "RealStartTime"::date = '2017-11-02' AND "RealStartTime" - "SchedStartTime" > interval '0 seconds' Average Tardiness: Aggregation With SQL Time Interval We can also use aggregate functions such as AVG or SUM with SQL arithmetic operations. Suppose our boss reviews the information for all firefighters who were late on the date 2017-11-02 and asks that we send him a complete report with the average and total accumulative tardiness of these employees. The following query will return this report: SELECT "FiremanFullName", AVG("RealStartTime"-"SchedStartTime")"AverageDelay", SUM("RealStartTime"-"SchedStartTime") "Accum. Delay" FROM firestation_working_time WHERE MONTH("RealStartTime"::date) = 11 AND YEAR("RealStartTime"::date) = 2017 GROUP BY "FiremanFullName" We can also execute a query to obtain the firefighters who logged excess hours. We’ll calculate the amount of excess hours they worked and organize the results in descending order. SELECT "FiremanFullName", SUM("RealEndTime"-"SchedEndTime") "Extra Work" FROM firestation_working_time WHERE MONTH("RealStartTime"::date) = 11 AND YEAR("RealStartTime"::date) = 2017 GROUP BY "FiremanFullName" ORDER BY "Extra Work" DESC In the previous query, notice that we’re adding intervals. The excess time a firefighter logged on a specific day is represented as a SQL time interval. The SUM function is then used to add these overtime intervals to obtain the firefighters’ cumulative excess hours for the month of November, 2017. It should be clear that the result of adding two or more intervals is another interval. Naturally, you may be wondering: can we add all kinds of data types to each other? What if we try to add two dates or two timestamps? Simply put, we can’t perform these operations because they do not produce meaningful results. However, recall that subtracting one date from another is perfectly fine, since doing so merely produces a time interval. Conclusion As we learned, there are several SQL arithmetic operations we can perform with date, timestamp, and interval data types. Moreover, the results of such operations can have different data types than those of their operands. We didn’t cover all SQL operations between date-related data types in this article, such as multiplying an interval by an integer. If you’re interested in learning more about these topics, go ahead and check out the courses we offer at LearnSQL.com. Tags: AVG date arithmetic SQL basics sql timestamps You may also like How to Group Data by Week in SQL Server SQL Server can group data by week. But what does that mean exactly? Is Monday or Sunday the first day of the week? How are the weeks numbered? Read more How to Get the First Day of the Week in SQL Server How to display the first day of the week in SQL Server using Sunday as the start of the week, Monday as the start of the week, or the DATEFIRST field Read more Performing Calculations on Date- and Time-Related Values Relational databases support several date and time data types. In this article, we'll look at several arithmetic operations we can do on these types. These operations are logical and understandable, even for the beginning SQL coder. Read more High-Performance Statistical Queries: Dependencies Between Discrete Variables Contingency tables and the chi-squared test—which method is better to test dependencies that do not rely on numeric values? Read more Introduction to Reporting with SQL — the Ultimate Tutorial for Business Professionals The right business decision-making is not based on intuition but data analysis. Learn how to perform simple SQL queries for data reporting. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.