#!/bin/sh # # This is a shell archive. To extract its contents, # execute this file with /bin/sh to create the file(s): # # frunixtime.spl tounixtime.spl unixtime_README # # This shell archive created: Thu Sep 26 10:40:53 EDT 2002 # echo "Extracting file unixtime_README" sed -e 's/^X//' <<\SHAR_EOF > unixtime_README XThis is a FAQ. There are answers available from http://groups.google.com/; Xthe code should be available at the IIUG web site. ...well...I thought Xthere was... The inverse function, to_unix_time() certainly is out there! X XOK; here's a brand new stored procedure, FROM_UNIX_TIME(), that takes a XDECIMAL value (to allow for fractional seconds; if you pass an integer, it Xwill be converted automatically) and spits out a DATETIME YEAR TO XFRACTION(5). And there's an upgraded TO_UNIX_TIME() that also handles Xfractional seconds. X X XSam Gentsch wrote: X X> Is there a function in SQL to change a integer which is X> actually an Epoch or UNIX time to a human readable date? X> (I have searched documentation and the web with no luck) X> X> The design was before my time, not sure why they chose X> not to let Informix handle the date. X> X> If it matters, IDS 7.30 on Solaris 7 or 9.30 on Solaris 8. X> X> Sam Gentsch X X X-- XJonathan Leffler (jleffler@us.ibm.com) XSTSM, Informix Database Engineering, IBM Data Management Solutions X4100 Bohannon Drive, Menlo Park, CA 94025 X "I don't suffer from insanity; I enjoy every minute of it!" SHAR_EOF if [ `wc -c < unixtime_README` -ne 1124 ] then echo "Lengths do not match -- Bad Copy of unixtime_README" fi echo "Extracting file frunixtime.spl" sed -e 's/^X//' <<\SHAR_EOF > frunixtime.spl X{ X# "@(#)$Id: frunixtime.spl,v 1.2 2002/09/25 18:10:48 jleffler Exp $" X# X# Stored procedure FROM_UNIX_TIME written by Jonathan Leffler X# (jleffler@us.ibm.com) as counterpart to TO_UNIX_TIME. X# X# If you run this procedure with no arguments (use the default), you X# need to worry about the time zone the database server is using because X# the value of CURRENT is determined by that, and you need to compensate X# for it if you are using a different time zone. X# X# Note that this version works for dates after 2001-09-09 when the X# interval between 1970-01-01 00:00:00+00:00 and current exceeds the X# range of INTERVAL SECOND(9) TO SECOND. Accepting DECIMAL(18,5) allows X# it to work for all valid datetime values including fractional seconds. X# In the UTC time zone, the 'Unix time' of 9999-12-31 23:59:59 is X# 253402300799 (12 digits); the equivalent for 0001-01-01 00:00:00 is X# -62135596800 (11 digits). The integer part of both these values is X# unrepresentable in a 32-bit integer, of course, so most Unix systems X# won't handle this range, and the so-called 'Proleptic Gregorian X# Calendar' used to calculate the dates ignores locale-dependent details X# such as the loss of days that occurred during the switch between the X# Julian and Gregorian calendar, but those are minutiae that most people X# can ignore most of the time. X} X XCREATE PROCEDURE from_unix_time(v DECIMAL(18,5) DEFAULT 0) X RETURNING DATETIME YEAR TO FRACTION(5); X DEFINE n DATETIME YEAR TO FRACTION(5); X DEFINE i1 INTEGER; X DEFINE i2 DECIMAL(11,5); X LET i1 = v / (24 * 60 * 60); X LET i2 = v - (i1 * 24 * 60 * 60); X LET n = DATETIME(1970-01-01 00:00:00.00000) YEAR TO FRACTION(5); X LET n = n + i1 UNITS DAY; X LET n = n + i2 UNITS FRACTION(5); X RETURN n; XEND PROCEDURE; SHAR_EOF if [ `wc -c < frunixtime.spl` -ne 1783 ] then echo "Lengths do not match -- Bad Copy of frunixtime.spl" fi echo "Extracting file tounixtime.spl" sed -e 's/^X//' <<\SHAR_EOF > tounixtime.spl X{ X# "@(#)$Id: tounixtime.spl,v 1.6 2002/09/25 18:10:48 jleffler Exp $" X# X# Stored procedure TO_UNIX_TIME written by Jonathan Leffler (previously X# jleffler@informix.com and now jleffler@us.ibm.com). Includes fix for X# bug reported by Tsutomu Ogiwara on X# 2001-07-13. Previous version used DATETIME(0) SECOND TO SECOND X# instead of DATETIME(0:0:0) HOUR TO SECOND, and when the calculation X# extended the shorter constant to DATETIME HOUR TO SECOND, it added the X# current hour and minute fields, as documented in the Informix Guide to X# SQL: Syntax manual under EXTEND in the section on 'Expression'. X# Amended 2002-08-23 to handle 'eternity' and annotated more thoroughly. X# Amended 2002-09-25 to handle fractional seconds, as companion to the X# new stored procedure FROM_UNIX_TIME(). X# X# If you run this procedure with no arguments (use the default), you X# need to worry about the time zone the database server is using because X# the value of CURRENT is determined by that, and you need to compensate X# for it if you are using a different time zone. X# X# Note that this version works for dates after 2001-09-09 when the X# interval between 1970-01-01 00:00:00+00:00 and current exceeds the X# range of INTERVAL SECOND(9) TO SECOND. Returning DECIMAL(18,5) allows X# it to work for all valid datetime values including fractional seconds. X# In the UTC time zone, the 'Unix time' of 9999-12-31 23:59:59 is X# 253402300799 (12 digits); the equivalent for 0001-01-01 00:00:00 is X# -62135596800 (11 digits). Both these values are unrepresentable in X# 32-bit integers, of course, so most Unix systems won't handle this X# range, and the so-called 'Proleptic Gregorian Calendar' used to X# calculate the dates ignores locale-dependent details such as the loss X# of days that occurred during the switch between the Julian and X# Gregorian calendar, but those are minutiae that most people can ignore X# most of the time. X} X XCREATE PROCEDURE to_unix_time(d DATETIME YEAR TO FRACTION(5) X DEFAULT CURRENT YEAR TO FRACTION(5)) X RETURNING DECIMAL(18,5); X DEFINE n DECIMAL(18,5); X DEFINE i1 INTERVAL DAY(9) TO DAY; X DEFINE i2 INTERVAL SECOND(6) TO FRACTION(5); X DEFINE s1 CHAR(15); X DEFINE s2 CHAR(15); X LET i1 = EXTEND(d, YEAR TO DAY) - DATETIME(1970-01-01) YEAR TO DAY; X LET s1 = i1; X LET i2 = EXTEND(d, HOUR TO FRACTION(5)) - X DATETIME(00:00:00.00000) HOUR TO FRACTION(5); X LET s2 = i2; X LET n = s1 * (24 * 60 * 60) + s2; X RETURN n; XEND PROCEDURE; SHAR_EOF if [ `wc -c < tounixtime.spl` -ne 2554 ] then echo "Lengths do not match -- Bad Copy of tounixtime.spl" fi echo "Done." exit 0