[Solved] Duration from two times?

[kabing]

I've searched the forum and looked at the Built-in functions lists and not found anything, so I'm guessing this isn't possible, but I thought I'd ask to know for sure.

Is Base capabale of calculating the duration (in minutes) of an activity when the starting and ending times are known? If so, how is this done?

If it's not possible, I'll just add a field to my database to manually enter the duration.

NeoOffice 2.2.3 (based on OpenOffice.org 2.2.1), Mac OS 10.4.11.

kabing

[squenson]

While dates and times are displayed in a format underestandable by humans, they are stored in Base as numbers called "double". The integer part of the number represeents the number of days from a reference date, which is January 1st, 1900, if I am not msitaken. The fractional part is the time, so 0.5 represents 12 hours, 4 hours is 0.33333, etc.

To calculate the difference between two times, simply subtract the two numbers and multiply by 24 then by 60 to get the number of minutes. Of course, it may be slightly more complicated if you have to consider that the time can span over midnight, so if the result in minutes is negative, simply add 1,440 minutes to get the right number.

[kabing]

Thanks! I probably should account for the possibility of a time period stretching over midnight. I don't see IF or CASEWHEN (?) in the list of Built-in functions for Base. Is it possible to use either in a Query

If it is possible to use CASEWHEN, could you spell out the syntax for me? I'm not familiar with that function, beyond having seen it mentioned a couple of times in the forums.

I forgot to say above that I'm using the embedded HSQLDB engine.

kabing

[squenson]

You can use a formula like:

((TimeEnd - TimeStart) < 0) * 1440 + TimeEnd - TimeStart

Note that I have not tested it.

[Sliderule]

kabling:

You might consider using DATEDIFF to help determine the number of 'minutes' between two datetime fields ( values ).

According to documentation at http://wiki.services.openoffice.org/wik ... _Functions:

DATEDIFF(string, datetime1, datetime2)

Returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'. Both the long and short form of the strings can be used.

DATEDIFF('dd', '2007-08-01', '2007-09-01' ) = 31

So, in your case . . . assuming you have two fields named: TIME_VALUE_1 and TIME_VALUE_2 . . . and . . . these are defined as only TIME ( not DATETIME ) . . . you could use either:

1. Code: Select all

   DATEDIFF( 'mi', CURRENT_DATE || ' ' || "TIME_FIELD_1", CURRENT_DATE || ' ' || "TIME_FIELD_2" )

2. Code: Select all

   DATEDIFF( 'mi', CONCAT(CONCAT(CURRENT_DATE, ' '), "TIME_FIELD_1"), CONCAT(CONCAT(CURRENT_DATE, ' '),  "TIME_FIELD_2") )

3. Code: Select all

   DATEDIFF( 'minute', CONCAT(CONCAT(CURRENT_DATE, ' '), "TIME_FIELD_1"), CONCAT(CONCAT(CURRENT_DATE, ' '),  "TIME_FIELD_2") )

4. Code: Select all

   DATEDIFF( 'minute', CURRENT_DATE || ' ' || "TIME_FIELD_1", CURRENT_DATE || ' ' || "TIME_FIELD_2" )

With regard to CASEWHEN . . . it is documented at: http://www.hsqldb.org/doc/guide/ch09.html#N1251E

CASEWHEN(exp,v1,v2)

if exp is true, v1 is returned, else v2)

The CASEWHEN is very similar to a Spreadsheet ( Calc ) IF statement, except, you use commas, not semi-colons, to separate the function arguments. You may 'nest' the functions accordly.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.

[kabing]

Thanks very much. I couldn't get squenson's equation to work, but Sliderule's #2 worked fine. I did replace CURRENT_DATE with "Date" as I do have a Date field in the table in question.

It doesn't adjust for End times after midnight, but I don't think that's going to be a big issue for me.

kabing

[Villeroy]

While dates and times are displayed in a format underestandable by humans, they are stored in Base as numbers called "double". The integer part of the number represeents the number of days from a reference date, which is January 1st, 1900, if I am not msitaken. The fractional part is the time, so 0.5 represents 12 hours, 4 hours is 0.33333, etc.

To calculate the difference between two times, simply subtract the two numbers and multiply by 24 then by 60 to get the number of minutes. Of course, it may be slightly more complicated if you have to consider that the time can span over midnight, so if the result in minutes is negative, simply add 1,440 minutes to get the right number.

Just for the records. If this would be so, there would be a simple answer to this other question DATE_ADD alternative?. At least you could simply add days by adding plain integers to a date or to a conversion of a date.
In fact we can not even convert a date/time to number by means of functions CONVERT or CAST.

Code: Select all

SELECT CAST ( "Date" AS INTEGER ) FROM "Table1"

(no matter if direct mode or interpreted) gives error:

SQL Status: 37000
Error code: -16

Wrong data type: java.lang.NumberFormatException: For input string: "2008-12-31" in statement [SELECT CAST ( "Date" AS INTEGER ) FROM "Table1"]

On the other hand you can format any date/time as generic number and:

Code: Select all

SELECT MIN( "Date" ), MAX( "Date" ), MIN( "Time" ), MAX( "Time" ) FROM "Table1"

shows the aggreated dates and times formatted as plain numbers with 1900-01-01 as day zero and times as fractions of days.

disgusted.