This is the first time I've posted a response to any mailing list, so I hope
I'm doing it right and it appears where it should!
Not SQLite, but a technique we use in our Time & Attendance system to help
with this sort of thing, where employees work nights / days and rotating
shift patterns, is to include a Boolean "After Midnight" with each time. We
are not looking for averages, so I won't get into the SQL you would use for
that, but are trying to keep transactions for the same shift together, where
they may be from different, consecutive, calendar days.
So, an early morning transaction for an early morning shift would have, say
False | 04:35, whereas an early morning Out from a night shift would be True
| 05:02.
The arithmetic then becomes something like ([Out After Midnight * 24] + Out
Time) - ([In After Midnight] * 24] + In Time). I guess it's really just a
variation on the 'add 12 hours, do your stuff, then take it off again'
method but, if you can determine the required state of After Midnight at the
point of data collection, it gives you a method to persist this state to the
database, rather than having to unpick each one on the fly.
Hope it's of some help :)
Mark Belshaw
-----Original Message-----
Message: 4
Date: Mon, 27 Feb 2012 14:09:55 -0500
From: C M <cmpyt...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: Re: [sqlite] SELECT average timestamp to get average time of
day?
Message-ID:
<caat3jwgj-glysngt8j_eadup--tt9y3ibf7fn3dwadvuwg8...@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
On Fri, Feb 24, 2012 at 4:53 PM, Marc L. Allen
<mlal...@outsitenetworks.com>wrote:
> You're trying to calculate it for individual people? Can you count on
> night-time people to stay night-time, or do you need to worry about
> someone shifting by 12 hours?
>
It's for individuals, and it is possible for individuals to shift or drift
by any amount.
>
> If not, your best bet is, for the night-time people, add, say 6 hours
> to all of their times, do your average, then subtract the 6 hours back
out.
>
Yes, this is a good idea, the same as was given in another response.
Thanks.
I found that this type of measure is referred to as the "mean of circular
quantities", and there is even a Wikipedia page about that...I had just
never thought about it before. I also found the Mitsuta Method for dealing
with this type of issue. But in any approach, things break down if data is
strewn all over a 24 hour period.
> There are cases where this will fail, but you might be able to detect
> data sets that will cause this issue and ignore them.
>
I will have to just come up with a reasonable check of the data's variance
and if I find it is all over the clockface, let the user know that the mean
bedtime can't really be computed due to the erratic data. Maybe if only a
few outliers are found I could filter them out. I may post a follow-up
question regarding that.
Thanks!
Che
________________________________________________________________________
This e-mail has been scanned for all viruses by Star. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
