5 Replies Latest reply on Oct 28, 2013 7:21 AM by erolst

# Using dates to calculate?

Hello there,

I have a db where I have the following fields:

log_date log_hour number

10/15/13 24:00 12345

10/16/13 01:00 12356

There are 24 hours for each day so I have 24 records for each day starting with 01:00 and ending with 24:00. I am trying to use the number from let's say hour 01:00 and subtract from the number in hour 02:00. My calculation works by subracting the number field between log_hour "02:00" and log hour "01:00". I used relationships to provide the basis for the calculations for the hours during the same day but to get the last hour from the previous day seems to be beyond my brain power at the moment. This works fine until I try to subtract the 01:00 hour from the 24:00 hour from the previous day. I know I am missing something simple but for the life of me I can't figure that one out.

Any suggestions?

Thanks,

Wayne

• ###### 1. Re: Using dates to calculate?

You only need one self-join relationship, using a timestamp calculation from the date and time field. Sort it descending, and the first record via this relationship is the respective previous entry, even across the day boundary.

• ###### 2. Re: Using dates to calculate?

It seems like you're just trying to get the difference between two timestamps in hours.  Have you tried Hour ( Timestamp ( log_date2, log_hour2 ) - Timestamp ( log_date1, log_hour1 ) )?

• ###### 3. Re: Using dates to calculate?

There are 24 records for each day, then I need one field from the 24:00 Log_Hour record from the previous day to start the "Sum" for the field. This field will usually contain a 7 digit number that I need to get the difference from one hour to the next through the 24 hour at the end of the day.

• ###### 4. Re: Using dates to calculate?

Does it need to look like this?

Timestamp = Autoenter calculation, Type=Timestamp, Calculation "log_date + log_hour"

• ###### 5. Re: Using dates to calculate?

You can either use a timestamp field with auto-enter calculation, or a calculation field, type timestamp; in both cases the calculation is Timestamp ( log_date ; log_hour ).