Fernando Nunes (see his blog at http://informix-technology.blogspot.com) send me a note on my last blog entry. He said to look at the dbinfo function to solve this problem. Spokey Wheeler basically said the same thing (see his blog at
http://www.informix-zone.com/blog/593).

Fernando mentioned that there is dbinfo('utc_to_datetime', ...) that provides the same translation I did in the SPL code. The thing is that it does not provide quite the same translation. The difference is that it looks at the system timezone and takes it into consideration in the conversion.

If we use the dbinfo function, we can re-write the from_unixtime function as follows:

CREATE FUNCTION from_unixtime(secs int)

RETURNING datetime year to second

WITH (NOT VARIANT)



return(dbinfo('utc_to_datetime', secs));

END FUNCTION;

This of course raise a question about the epoch (unix_timestamp) function that should take into consideration the timezone. We can either hardcode the timezone in the function calculation or use dbinfo('get_tz'). As strange at it sounds, I'd have a tendency to go with the hardcoded calculation because dbinfo('get_tz') simply returns the value of the TZ environment variable set in the session. I don't find that dependable enough. Of course, all this would be easily solved if we used a "C" user-defined routine.

Till next time