What am I doing wrong in making a format code HH:MM:SS.000?

[hrafnblod]

In Base, I’m trying to get a field to have default formatting for CCTV time stamp as it appears on the camera, specifically HH:MM:SS.000

When I use HH:MM:SS.000 as the format code, it replaces the “000” bit with literally “000” when i move to the next line.

Example

I enter 01:24:30.731 into the appropriate box and when i move to the next entry, Base replaces what i’ve entered with 01:24:30.000

For now ive had to just enter the date as text (hh:mm:ss.xxx), but once ive understood how to make the correct code, will it automatically change that text into the appropriate format, or will i have to go through by hand?

Additionally, although possibly this may need to be covered in another separate question, I never seem to have much luck in understanding how to make the format codes and/or have them work as I expect. Specifically, I need a code to always display four digits, e.g. 0.900, 0.925, 0.800 etc., I also need to understand how to have letters displayed after numbers, but only from a selection of them e.g. Cu, Zn, Ag, etc. and so I can combine the two e.g. 0.900 Ag, 0.200 Cu etc…

I have tries and tried to go through existing documentation and understand how to do these things but either I’m being a little obtuse or…

Any help would be great thanks
Regards

NB Why is the recaptcha thingy in Portuguese?

[Ratslinger]

First, the Captcha problem has been around for a while - no sign of when it will be fixed. Language varies each time.

You don’t mention anything about your setup. I’ll guess you have a fairly recent version of LO and are dealing with an embedded DB. The embedded version of HSQL (the database) is 1.8 whereas the current version is 2.3 or maybe higher. What you are looking for with your time field is precision. I don’t see this defined in v1.8 but I see it in the v2.3 documentation. You would define it as FIELD-NAME TIME(X) where X = number of decimal positions. The default is 0.

Tried to set up a new table using direct SQL entry (since there is no way to do this in the regular table definition). It is rejected in HSQL v1.8 with a numeric value out of range error. Then tried with a split DB using HSQL v2.3 and it came back with a successful creation. However, upon testing, the decimal positions were always zeroes when the field entry was made. I also tried using SQL Workbench/J to enter the data but got an invalid date field error.

Next I tried a Base file connected to MySQL. Again direct SQL to create table was OK but field never displayed decimal positions. Then went to MySQL Workbench, set up the table and records entered worked. Using this table in Base it still displayed zeroes for the decimals even though digits were in the records. This is a bug in Base and you should report it (click here). I don’t see one reported currently.

As for the other fields. The 0.900, 0.925 etc - set field as DOUBLE in table with format of one leading zero and three decimal positions.

The characters after the numbers? Once you mix numbers & letters this is a TEXT (VARCHAR) field. What seems appropriate is a DOUBLE field for the number and VARCHAR(2) for the text. In form data entry for the characters you can limit the selection by using a list box and have only allowable entries as a Valuelist. You can place the two fields together if necessary for various displays or reports by concatenating them in an SQL statement as one field.

[EasyTrieve]

EDIT: I tested the user-format code “MM/DD/YYYY HH:MM:SS.000” in HSQLDB 2.3 using a split database and it works to show sub-seconds. But in HSQLDB 1.8 (comes by default w/ LO 5.2.3.3) it does not work.

(BTW, I also finally found, and added a link to the 1.8 HSQLDB manual to my documentation list below.)

old:
I suggest you refer to this list of documentation that I compiled to help people like you.

In particular, please scroll down to the user guide for the HyperSQLdb (HSQLdb).

On page 202 of this guide there is a table of available formatting codes for date and time.

That should help you if you are using the default “Embedded” data base. But please note that the default embedded HSQL data base in the most recent LO is version 1.8, not the newer 2.x, which this manual is for. So some things might not work, that is, unless you split your database so you can upgrade to HSQL 2.x. But that being said most things should work as described.

---

If this helps you, please upvote this answer by clicking the “^” symbol to the left of this question. Thanks.

[Ratslinger]

Page 202 is not referring to setting up dates/times in the table. Page 19 is the actual definition (pdf page 32). Regardless of the documentation, Base is not recognizing time precision even when you can get any DB set up with it.

[EasyTrieve]

Updated my answer.

[Ratslinger]

How do you get a table field of type TIME to accept data in a format of HH:MM:SS.000? Displays will work but the problem is in saving to the table field itself. Have only been successful in MySQL directly and not through Base or even HSQL 2.x version.

[Ratslinger]

@EasyTrieve - understand your Timestamp sample, but the problem is with a TIME field (that is of course ‘with precision’). Have tried every possible way but all fails (again except directly in MySQL). If you have something, I’m looking forward to it. As for an answer to @hrafnblod it appears a Timestamp is the best solution at this point.

[EasyTrieve]

@hrafnblod I confirm with R that I can only get this to work in MySQL (MariaDB 10.1 actually) directly (like testing with HeidiSQL. I can not get LO-connected-to-MySQL to do fractional seconds. If you don’t want to move to HSQLDB 3.2+ then I suppose you could have two fields, on for datetime, and one for fractional seconds. It’s not unheard of to do such things. You can use the SQL function CONCAT() to join them together for appropriate display, and sort both fields when you sort.