community.borland.com

Article #28886: Mathmatical operations on Time and Timestamp data

When you substract 2 dates or timestamp from each other you get a result in days. For example:

select current_timestamp-cast('yesterday' as timestamp) from rdb$database

results in some floating point number greater than 1 because cast('yesterday' as timestamp) is 12:00 midnight yesterday.

When you subtract 2 times from each other you get a result in seconds. For example:

select current_time - cast('0:00' as time) from rdb$database

results in the number of seconds since midnight.

What has been stated so far is pretty easy to understand. So, what if you want to get the difference between 2 timestamp or times in terms of time instead of a number, or you want to subtract a time from a timestamp, or get the average or sum of some times or timestamps? The remainer of this article describes how to do these things.

Get the difference between 2 times as a time.

How: Subtract the times, then add the result to midnight.

Example: This example tells you how much time until quitting time:

select cast('0:00' as time)+(cast('17:00' as time)-current_time) from rdb$database

Get the difference between 2 timestamps as a time.

How: Subtract the timestamps, multiply the result times the number of seconds in a day, then add the result to midnight.

Example: This example tells you how much time until the fireworks on July 4th (assuming today is July 4th):

select cast('0:00' as time)+(cast('7/4/02 21:00' as timestamp)-current_timestamp)*24*60*60 from rdb$database

Get the difference between 2 timestamp as a timestamp.

How: Substract the timestamps, the add the result to what you consider to be the "0" day. (Dec 31, 1999 works well as the 0 zero day.)

Example: Say you started running The West States 100 at 5:00 on June 29 1, 2002 and finished at 8:00, June 30, 2002. The result shows a running time of 1 day and 3 hours after your 0 day (which is Dec 31, 1999 in this case).

select cast('12/31/99' as timestamp) + (cast('6/30/02 8:00' as timestamp)-cast('6/29/02 5:00' as timestamp)) from rdb$database

Subtract or Add a time from/to a timestamp.

How:Subtract the time from midnight, divide the result by the number of seconds in a day, take that result and add or substract it from your timestamp.

Example: This example shows what time it will be 2 hours from now.

select current_timestamp+(cast('2:00' as time)-cast('0:00' as time))/(60*60*24) from rdb$database;

Compute the avg or sum of a time column

How: First create a calculated field which calculates the number of seconds since midnight for a time. Then calculate the avg or sum of this column and add it to midnight.

Example: Let's say you want to compute the average time from the Slug Run III Kids Race. First you create this table: CREATE TABLE Times (place smallint, name varchar(20), age smallint, gender char(1), city varchar(13), duration time, seconds computed by (duration-cast('0:00' as time)))

and you populate it:

insert into times (place, name, age, gender,city, duration) values (1,'CAMERON BUNKER',12,'M','SARATOGA','0:02:59');
insert into times (place, name, age, gender,city, duration) values (2,'MICHAEL YOUNG',10,'M','WATSONVILLE','0:03:00');
...

To calculate the avg time, do:

select cast('0:00' as time) + avg(seconds) from times

Compute the average timestamp

How: First create a calculated field which calculates the number of days since your "zero" date. (For timestamps whose duration is days, 12/31/99 works well for the zero day. If your timestamps were birthdays, then 12/31/1899 might work well.) Then calculate the average of this column and add it to your "zero" date.

Example: Let's say you want to compute the average finish time from the West States 100. First you create the following table:

CREATE TABLE Results (place smallint, first_name varchar(10), last_name varchar(10), bib char(3), gender char(1), age smallint, state char(2), duration timestamp, pace computed by ((duration-cast('12/31/99' as timestamp))*864 + cast('0:00' as time)), days computed by (duration-cast('12/31/99' as timestamp)))

and you populate it:

insert into results (place, first_name, last_name, bib, gender, age, state, duration) values (1, 'Scott', 'Jurek', 'M1', 'M', 28, 'WA', '12/31/99 16:19:10');
insert into results (place, first_name, last_name, bib, gender, age, state, duration) values (2, 'Steve', 'Peterson', '18', 'M', 39, 'CO', '12/31/99 17:28:43');
...
insert into results (place, first_name, last_name, bib, gender, age, state, duration) values (255, 'James', 'Ballard', '85', 'M', 51, 'OR', '1/1/00 5:58:49');

To calculate the avg timestamp do:

select avg(days)+cast('12/31/99' as timestamp) from results;

Last Modified: 19-JUL-02