How to Extract Timestamp Differences in Hours and Calculate Cumulative Sum in SQL

Learn how to calculate the timestamp differences in hours and perform cumulative sums using SQL. This guide offers a step-by-step approach to help you understand the process, particularly in Oracle SQL.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to extract timestamp differences in hours and do cumulative sum

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Extract Timestamp Differences in Hours and Calculate Cumulative Sum in SQL

When working with timestamps in SQL, you might encounter the need to calculate the differences between timestamps in hours and also compute cumulative sums for repeated entries. This is especially relevant in scenarios where you deal with task completion times, deadlines, and approvals. In this post, we’ll take a look at how you can achieve that using Oracle SQL.

The Problem

You have a table that tracks events, each with timestamps for when they were created and when actions were taken. Your goal is to achieve the following:

Calculate the aging: For records with a status of created, you want to compute the difference between the created_on timestamp and the act_taken_on timestamp, displaying the result only in hours. If an id appears more than once, the aging column should show the cumulative sum of hours.

Calculate the time_left: This should represent the difference between the due_dt timestamp and the current system time (sysdate), also expressed in hours.

The Solution

Let's break down the solution into clear sections.

Sample Data Setup

To illustrate how to approach this, we will create a sample data table named test:

[[See Video to Reveal this Text or Code Snippet]]

Query for Aging and Time Left

To retrieve the desired output, you can use the following SQL query. This query calculates the aging and time_left based on the provided conditions:

[[See Video to Reveal this Text or Code Snippet]]

Understanding the Query

Casting and Rounding: We cast the TIMESTAMP as DATE to perform the calculations and multiply the results by 24 to convert the time difference from days to hours. The ROUND function helps to present the values without decimals.

CASE Statement: The CASE statement checks the status to appropriately compute time differences. This ensures that only timestamps with the status of created or Approved are processed for calculation.

Cumulative Sum: The SUM function combined with the OVER clause enables us to compute running totals grouped by id and ordered by created_on.

Expected Results

After executing the SQL query, the expected results would look like this:

[[See Video to Reveal this Text or Code Snippet]]

Here, we see that for the first entry of id 1, the aging is calculated as 24 hours. The next entry of id 1 accumulates the previous value to show 48 hours. The time_left shows the difference in hours before the due date.

Conclusion

With these steps, you can successfully extract timestamp differences in hours and compute cumulative sums using SQL, especially in Oracle environments. It’s a powerful technique for managing timelines and assessing operational efficiencies effectively. Happy querying!