# Thread: Date math in SQL

1. Registered User
Join Date
Dec 2009
Posts
3

## Unanswered: Date math in SQL

I am running a SQL that returns the interval between two datetimes. How do I control the precision of the returned value? I would like the interval to be returned as seconds(3). I have looked through the manual and can not find this answer.

select (dt1 - dt2) as elapsed_tm from table;

2. Registered User
Join Date
Apr 2007
Location
Jundiai / SP - Brasil
Posts
311
Code:
```Database selected.

create temp table a (dt1 datetime year to second, dt2 datetime year to second, num interval year to month);
Temporary table created.

insert into a values (current , '2009-10-12 15:23:11',null);
1 row(s) inserted.

insert into a values (current , '2002-10-12 15:23:11',null);
1 row(s) inserted.

select (dt1 - dt2)
, cast(dt1 - dt2 as interval second(9) to second  )
, (dt1 - dt2 ):: interval second(9) to second
from a ;

(expression)       (expression) (expression)

50 01:53:49    4326829      4326829
2607 01:53:49  225251629    225251629

2 row(s) retrieved.

Database closed.```
Last edited by ceinma; 12-01-09 at 15:19. Reason: add better example...

3. Registered User
Join Date
Dec 2009
Posts
3
@ceinma

Thanks for your solution. It identified that I left out one important piece of information. I am still using Informix v7.31.

4. Registered User
Join Date
Apr 2007
Location
Jundiai / SP - Brasil
Posts
311

## there is...

There several ways.. all tested in IDS 7.31 (I have a production with 7.31 too)

Pick up any of the three, but I recommend the procedure intrvl_to_sec3.
Code:
```Database selected.

create temp table a (dt1 datetime year to second, dt2 datetime year to second, num interval year to month);
Temporary table created.

insert into a values (current , '2009-10-12 15:23:11',null);
1 row(s) inserted.

insert into a values (current , '2009-06-12 15:23:11',null);
1 row(s) inserted.

insert into a values (current , '2009-12-02 15:23:11',null);
1 row(s) inserted.

----------------------
-- The nice way...
----------------------
CREATE PROCEDURE intrvl_to_sec( p INTERVAL DAY(9) TO SECOND)
RETURNING interval second(9) to second;;
DEFINE vStrParam varchar(20);;
DEFINE vDAY integer;;
DEFINE vhour integer;;
DEFINE vmin integer;;
DEFINE vsec integer;;
DEFINE vres interval second(9) to second;;
--set debug file to '/tmp/x.out' ;
--trace on;
LET vStrParam=p;;
LET vStrParam=TRIM(vStrParam);;
LET vDAY=substr(vStrParam,1,2);;
LET vhour=substr(vStrParam,-8,2);;
LET vmin=substr(vStrParam,-5,2);;
LET vsec=substr(vStrParam,-2,2);;
LET vres=vsec units second + vmin units minute + vhour units hour + vday units day ;;
RETURN vres ;;
END PROCEDURE ;
Routine created.
;

----------------------
-- The ugly way...
----------------------
CREATE PROCEDURE intrvl_to_sec2( p INTERVAL DAY(9) TO second)
RETURNING INTEGER;;
DEFINE vStrParam varchar(20);;
DEFINE vDAY integer;;
DEFINE vhour integer;;
DEFINE vmin integer;;
DEFINE vsec integer;;
DEFINE vres INTEGER ;;
--set debug file to '/tmp/x.out' ;
--trace on;
LET vStrParam=p;;
LET vStrParam=TRIM(vStrParam);;
LET vDAY=substr(vStrParam,1,2);;
LET vhour=substr(vStrParam,-8,2);;
LET vmin=substr(vStrParam,-5,2);;
LET vsec=substr(vStrParam,-2,2);;
LET vres=vsec ;;
LET vres=vres + (vmin * 60) ;;
LET vres=vres + (vhour *60*60);;
LET vres=vres + (vday *24*60*60);;
RETURN vres ;;
END PROCEDURE ;
Routine created.

;

----------------------
-- The magic way...
----------------------
CREATE PROCEDURE intrvl_to_sec3( p INTERVAL second(9) TO second)
RETURNING interval second(9) to second;;
RETURN p ;;
END PROCEDURE ;
Routine created.
;

select first 1
intrvl_to_sec(interval(100 00:00:00) day(4) to second) as day
,intrvl_to_sec(interval(1 00:00:00) day to second) as day
,intrvl_to_sec(interval(0 01:00:00) day to second) as hour
,intrvl_to_sec(interval(0 00:01:00) day to second) as minute
,intrvl_to_sec(interval(0 00:00:10) day to second) as second
from a ;
day        day        hour       minute     second
864000      86400       3600         60         10
1 row(s) retrieved.

select (dt1 - dt2) ,intrvl_to_sec(dt1 - dt2) from a ;
(expression)       (expression)
51 02:19:06    4414746
173 02:19:06    1477146
0 02:19:06       8346
3 row(s) retrieved.

select first 1
intrvl_to_sec2(interval(100 00:00:00) day(4) to second) as day
,intrvl_to_sec2(interval(1 00:00:00) day to second) as day
,intrvl_to_sec2(interval(0 01:00:00) day to second) as hour
,intrvl_to_sec2(interval(0 00:01:00) day to second) as minute
,intrvl_to_sec2(interval(0 00:00:10) day to second) as second
from a ;

day         day        hour      minute      second
864000       86400        3600          60          10
1 row(s) retrieved.

select (dt1 - dt2) ,intrvl_to_sec2(dt1 - dt2) from a ;
(expression)       (expression)
51 02:19:06      4414746
173 02:19:06      1477146
0 02:19:06         8346
3 row(s) retrieved.

select first 1
intrvl_to_sec3(interval(100 00:00:00) day(4) to second) as day
,intrvl_to_sec3(interval(1 00:00:00) day to second) as day
,intrvl_to_sec3(interval(0 01:00:00) day to second) as hour
,intrvl_to_sec3(interval(0 00:01:00) day to second) as minute
,intrvl_to_sec3(interval(0 00:00:10) day to second) as second
from a ;
day        day        hour       minute     second
8640000      86400       3600         60         10
1 row(s) retrieved.

select (dt1 - dt2) ,intrvl_to_sec3(dt1 - dt2) from a ;
(expression)       (expression)
51 02:19:06    4414746
173 02:19:06   14955546
0 02:19:06       8346
3 row(s) retrieved.

Database closed.```

5. Registered User
Join Date
Dec 2009
Posts
3
@ceinma

Thanks for all your hard work and help!

The "magic way" is definitely the direction I'll go. I suspect that it will be faster too.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•