Menu
I have a table that has (among others) a timestamp column (named timestamp; it's a standard Oracle DATE datatype). The records are about 4-11 minutes apart, about 7 or 8 records every hour, and I'm trying to determine if there is any pattern to them.
Is there an easy way to see each record, and the number of minutes that record occurred after the previous record?
Thanks, AndyDan
576
In the blue text, you can see the calculation of the SQL delta between two rows. To calculate a difference, you need a pair of records; those two records are “the current record” and “the previous year's record”. You obtain this record using the LAG() window function.
If you'd like to calculate the difference between the timestamps in seconds, multiply the decimal difference in days by the number of seconds in a day, which equals 24 * 60 * 60 = 86400 , or the product of the number of hours in a day, the number of minutes in an hour, and the number of seconds in a minute.
To calculate the difference between the arrival and the departure in T-SQL, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument can be microsecond , second , minute , hour , day , week , month , quarter , or year .
SQL Server DIFFERENCE() Function The DIFFERENCE() function compares two SOUNDEX values, and returns an integer. The integer value indicates the match for the two SOUNDEX values, from 0 to 4. 0 indicates weak or no similarity between the SOUNDEX values. 4 indicates strong similarity or identically SOUNDEX values.
This is Oracle 9i+, using the LAG function to get the previous timestamp value without needing to self join:
SELECT t.timestamp - LAG(t.timestamp) OVER (ORDER BY t.timestamp) AS diff
FROM YOUR_TABLE t
...but because whole numbers represent the number of days in the result, a difference of less than 24 hours will be a fraction. Also, the LAG will return NULL if there's no earlier value -- same as if having used an OUTER JOIN.
To see minutes, use the ROUND function:
SELECT ROUND((t.timestamp - LAG(t.timestamp) OVER (ORDER BY t.timestamp)) *1440) AS diff_in_minutes
FROM YOUR_TABLE t
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
