On Wed, Feb 29, 2012 at 8:41 AM, Mark Belshaw
<s...@enfield-computers.co.uk>wrote:
> 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!
>
It sure did. Thanks for participating.
> 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.
>
That's an interesting angle. I may actually already be doing something
like that inadvertently, because I am processing different times of day and
likely events during them separately. In other words, I might only handle
the 8pm--4am block in one action, which would be equivalent to your True
("After Midnight") case.
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.
>
I'll think about how to incorporate that in what I'm doing.
Thanks!
Che
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
