VERSIONS: All versions
SUBJECT: Time calculations
FROM: Paradox Technical Information document
Q: How do I sum all the times in a time field in a query?
A: If you have a report or form based on a query, it is quite easy to do time or timestamp arithmetic in a calculated field based on a calculated
field in a query. This is because using the time() or datetime() procedure against a number in milliseconds results in a time or timestamp. For
example, if your query looks as follows:
Query
Table | Time1 | Time2 |
| _t | _t2, calc _t2 - _t1 as timediff |
EndQuery
There are 86400000 milliseconds in a day. So, a calculated field that shows a sum of the times as a result of this query would be:
time(sum([:PRIV:ANSWER.timediff]*86400000.0))
This assumes the sum of all the times is less than a day. If not, you need to use the following formula to get the days portion as well:
datetime(sum([:PRIV:ANSWER.timediff])*86400000.0)
Q: In a calculated field, how do I subtract two times or timestamps and get the number of minutes or hours?
A:Similar to other data types, by default when you operate on two values of the same datatype, a calculation returns the same data type. This
means if you subtract two times you get a time, if you subtract two timestamps you get a timestamp. To get the data in another format you must
perform an operation on it.
Any of the methods in the time type may be applied to the result of the subtraction of two times, and similarly any of the methods in the
datetime type may be applied to the subtraction of two timestamp fields.
Minute() returns the minutes portion of a time or timestamp field. Hours() returns the hours portion. Here is a formula that calculates the
number of minutes between two times:
hour([Table.Time2] - [Table.Time1]) * 60 + minute([Table.Time2] - [Table.Time1])
Assuming that the two timestamp values are less than 24 hours apart, then the above formula would be identical for timestamp fields. If you want
to consider timestamps which might be more than 24 hours apart, then you need to consider days as well. The following code demonstrates
this:
day([Table.TimeStamp2] - [Table.TimeStamp1]) * 60 * 24 + hour([Table.TimeStamp2] - [Table.TimeStamp1]) * 60 + minute([Table.TimeStamp2]
- [Table.TimeStamp1])
Returning the number of hours between a time or a timestamp value is similar. Here is a formula that calculates the difference in hours, and
the fraction thereof, between two times:
hour([Table.Time2] - [Table.Time1]) + minute([Table.Time2] - [Table.Time1])/60.0
Similarly, here is a formula that calculates the number of hours, and the fraction thereof, between two timestamp values assuming the two
timestamps might be more than 24 hours apart:
day([Table.TimeStamp2] - [Table.TimeStamp1]) * 24 + hour([Table.TimeStamp2] - [Table.TimeStamp1]) + minute([Table.TimeStamp2] -
[Table.TimeStamp1])/60.0