calculating elapse time

[timalex]

Hi

I know this is on old chestnut, but I can't seem to find an obvious solution.

I have an event database (date field, time field, event). I am trying to perform a find subset of events that only happen up to a certain time.

Example: (Rec, cDate, cTime, Event)

1 01.03.01 12:00:00 OFF

2 17.03.01 17:30:00 ON

3 20.04.01 09:00:00 ON

4 20.04.01 10:00:00 OFF

5 22.04.01 09:00:00 OFF

6 22.04.01 23:30:00 ON

set globaldate=22.04.01

set globaltime=17:00:00

(I want to find all events that happened up until 22.04.01 at 17:00:00.

If I set a calculation field so:

calcdate: cdate<=globaldate AND ctime<globaltime

it doesn't give me the right result because already in rec 2 the time invalidates the calculation.

I have a feeling I need to convert the date and time to a number of seconds from the start of the year or something.

[timalex]

Well I sorta found a solution to this. I realised that if I store a date field as numeric, I get a value that relates to some date at the start of the year 0001. If I multiply this out into seconds, then add it to the number of seconds in my time field, I get a timestamp.

The only drawback was that my values were going off the numeric scale, so I have to deduct a constant (in my case, 730000 days) to make the number more readable.

Here the calculation: (cdate and ctime stored as numeric fields)

(cdate-730000)*24*60*60 + ctime