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
> -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
>> 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]
