Why CURRENT returns zero's in fraction part of the Datetime Year to Fraction?

Answer

PROBLEM

The value for function CURRENT DATETIME YEAR TO FRACTION is expressed by the IBM Informix Dynamic Server instance with fraction value in zero:

Example:

CREATE TABLE mytable (id serial, mydate datetime year to fraction);

INSERT INTO mytable VALUES (0, current year to fraction);

   
id mydate

   1 2005-03-16 07:26:15.000

CURRENT function returning zero for fraction:

How to make the fraction value different from zero?

SCOPE

The following products and operating systems have the same behavior:

Product Name	Product Version(s)	Hardware Vendor	Operating System
IBM Informix® Dynamic Server	All	All	All
IBM Informix® Extended Parallel Server	All	All	All

CAUSE

By default, the database server has ONCONFIG parameter USEOSTIME set to 0, and so, as expected behavior, CURRENT function returns a zero (.000) for the FRACTION field of a DATETIME YEAR TO FRACTION data type.

When precision of one (1) second is enough for your applications, set the ONCONFIG parameter USEOSTIME to 0, and the IBM Informix Dynamic server will retrieve the current datetime from the Operating System once per second.


SOLUTION

Setting the ONCONFIG parameter USEOSTIME to 1 specifies that the database server is to use subsecond precision when it obtains the current time from the operating system to satisfy the CURRENT clause in SQL statements.

IBM Informix Dynamic Server (IDS) instance has to be restarted for a change in USEOSTIME to take effect.

The reason for this change lies in the following facts:

Multiple applications in the engine require a time. For example, the time at what a user logged into the system, the time that a thread started to wait on a condition, etc. All these events use a datetime field stored in shared memory that is refreshed every second by the Timer Virtual Processor (Timer VP) using the time system call. The time system call has a precision of seconds.

When USEOSTIME is set to 0, IDS returns the time for the CURRENT clause using the latest time value that was stored in shared memory by the Timer VP. Since the Timer VP uses the time system call, no sub-second precision is available with this method, but is very fast as this value is in the shared memory.

When USEOSTIME is set to 1, IDS gets the sub-second precision time from the operating system every time a user requires the CURRENT date, and since it is a system call, there is an overhead that might impact in performance in 4-5% compared to USEOSTIME turned off.

For further reference, consult this manual:

IBM Informix Dynamic Server Administrator’s Reference

Example:

When having USEOSTIME ONCONFIG parameter set to 1:

CREATE TABLE mytable (id serial, mydate datetime year to fraction);

INSERT INTO mytable VALUES (0, current year to fraction);

   
id mydate

   1 2007-03-16 07:26:15.612