×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to implement datetime difference

How to implement datetime difference

How to implement datetime difference

(OP)
Hello,

I have a fact table which has a column called TimeDiff and will contain the time difference b/w the two adjacent rows in another column called TimeCol. Assume TimeCol has the following entries:

TimeCol
2002-09-05 14:12:00
2002-09-05 14:12:05
2002-09-05 14:12:09
2002-09-05 14:12:45

Then the column TimeDiff will be:

TimeDiff
0
5
4
36

Please can someone give me an idea on how to implement it. The TimeCol has upto 1200 rows.

Thanks,

Jony

RE: How to implement datetime difference

You start somewhere like this in your ETL

SELECT qryDiff.TimeCol, Min(qryDiff.Diff) AS MinOfDiff
FROM (
SELECT Time.TimeCol, (Time.TimeCol - Time_1.TimeCol) AS Diff
FROM [Time], [Time] AS Time_1
WHERE Time.TimeCol - Time_1.TimeCol > 0
) AS qryDiff
GROUP BY qryDiff.TimeCol;

You can then update back the time differences.  You will get a problem where you have multiple events with identical times.  You need to be sure this isn't happening before applying the solution.

C

RE: How to implement datetime difference

This is dependend on the environment you will build this in. Oracle has analytics extensions to SQL which allow you to access the next row from a resultset. With this, calculating a time-difference gets easy.

I do not know if SQL-server, sybase or DB2 or ... has a similar feature.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close