I have a table that shows a list of connections showing the time the connection was finished and the duration. I wish to show concurrent connections during a particular minute. for instance the following would show that there was two connections during 2003-12-04 09:27:00 ----------------------------------------- call_time | duration ----------------------------------------- 2003-12-04 09:27:00 | 00:01:21 ----------------------------------------- 2003-12-04 09:28:00 | 00:04:19 -----------------------------------------
I just checked in changes to the date/time functions to fix a bug that this question brought to light, and to add some new capability.
If you give one of the date-time functions just a time with no date, they are suppose to fill in a date of 2000-01-01. For example:
SELECT datetime('00:01:21');
2000-01-01 00:01:21This was working for julianday() but not for datetime(). It has now been fixed.
I also added the ability to put a time value in as the modifier and shift the date by that amount. For example:
SELECT datetime('2003-12-04 09:27:00', '00:01:21');
2003-12-04 09:28:21The time modifier can be negative. So to shift a date/time backwards by 2 hours and 45 minutes, you could say this:
SELECT datetime('2003-12-04 09:27:00', '-02:45');
2003-12-04 06:42:00In situations like the above, the new capability can be used to compute the ending time of a call as follows.
SELECT datetime(call_time, duration);
But I don't think the original post needs any of the above. These were just deficiencies I noticed in the date/time functions as I looked at the question. The original poster just wanted to know the number of seconds in a call, and that can be computed as follows:
SELECT (julianday(duration) - julianday('2000-01-01'))*86400
Note that you are subtracting two number that are very close to one another - an operation that introduces a lot of error. So the result will be off by a few microseconds. You can use the round() function to round it off to the nearest second which should then be exact. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
