In order to give the duration of a event in total houres, I made a list in Calc where one collumn has the format [HH]:MM:SS. The format in Calc works correctly. However by importing the data in Base, I get the message that this format is not supported in Base. Trying to make the tablefield by hand, the fieldformat TIME [HH]:MM:SS is accepted but it acts as HH:MM:SS. E.g. it shows only houres between 00:00:00 and 23:59:59. Input of a value larger than 23:59:59 results in starting to count from 00:00:00. The funny thing is if I make the fieldformat NUMMERIC (length 10 with 6 decimals), entering the nummeric timevalue (1,615278 = 38:46:00) and I use in a form this field with the format [HH]:MM:SS it displays the duration correctly. But using the form I can't make any changes in the field because then it returns to act as HH:MM:SS. Calculating and displaying correct time-events is a feature that a database should support. That is why i see this problem as major. Searching the internet I discovered that the problem exists in Libreoffice and Openoffice. There are lots of questions but no answers. And i cann't find the report of this problem. As OS I use Windows (Vista and XP) and Linux (Xubuntu) I use Libreoffice 3.5.4.2 but i think the problem is not version-specific. Greetings from a Dutch user.
Hi Cocky, Thanks for posting! If I saw it correct, you posted to the users list a quote from the help, stating that is should work as you expected? I added Lionel as cc - since he is our Base guru. I set the importance back to medium - not that I want to say it's not a nasty bug, but basically that field is related to the developers insight.
Please give more detailed information: 1) Attach an example ODB file, and if not an embedded HSQL one (e.g. MySQL or PostgreSQL), then also attach the SQL script to run against the server to create the example database. 2) What is the [HH]:MM:SS format supposed to do? You says it should do something different than HH:MM:SS, but don't say what. 4) How do you "import the data in Base"? Copy/paste? A wizard somewhere (what menu entry?)? An odb file that has the ods spreadsheet as underlying database? 5) Are your fields of type "DATE", "DATETIME", "TIMESTAMP", something else? 6) More generally, please give detailed reproduction information, what happens and what you expect to happen. E.g. "in attached example odb, open form FOO, enter 15:24:41 in field DUR. It should display "3pm 24min 41s", but it displays "15am 24arc 41micro".
Just a guess as I'm by no means a database expert: a Base TIME field is limited to clock time (<24h) and does not accept duration times.
(In reply to comment #3) > Just a guess as I'm by no means a database expert: a Base TIME field is limited > to clock time (<24h) and does not accept duration times. It all depends on the underlying database engine "obviously". But basically in the SQL standard TIME is indeed a clock time (the time part of a full datetime/timestamp). For a duration, type INTERVAL seems more appropriate. Except that HSQLDB 1.8 (used as our embedded HSQL) does not support that datatype. Use a database backend that supports it, I suppose <shrug>
(In reply to comment #4) > basically in > the SQL standard TIME is indeed a clock time (the time part of a full > datetime/timestamp). For a duration, type INTERVAL seems more appropriate. > Except that HSQLDB 1.8 (used as our embedded HSQL) does not support that > datatype. Use a database backend that supports it, I suppose <shrug> Except that Base itself does not support the INTERVAL datatype. <sigh> For this whole shebang to have a chance of working as expected, this would have to be added: 1) offapi/com/sun/star/sdbc/DataType.idl needs a value for INTERVAL 2) connectivity/inc/connectivity/FValue.hxx: class ORowSetValue needs to be adapted to handle values of that type. Also connectivity source/commontools/FValue.cxx and undoubtedly many other places, such as source/commontools/DateConversion.cxx, etc. In the meantime using integers or floats to store "number of $SOME_TIME_UNIT" is probably the best one can do. SOME_TIME_UNIT=seconds and integers is probably the sanest (if seconds resolution is enough).
Created attachment 63576 [details] An example of a sheet collecting data This is the sheet with various TIME formats
Created attachment 63577 [details] An example of the database The database containing 1. A table with the imported data from Example sheet.ods 2. A form connected to the table.
I mostly use Spreadsheets to collect data and plan the deviation in tables. Now the goal is to insert the specific duration of a event. Attached i have a CALC-sheet called Example Sheet.ods. In the Netherlands we don't work with AM/PM but i made a few collumns with differend variations of the format TIME. I did set the language of the format to English(VS). Take note of the following collumns: In the first collumn (DUR_[HH]) you see the display of the duration to be expected. It has the format TIME [HH]:MM:SS and it keeps counting the Houres even if the duration is longer than 24 houres. In the second collumn (DUR_HH) The format is TIME HH:MM;SS. The difference here is that this format only displays time between 00:00:00 and 23:59:59. In the last collumn (DUR_DEC) The format is NUMMERIC 0.000000 Now I create a new Database called Example Base.odb (attached) and let Libreoffice registrate the database. In the sheet i select the range A1:E5 and choose Copy In the base i choose Paste defenitions and data using the first row as header and letting Base make a primary Key. Then i add all the fields and set the fieldtype as TIME exept the last field wich is NUMMERIC with 6 decimals and saving the table as TabelTIME Now i get a warning "Wrong data type: java.lang.IlligalArgumentExeption". Do you wish to continue? Yes! If you look at the table now, you see that the field (DUR_[HH]) acts as format HH:MM:SS Importing the fields with AM/PM time does not work correctly because it fails to take over the language-setting (not importand to me i suppose but worth mentioning). Next step is creating a Form (FormTIME) based on the table. I used the form wizzard with default settings. And then i made a change in the format of the last field. I changed it in [HH]:MM:SS. The result is that it displays the duration now correctly. But the problem is that i can't change the value of the field in the form because than it starts acting as HH:MM:SS and i get a failure message "java.lang.NumberFormatExeption".
(In reply to comment #2) > Please give more detailed information: > > 1) Attach an example ODB file, DONE! See comment 6 and 7. > and if not an embedded HSQL one (e.g. MySQL or > PostgreSQL), then also attach the SQL script to run against the server to > create the example database. Sorry but poor me as user don't know how :( > 2) What is the [HH]:MM:SS format supposed to do? You says it should do > something different than HH:MM:SS, but don't say what. It displays houres above 23 > 4) How do you "import the data in Base"? Copy/paste? A wizard somewhere (what > menu entry?)? An odb file that has the ods spreadsheet as underlying database? I select the range in a CALC-sheet and copy it into BASE. See explanation in Comment 8 > 5) Are your fields of type "DATE", "DATETIME", "TIMESTAMP", something else? Fields are of type TIME > 6) More generally, please give detailed reproduction information, what happens > and what you expect to happen. E.g. "in attached example odb, open form FOO, > enter 15:24:41 in field DUR. It should display "3pm 24min 41s", but it displays > "15am 24arc 41micro". See explanation in Comment 8
(In reply to comment #8) Hi, > > Next step is creating a Form (FormTIME) based on the table. I used the form > wizzard with default settings. And then i made a change in the format of the > last field. I changed it in [HH]:MM:SS. The result is that it displays the > duration now correctly. But the problem is that i can't change the value of the > field in the form because than it starts acting as HH:MM:SS and i get a failure > message "java.lang.NumberFormatExeption". For this particular one, have you tried unsetting (in the form's general properties, I think, but don't remember exactly), whereby the form control formatting overrides data input ? That might allow you to enter the value you want and be taken up in the db. I haven't tried this by the way, so can't guarantee it will work. Alex
(In reply to comment #10) > > For this particular one, have you tried unsetting (in the form's general > properties, I think, but don't remember exactly), whereby the form control > formatting overrides data input ? That might allow you to enter the value you > want and be taken up in the db. I haven't tried this by the way, so can't > guarantee it will work. > > > Alex It just isn't working. I suppose this won't be solved in the near future. And that is a shame. I started to like the program en can still recommend it to a lot of people. (Witch i really do!) I respect the people working at this project and understand that i can't have it all. I will check on a regular base here, if there is any development. Cocky
(In reply to comment #9) > (In reply to comment #2) >> 5) Are your fields of type "DATE", "DATETIME", "TIMESTAMP", something else? > Fields are of type TIME That's your problem. SQL type "TIME" stores values between "00:00:00" and "23:59:59" (or possibly "23:59:60" if the implementation is that good that it handles leap seconds; I doubt any implementation is that good). So it is not the *format* that does not work, but you are using the wrong column type. What you are trying to do is akin to trying to store "600" in a TINYINT column: it will wrap around and *store* 88 because you are beyond the range handled by the column. Try with TIMESTAMP (Date/Time) type, it is hackish, but it seems to sorta-work when the data is entered under date format (as e.g. "50:00:00"), and only formatted as [HH]:MM:SS later for printing / output. I think your best bet right now is to use a float/real/double/decimal type for the column, and format [HH]:MM:SS for output. The true solution would be the INTERVAL column type, but it is not supported by current version of LibO (and neither by the old embedded HSQLDB it uses) :-( >> and if not an embedded HSQL one (e.g. MySQL or >> PostgreSQL), then also attach the SQL script to run against the server to >> create the example database. > Sorry but poor me as user don't know how :( Your example is embedded HSQLDB, so no need for a SQL script (it is included in the .odb).
(In reply to comment #12) > That's your problem. SQL type "TIME" stores values between "00:00:00" and > "23:59:59" (or possibly "23:59:60" if the implementation is that good that it > handles leap seconds; I doubt any implementation is that good). So it is not > the *format* that does not work, but you are using the wrong column type. What > you are trying to do is akin to trying to store "600" in a TINYINT column: it > will wrap around and *store* 88 because you are beyond the range handled by the > column. If that is so, then why is there a menu in editing a tablefield that gives the possibillity to choose the format TIME (876613:37:46). If the possibillity is given and in the helpfile also mentioned, a user should be able to use it without complications. If a option is INVALID than take it out of the program. > Try with TIMESTAMP (Date/Time) type, it is hackish, but it seems to sorta-work > when the data is entered under date format (as e.g. "50:00:00"), and only > formatted as [HH]:MM:SS later for printing / output. I will try this. Thanks for the help so far!
(In reply to comment #13) > (In reply to comment #12) >> That's your problem. SQL type "TIME" stores values between "00:00:00" and >> "23:59:59". So it is not the *format* that does not work, but you are using the >> wrong column type. What you are trying to do is akin to trying to store "600" >> in a TINYINT column: it will wrap around and *store* 88 because you are beyond >> the range handled by the column. > If that is so, then why is there a menu in editing a tablefield that gives the > possibillity to choose the format TIME (876613:37:46). The choice of format is not, in general, restricted by the type of the column. You can also choose a "Boolean Value" format, which makes even less sense. So, yes, the user is allowed to shoot him/herself in the foot and to make choices that don't make sense. Also, the formatting system is shared with Calc, which does not have as strong/static typing as a SQL database. Calc does not make a clean difference between a TIME (within an unspecified day), a DATE (a specific day), a DATETIME (SQL TIMESTAMP, a specific time withing a specific day) and a duration (time interval, SQL INTERVAL, that is the difference between two DATETIMEs). These are all floating-point numbers to Calc, just "formatted" differently. The difference between interval and time is the same as "2 hours" and "2 o'clock". "40 hours" makes sense as a duration, but "40 o'clock" does not make sense (on planet Earth). SQL databases make the difference between the two, but LibreOffice Base does not support intervals. That's a missing feature, I filed bug 51779 about that. > If the possibillity is given and in the helpfile also mentioned, > a user should be able to use it without complications. I don't know which paragraph of which documentation you refer to; we'd be glad for a suggestion what to write instead of what where.