RE: [sqlite] time calculations
> -Original Message- > From: Griggs, Donald [mailto:[EMAIL PROTECTED] > Sent: Monday, March 01, 2004 11:14 PM > > > I would think a general database program would do as sqlite > does -- call the > carefully written, standard posix routines (or their PC or > MAC analogues), > and massage the data with some "generally recognized as safe" > algorithms. > If you're doing something really unusual, you can incorporate more > sophisticated time schemes in the application or incorporate into a > specialized sqlite source. That sounds quite reasonable to me. =) -Ken - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] time calculations
(Shoot! My previous email was another sent before it was quite ready. My apologies to all.) -regarding specialized needs for date/time calculations: An alternative (but way overkill for sqlite, right?) is to use specialized software to keep atomic time (TAI), or it's cousin, the GPS epoch time, neither of which take heed of leap seconds. To calculate very precise times in the past, then convert to ordinary time, the software needs to contain a list of past leap seconds, and of course to have that list updated as future leap seconds are announced. I would think a general database program would do as sqlite does -- call the carefully written, standard posix routines (or their PC or MAC analogues), and massage the data with some "generally recognized as safe" algorithms. If you're doing something really unusual, you can incorporate more sophisticated time schemes in the application or incorporate into a specialized sqlite source. D. J. Bernstein disliked the unix conventions, and wrote some free software to keep TAI time: http://cr.yp.to/proto/utctai.html http://cr.yp.to/libtai.html And here's a tcl script for dealing in GPS time: http://ldas-cit.ligo.caltech.edu/doc/tcl_docs/html/gpstimetcl.html#leapSecs Unix epoch http://en.wikipedia.org/wiki/Unix_epoch Yet more systems of time: http://tycho.usno.navy.mil/systime.html Interesting note from Naval Observatory: http://tycho.usno.navy.mil/leapsec.html As of 1 January 1999 [and should still be true until the next leapsecond] TAI is ahead of UTC by 32 seconds. TAI is ahead of GPS by 19 seconds. GPS is ahead of UTC by 13 seconds. -- BTW, there will definitely NOT be a leap second this June. Leap seconds *can* be negative as well as positive, but we've never had a negative one, and won't likely see one (as the moon keeps slowing us down with tidal friction). [Writing only for myself, and not on behalf of my company.] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] time calculations
Hi Ken, Regarding: " But the events can take place in any setting whatsoever. For instance, if the events represent some sort of timing for stock trades, or ..., then the database host's internal clock has no relevance." You're absolutely right. Regarding: "(Here I'm assuming that SQLite doesn't do leap seconds,). to implement the date/time functions in a really correct way..." Actually, POSIX standard defines UNIX time as specifically excluding leap seconds. At the end of June or December, a proper Unix system will "relive" the final second of the UTC day -- which can make for some funky time warps for a few applications during those moments -- but for most systems, things are made simpler -- one just pretends that the extra second never happened. An alternative is to use specialized software to keep atomic time, TAI, or it's cousin, the GPS epoch time, neither of which take heed of leap seconds. To calculate very precise times in the past, then convert to ordinary time, the software needs to have the http://en.wikipedia.org/wiki/Unix_epoch - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] time calculations
Hi Donald, > From: Griggs, Donald [mailto:[EMAIL PROTECTED] > Sent: Monday, March 01, 2004 10:46 AM > >I suppose that for a system to really care about a leap > second jump, it > would have to be at least reasonably synched to the NIST clocks as a > precondition -- otherwise the normal computer clock drift > would mean that the clock is off by multiple seconds routinely. Not necessarily - you're assuming that the events stored in the database correspond to events that took place on that computer, or at least that use that computer's clock as a frame of reference. But the events can take place in any setting whatsoever. For instance, if the events represent some sort of timing for stock trades, or ..., then the database host's internal clock has no relevance. Imagine a (contrived) situation where you start with a datetime like "June 1, 1972 at 12:00:00" represented as a Unix epoch time (which should track leap seconds correctly), then repeatedly add 60 seconds to it. When you pass the leap second at the end of 1972, you'll eventually get to "January 5, 1973 at 11:59:59". By contrast, if you start with the same datetime in SQLite and perform the same operation, you'll eventually get to "January 5, 1973 at 12:00:00", and the results won't agree. (Here I'm assuming that SQLite doesn't do leap seconds, but maybe Richard will reply that it does.) This could potentially violate some application assumptions, and bugs could ensue. By contrast, if you repeatedly add one *minute* (which is not always the same as adding 60 seconds), both systems should perform the same. I'm not suggesting this error is catastrophic, merely that it's likely present, and I'm not sure the members of this list necessarily have the expertise (or desire) to implement the date/time functions in a really correct way, though they may expect them to *work* in a correct way. -Ken - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] time calculations
In "after-pressing-SEND hindsight:" I suppose that for a system to really care about a leap second jump, it would have to be at least reasonably synched to the NIST clocks as a precondition -- otherwise the normal computer clock drift would mean that the clock is off by multiple seconds routinely. And if one is synched to NIST, then the leap second correction will cause your computer's clock to jump right on schedule, and you'd need to account for this in any precise time-measuring applications running in the dead of night, unless some very special software lets you delay your implementation of the leap second. At any rate, SqLite shouldn't be dealing with any of that, right? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] time calculations
From: Williams, Ken [mailto:[EMAIL PROTECTED] That sounds convenient but potentially dangerous - does it understand leap years/seconds, for example? And various other pitfalls of date/time math? Regarding leap *years*, I agree that it should account for them, and I think it does for many decades. Regarding leap *seconds*: Maybe it's a leap of faith on my part ;-) but doesn't essentially all date/time software ignore leap seconds? I thought leap seconds were scheduled "as necessary" to keep things in synch with the star observations, so they can't be predicted accurately for years in advance. And if Sqlite *could* account for leap seconds, wouldn't it better if it did not? For example, most computer systems are not set up so as to implement leap seconds as they occur, so any software measuring durations would be incur an error if it made an adjustment "unilaterally," right? For those few systems where a second is quite significant, I wonder if it's better to simply correct the computer's clock at a time when you know you can account for this little quantum leap. [speaking only for myself and not on behalf of my employer] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] time calculations
> -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Saturday, February 28, 2004 6:57 PM > To: [EMAIL PROTECTED] > Subject: Re: [sqlite] time calculations > > > 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:21 > That sounds convenient but potentially dangerous - does it understand leap years/seconds, for example? And various other pitfalls of date/time math? -Ken - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] time calculations
Lloyd thomas wrote: > Will there be a simple way to update SQLite in PHP on a windows platform so > that I can use the new date/time functions? > You'll have to ask the PHP people about that. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] time calculations
Thank you DRH, I did manage to sort out my problem with the help from another member. The functions you are adding I'm sure will be appreciated. Will there be a simple way to update SQLite in PHP on a windows platform so that I can use the new date/time functions? Lloyd - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, February 29, 2004 12:57 AM Subject: Re: [sqlite] time calculations > Lloyd thomas wrote: > > 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:21 > > This 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:21 > > The 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:00 > > In 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] > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] time calculations
Lloyd thomas wrote: 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:21 This 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:21 The 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:00 In 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]
Re: [sqlite] time calculations
Kurt, I managed to resolve my problem with the following. Thanks for your help. code SELECT round((julianday(duration)-julianday('00:00:00')) * 86400) as duration_sec FROM call_data WHERE julianday(call_time) <= julianday('2003-12-03 18:42') AND (julianday('2003-12-03 18:42') - julianday(duration)) <= julianday(call_time) --- Lloyd - Original Message - From: "Lloyd thomas" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, February 28, 2004 10:28 PM Subject: Re: [sqlite] time calculations > 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 > - > > - Original Message - > From: "Kurt Welgehausen" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Saturday, February 28, 2004 9:26 PM > Subject: Re: [sqlite] time calculations > > > > >> it relies on having the seconds as well. > > > > I don't understand the question. As far as I know, > > nothing in SQLite requires the seconds. > > > > sqlite> select julianday('2003-12-04 16:41'); > > julianday('2003-12-04 16:41') > > - > > 2452978.19513889 > > > > If you're getting an error msg, maybe you can be > > more specific. > > > > Regards > > > > > > - > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] time calculations
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 - - Original Message - From: "Kurt Welgehausen" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, February 28, 2004 9:26 PM Subject: Re: [sqlite] time calculations > >> it relies on having the seconds as well. > > I don't understand the question. As far as I know, > nothing in SQLite requires the seconds. > > sqlite> select julianday('2003-12-04 16:41'); > julianday('2003-12-04 16:41') > - > 2452978.19513889 > > If you're getting an error msg, maybe you can be > more specific. > > Regards > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] time calculations
>> it relies on having the seconds as well. I don't understand the question. As far as I know, nothing in SQLite requires the seconds. sqlite> select julianday('2003-12-04 16:41'); julianday('2003-12-04 16:41') - 2452978.19513889 If you're getting an error msg, maybe you can be more specific. Regards - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] time calculations
- Original Message - From: "Lloyd thomas" <[EMAIL PROTECTED]> To: "Kurt Welgehausen" <[EMAIL PROTECTED]> Sent: Saturday, February 28, 2004 8:34 PM Subject: Re: [sqlite] time calculations > Thanks Kurt, > Nearly there, but not quite. In the datetime string I have, > it relies on having the seconds as well. ie. '2003-12-04 16:41:00' instead > of '2003-12-04 16:41'. is there a way round this. > - Original Message - > From: "Kurt Welgehausen" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Saturday, February 28, 2004 7:14 PM > Subject: Re: [sqlite] time calculations > > > > > > >> SELECT TIME_TO_SEC(duration)as duration > > >> FROM call_data where call_time <= '2003-12-04 16:41' > > >> AND DATE_SUB('2003-12-04 16:41', INTERVAL duration HOUR_SECOND) <= > call_time > > > > Try > > SELECT round((julianday(duration)-julianday('00:00:00')) * 86400) as > duration > > FROM call_data where call_time <= '2003-12-04 16:41' > > AND julianday('2003-12-04 16:41')-duration/86400.0 <= > julianday(call_time) > > > > I haven't tested it, but it's a start. > > > > See also http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions > > > > Regards > > > > - > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] time calculations
>> SELECT TIME_TO_SEC(duration)as duration >> FROM call_data where call_time <= '2003-12-04 16:41' >> AND DATE_SUB('2003-12-04 16:41', INTERVAL duration HOUR_SECOND) <= call_time Try SELECT round((julianday(duration)-julianday('00:00:00')) * 86400) as duration FROM call_data where call_time <= '2003-12-04 16:41' AND julianday('2003-12-04 16:41')-duration/86400.0 <= julianday(call_time) I haven't tested it, but it's a start. See also http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions Regards - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] time calculations
I know there are now some time functions in sqlite, but I did not quite understand the functions available in it. I apologise for the MySQL references below, but I wish to achieve the query below with SQLite, assuming some of you are fimiliar with MySQL. Can you help? code SELECT TIME_TO_SEC(duration)as duration FROM call_data where call_time <= '2003-12-04 16:41' AND DATE_SUB('2003-12-04 16:41', INTERVAL duration HOUR_SECOND) <= call_time --- Lloyd