Base: Convert decimal number to MM:SS.00

[nickwe]

Hello,

LibreOffice Base
Version: 4.3.6.2 Arch Linux build-2
Locale: English(UK)

I need to make a database which display time as MM:SS.00 (swimming race time).

In Calc it’s very easy as there is a “Format” for that, but in Base I’ve seen that the “protocol” wouldn’t allow it because time is limited to the second, like if I entered 01:05.78, it would automatically become 1:05.00. MS Access do exactly the same, so this is why I’m talking about “protocol”. I’ve tried to connect Base with HSQLDB, MySQL (Direct, ODBC and JDBC), none is working

Even worse, if I manually put in the MySQL database 1:05.78, it would still display 01:05.00 in Base no matter what I do

So I have read in forums that I should import/enter the “race time” in Base as number (to still be able to do some numerical comparison) of seconds with two decimal as “double float” format and then format the display for the reports (would be great to be able to format in “Query” view but I doubt it would be possible), which is what I’m trying to do right now…

So the idea would be to convert (in a query, form or report) 65.78 seconds to 01:05.78. It’s swimming time, so the time won’t never be over 25 minutes.

I think it should be possible without macro with TimeValue(), CONCAT() and/or TRUNK() but I don’t know how to use them

In MS Access I’ve seen forums where it pretends to work with the Format() function:

FORMAT(Int([time_field]/3600),"00") & ":" & Format(Int(([time_field]-(Int([time_field]/3600)*3600))/60),"00") & ":" & Format((([time_field] Mod 60)),"00")

but this function is not available in LibreOffice, so I can’t test.

Regards,

Nicolas.

[doug]

So you will display a DECIMAL value as a string. That should be ok for display only; for update to the database from VARCHAR (00:00.00) you would need a macro.

After constructing said function, I don’t blame you for throwing up your hands. This implicates multiple limitations of version 1.8 of HSQLDB. That said, here is the kluge function that appears to spit out the correctly-formatted string, where "tmsec" is the DECIMAL field representing number of seconds:

CONCAT( 
  CONCAT(CAST( FLOOR( "tmsec" / 60 ) AS INTEGER ), ':' ), 
  CONCAT( 
    CASE WHEN "tmsec" - (( FLOOR( "tmsec" / 60 ) )* 60) < 10 THEN '0' ELSE '' END, 
    CONCAT( 
      ROUND( "tmsec" - (( FLOOR( "tmsec" / 60 ) )* 60), 2), 
      CASE WHEN SUBSTRING ( ROUND( "tmsec", 2 ), 2, 1 ) = '.' THEN '0' ELSE '' END 
    ) 
  ) 
)

Copy that and paste it into the Query Builder at the top of a column and give it an alias, or else use it directly in a SQL statement.

The CASE WHEN statements are to deal with special problems where seconds fewer than 10 have the wrong number of digits, as does the output of ROUND([field], 2) when [field] is an integer value.

Seems like there ought to be lots of other ways to do this, but in thinking through other options appear to be roadblocks there also, for example, DATETIME has milliseconds, etc., but LO Base Forms and tables appear not to be capable of either displaying or accepting data entry, not sure. Seems like there should be a native string function in MySQL cleaner than the kluge above, but not seeing it immediately.

As a further note: I am encountering another LO Base limitation where I can save and execute the query, and execute again later, but subsequent attempts to open for editing in the Query Builder GUI will be met with spurious reports of syntax error. Might try to sidestep these problems by saving as a VIEW.

EDIT: addressed a conversion problem in initial version.

(if this answered your question, please accept the answer by clicking the check () to the left)

[nickwe]

You’re my hero, working perfectly, thanks a lot doug!

[nickwe]

Thanks a lot doug, you’re my hero

Working perfectly for display just as I wanted as I’ll do all the calculation/comparison on the decimal field

Best Regards,

Nicolas.