My database is for time-based billing. I would like to add an internal verification mechanism that verifies that none of the entered times are overlapping. (We can't be in two places at once, now can we?)
Each record contains a start time, a stop time and the date of service. I just need to ensure that there aren't any conflicts.
At first this seems like a simple task, just check that
STARTTIME(this record) > STOPTIME(previous record)
STOPTIME(this record)<STARTTIME(next record)
And, in fact a simple check like that works - most of the time. When billing periods extend beyond midnight into the next day and the stop time is now likely to be numerically smaller than the start time, things get more complex.
I solved most of the troubles by converting all the start and stop times into the number of elapsed minutes from the stroke of midnight on new years eve. Then it is a simple comparison. It works fine except for a billing period that spans midnight on new years eve. In that situation I run into the same sort of problem.
I'm wondering if there is some simpler solution to this problem that I'm missing. I'm thinking along the lines of how the Mac operating system is referenced to a date in 1906 (or similar) and all time calculations use the elapsed time from then. Does FM use a similar reckoning system internally? If so, can this be accessed?
Or is there something else I could do entirely?