Using the 'utc_to_datetime' Option

The 'utc_to_datetime' option of the DBINFO function returns the UTC seconds to DATETIME value that the server would generate if the UNIX™ time( ) system call returned the value of the second parameter, taking into account the time zone of the database server.

The 'utc_to_datetime' option casts to a DATETIME value its last argument, which must be a numeric expression representing a Coordinated Universal Time (UTC) value. If this evaluates to a number with a fractional part, any fractional seconds are ignored.

In the first example below, the last argument is a UTC value represented as a literal integer. In the second example, the last argument is a column expression specifying an integer column that stores UTC values. In both examples, DBINFO casts the UTC value to a DATETIME value in the time zone of the database server:

DBINFO ('utc_to_datetime', 1299912999 )

DBINFO ('utc_to_datetime', timesheet.utc_checkin )

If the value of the last argument is negative, the function returns a DATETIME value from an earlier UNIX epoch, as in the next example:

SELECT DBINFO("utc_to_datetime", -2134567890.91234) 
   FROM 'sysmaster:"informix".sysdual';

This query returns the DATETIME value 1902-05-12 08:28:30.

These example times all assume that the server is in a specific time zone. The following query returns four DATETIME values:

SELECT
    DBINFO('utc_to_datetime', -32767) AS min_smallint,
    DBINFO('utc_to_datetime', +32767) AS max_smallint,
    DBINFO('utc_to_datetime', 1299912999),
    DBINFO("utc_to_datetime", -2134567890.91234)
FROM 'sysmaster:"informix".sysdual';

These are the returned DATETIME values from a server in the United States Pacific time zone:

1969-12-31 06:53:53   1970-01-01 01:06:07   2011-03-11 22:56:39 
  1902-05-12 01:28:30
		# Server running in TZ=US/Pacific

These are the returned DATETIME values from the same query from a server in the UTC0 time zone:

1969-12-31 14:53:53   1970-01-01 09:06:07   2011-03-12 06:56:39 
  1902-05-12 08:28:30
		# Server running in TZ=UTC0

Note that the DAY component in the third DBINFO result is different for the United States Pacific time zone and for the UTC0 time zone, because of the 8-hour offset between those two time zones.

The database server time zone can similarly affect the return value from other expressions for points in time, such as CURRENT, SYSDATE, and TODAY, whose DATETIME YEAR TO SECOND or DATE representation depends on the time zone of the server.