Ask Your Question

# Base: Convert decimal number to MM:SS.00

asked 2015-04-16 22:50:02 +0200

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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.

edit retag close merge delete

## 2 Answers

Sort by » oldest newest most voted

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)

more

## Comments

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

( 2015-04-17 08:48:27 +0200 )edit

answered 2015-04-17 08:47:17 +0200

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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.

more

## Stats

Asked: 2015-04-16 22:50:02 +0200

Seen: 7,875 times

Last updated: Apr 17 '15