Message Posted: Wed, 26 Mar 2003 @ 17:33:34 GMT

 < Last>>

 Subj: Re: Timestamp difference From: Geoffrey Rommel

 For example tblXYZ is the table, BEGIN_TIME is the smaller timestamp and END_TIME is the larger timestamp, the following gave me inaccurate results.

 SELECT minute(END_TIME - BEGIN_TIME) FROM tblXYZ;

 I want the total difference in minutes sometimes and total difference in seconds sometimes.

 The issue is even if I do that case, the answer is not right.

If I understand your requirements, you want to compute the difference in seconds (or minutes) between two timestamps. Try this...

First, subtract them. The difference will be an INTERVAL, not a number.

```select ts01 - ts04 day(4) to second
from dttest;

(ts01 - ts04) DAY TO SECOND
---------------------------
2 07:11:24.000000
```

Now, you need to extract the day portion. This will be an integer.

```select extract(day from (ts01 - ts04 day(4) to second))
from dttest;

EXTRACT(DAY FROM (ts01 - ts04) DAY TO SECOND)
---------------------------------------------
2
```

That integer times 86400 gives you the number of seconds.

```select extract(day from (ts01 - ts04 day(4) to second)) * 86400
from dttest;

(EXTRACT(DAY FROM (ts01 - ts04) DAY TO SECOND)*86400)
-----------------------------------------------------
172800
```

Now do likewise for hours, minutes, and seconds, and you have the total difference in seconds.

```select (extract(day from (ts01 - ts04 day(4) to second)) * 86400)
+ (extract(hour from (ts01 - ts04 day(4) to second)) * 3600)
+ (extract(minute from (ts01 - ts04 day(4) to second)) * 60)
+ extract(second from (ts01 - ts04 day(4) to second))
from dttest;

((((EXTRACT(DAY FROM (ts01 - ts04) DAY TO SECOND)*86400)+(EX
------------------------------------------------------------
198684.000000
```

The same approach, mutatis mutandis, will give you the difference in minutes.

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2003 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback