datetime question

I have a table that has these columns (among others) start_time datetime year to second end time datetime year to second I've always wanted to just select number of elapsed seconds in straight sql but have never figured it out. I thought a cast might work, as in: ( extend(complete_time,year to fraction) - extend(start_time,year to fraction) )::interval minute(5) to second(2) but that throws a syntax error. When you do it without the cast, you get ugly stuff like 0 00:00:16.000 (for 16 seconds). Is there no way to just get total seconds without C++, Perl, etc ?

Bill64bits wrote: > I have a table that has these columns (among others) > start_time datetime year to second > end time datetime year to second > > I've always wanted to just select number of elapsed seconds in straight > sql but have never figured it out. > I thought a cast might work, as in: > > ( extend(complete_time,year to fraction) - > extend(start_time,year to fraction) )::interval minute(5) to second(2) > > but that throws a syntax error. > > When you do it without the cast, you get ugly stuff like 0 > 00:00:16.000 (for 16 seconds). > > Is there no way to just get total seconds without C++, Perl, etc ? SELECT (complete_time - start_time):: interval second(8) to second .... Art S. Kagel

Bill64bits wrote: > I have a table that has these columns (among others) > start_time datetime year to second > end time datetime year to second > > I've always wanted to just select number of elapsed seconds in straight > sql but have never figured it out. > I thought a cast might work, as in: > > ( extend(complete_time,year to fraction) - > extend(start_time,year to fraction) )::interval minute(5) to second(2) > > but that throws a syntax error. > > When you do it without the cast, you get ugly stuff like 0 > 00:00:16.000 (for 16 seconds). > > Is there no way to just get total seconds without C++, Perl, etc ? SELECT (complete_time - start_time):: interval second(8) to second .... Art S. Kagel

Thanks Art. You nailed it. Very odd, though that WinSql does not display it (even after chainging 'parameter string' to @ from '::' . Works find in Dbaccess, though. ----- Original Message ----- From: Art S. Kagel Newsgroups: comp.databases.informix To: informix-list@iiug.org Cc: informix-list@iiug.org Sent: Thursday, January 18, 2007 2:14 PM Subject: Re: datetime question Bill64bits wrote: > I have a table that has these columns (among others) > start_time datetime year to second > end time datetime year to second > > I've always wanted to just select number of elapsed seconds in straight > sql but have never figured it out. > I thought a cast might work, as in: > > ( extend(complete_time,year to fraction) - > extend(start_time,year to fraction) )::interval minute(5) to second(2) > > but that throws a syntax error. > > When you do it without the cast, you get ugly stuff like 0 > 00:00:16.000 (for 16 seconds). > > Is there no way to just get total seconds without C++, Perl, etc ? SELECT (complete_time - start_time):: interval second(8) to second .... Art S. Kagel _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list