Time field not accepting 1/100th of seconds

rktu · General User Joined: 23 Nov 2006 Posts: 7

Hi,

I've been trying to create a DB to track the times of the swimmers in our local swimming club.

I create the tables and relatinships were needed but when I go to enter the times in the tables it will not accept the hundredths of second. The format being used is mm:ss.00 which is what is advised by the help.

When I enter the .xx it reverts to zero. (eg 1:06.34 becomes 1:06.00) I have tried replacing the .00 in the format with ## but this is not accepted.

I am using version 2.02.

Has anyone got any sugestions for me.

Regards,

Rob

DiGro · Posted: Thu Nov 23, 2006 8:40 am Post subject:

Rob,

tried it on 2.0.4. It simply accepts the entry: 01:06.45
This should be the default format when entering a time WITH a decimal separator.

That is: 1 : 06 . 45 (minutes colon seconds decimal separator 100" sec )

Don't really know what separator your locale is using but it might be the comma instead of the dot ?
_________________
DiGro

Windows 7 Home Premium and OOo 3.3 NL (Dutch)

rktu · General User Joined: 23 Nov 2006 Posts: 7

Thanks for the reply.

Was using ":" and "." seperators. Thats the way we reocord time in Australia.

Might see if 2.04 solves the problem.

Thanks again.

Rob

rktu · General User Joined: 23 Nov 2006 Posts: 7

Have tried it with version 2.04 and the same result:

Data entered 00:36.17

Data recored 00:36.00

The fraction of second stays until you tab to the next record then it zeros itself.

Please help.

What am I doing wrong.

jrkrideau · Posted: Sat Nov 25, 2006 8:19 am Post subject:

acknak · Posted: Sat Nov 25, 2006 9:44 am Post subject:

What kind of data store are you using? What is the data type for the field?

If the database is using a datatype that stores times as integer seconds, then no matter what format you use, it's going to throw away the fractional part.

rktu · General User Joined: 23 Nov 2006 Posts: 7

The format I have show is standard under time, take a look.

rktu · General User Joined: 23 Nov 2006 Posts: 7

Is there anywhere I can post a copy of the basic db file?

acknak · Posted: Sat Nov 25, 2006 10:55 am Post subject:

I can only assume you are using the default options, meaning the data are stored in an HSQLDB database. Unfortunately, the HSQLDB docs only specify the data types in terms of the Java data types, and the Java docs indicate it could be either integer seconds, or integer milliseconds. I guess we would have to make a deep dive into the HSQLDB source code to find out for sure.

In my playing around with it, it looks like it is storing seconds, so no matter what you enter, it gets truncated to an integer number of seconds.

As for your database, just store the time as an integer, and scale it yourself. You can define it as the number of milliseconds, or 1/10 seconds or whatever you like. Or define it as a decimal field and call it seconds. Then you can deal with the fractional part easily.

rktu · General User Joined: 23 Nov 2006 Posts: 7

Thank for the info acknak,

Gave me the idea to do the following.

minutes field (integer)
Seconds field (integer)
Fraction field (integer)

Then format using the form output.

I dont have to do any calc on the the times just comparisons.

Somtimes you just have to think outside the "box".

I'll let you all know if it works for me.

Regards,
Rob

rktu · General User Joined: 23 Nov 2006 Posts: 7

Hi again,

Was setting up the seperate fields and decidedd to start fooling around with field types.

What I finally tried was setting the field type to "REAL" and then using the mm:ss.00 field format. Guess what it worked and the info recorded is in minutes seconds and hundredths. (ie 00:66.25 will become 01:06.25 when you move to the next record.

The "Date" field type must be based on integers and therfore won't accept fractions.

It's amazing what happens when you fool aroun. Smile

Regards,

Rob.

acknak · Posted: Mon Nov 27, 2006 8:17 am Post subject:

Just be aware that when you do this, again the database is not storing what you may think it's storing. When you input a time like "00:66.25" someone (I'm not sure if it's OOo Base or the database itself) converts this to a fractional number of days: 66.25s / (seconds per day=86400) = 0.000766782407407407... (which appears to have no exact representation in ieee floating point). You can see this if you enter some times, then change the column format to a decimal format.

I honestly can't think of anything you might do with swim meet records that this would cause problems for, but if I were you, I would stick with storing my data in a form as close as possible to the form I actually measure it in, in this case fractional seconds, and worry less about the formatting.