Re: Date question?
On Tue, 15 Dec 1998, Terry Haroldson wrote:
> I'm not that familiar with Informix, but trying to evaluate some data
> from it. I have a field that is supposed to be a date and seems to be
> defined as integer. But I don't know how to really convert it to a
> useable date. A couple of examples are:
>
> 909576453
> 912859221
>
> I'm not sure if the d/b designer set up their own way to evaluate this
> as a date, or if it is a standard Informix format.
It isn't a standard Informix format. They look like Unix timestamps:
$ timestamp -u 909576453 912859221909576453 = Wed Oct 28 12:07:33 1998
912859221 = Sat Dec 05 12:00:21 1998
$
Those are the UTC (GMT) values. (No, timestamp is my program, not
widely available because most people don't need it -- ask, and it shall
be given unto you, 'cos 'tis the season...)
> If these numbers mean a date to anyone, could you let me
> know......appreciate it....Terry.
You can convert those into Informix DATETIME YEAR TO SECOND values by
using:
SQL[16]: CREATE PROCEDURE unix_to_informix(i INTEGER)
> RETURNING DATETIME YEAR TO SECOND;
> DEFINE d DATETIME YEAR TO SECOND;
> LET d = DATETIME(1970-01-01 00:00:00) YEAR TO SECOND + i UNITS SECOND;
> RETURN d;
> END PROCEDURE;
SQL[17]: EXECUTE PROCEDURE unix_to_informix(909576453);
1998-10-28 12:07:33
SQL[18]: EXECUTE PROCEDURE unix_to_informix(912859221);
1998-12-05 12:00:21
SQL[19]:
Yours,
Jonathan Leffler (jleffler@informix.com) #include <witticism.h>
Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN
Informix IDN for D4GL & Linux -- http://www.informix.com/idn
PS: Oh, the SQL command interpreter is SQLCMD -- check out IIUG web site
for a very old version.