Stored, Calc, formatting & rounding issue

Classic Servoy
1

Hi,

Got the following Stored Calculation:

if (startdate != null &&
	enddate != null)
{
	return enddate.getTime() - startdate.getTime();
}
return -1;

I display this dataprovider in a field with the following formatting: “# ms”.

Now, when new records are created, the value (usually less than 1000 milliseconds) is displayed just fine (for example “839 ms”, but when I do a sort (or even just open the sort dialog and close it without sorting), or go to designmode and back (in developer offcourse), the values are rounded to whole seconds (so the displayed value shows either 0 ms, 1000 ms, 2000 ms, 3000 ms etc).

Am I doing something wrong, or is this a bug?

Paul

Servoy Developer
Version R2 2.2.4-build 336
Java version 1.5.0_06-b05 (Windows 2000)

2

No one else seeing this behavior?

To me, it really looks like a bug, dunno what els I could do to get it to work properly.

Paul

3

enddate and startdate come from a database?
What is stored exactly in the database?

If you debug what is really the output of that calculation (just use application.output) what is then enddate time and startdate time?

it looks to me those are loosing digits because of the reload from the database?

4

Johan,

The start and enddates are comming from the database:

	forms.batch_test.controller.newRecord(true);
	startdate = new Date();
	input_message = params[6];
	output = plugins.Kydome_WebServices.DynInvokeServerside(params);
	output_message = output;
	enddate = new Date();
	forms.batch_test.controller.saveData();

Note: output is not one of the columns, but a variable, the rest are DB columns.

The startdate and enddate are defined als datetime columns in Servoy.

I put some output into my calc:

if (startdate != null &&
	enddate != null)
{
application.output('StartDate: '+startdate+'\nEndDate: '+enddate)
application.output('StartDate: '+startdate.getTime()+'\nEndDate: '+enddate.getTime())
application.output('StartDate: '+startdate.getMilliseconds()+'\nEndDate: '+enddate.getMilliseconds())
	return enddate.getTime() - startdate.getTime();
}
return -1;

And see the following:
On Record creation:
StartDate: Tue May 09 13:19:29 CEST 2006
EndDate: Tue May 09 13:19:30 CEST 2006
StartDate: 1147173569316
EndDate: 1147173570191
StartDate: 316
EndDate: 191

After an action, for example sort:
StartDate: Tue May 09 13:19:29 CEST 2006
EndDate: Tue May 09 13:19:30 CEST 2006
StartDate: 1147173569000
EndDate: 1147173570000
StartDate: 0
EndDate: 0

So, I think it’s save to say that the milliseconds get lost when a date is queried from the DB.

Paul

5

exactly and what happens if you look into the database with another tool? What does it say?

Can you also debug with Tracing output what servoy really send to the database when it inserts the record?

6

Dug a bit deeper: Apparently, Oracle Date fields do not support fractional seconds, whereas most other DB’s do.

The “workaround” for this is not to use the Date type, but the TimeStamp type to store dates in Oracle, as that type allows for fractional seconds.

Paul

BTW: When you create tables through Servoy in an Oracle DB, columns of type DateTime are created with Oracle Type = Date

7

please report this in oure support system.

Oracle does handle dates very strange anyway, they don’t follow the spec at all.