How to convert a DATETIME to an integer?

[mi...@excite.com]

This can't be that hard. I've looked all over, but I can't figure out
how to do this simple task.

How do I convert a DATETIME to an integer, for use in the WHERE clause
of a SELECT statement?

Basically, I want to do something like:

SELECT * FROM table
WHERE INTEGER(some_datetime) / 1234 > 5678;

Actually, it is more complicated than that, but if I could get that
far, I can do the rest.

I want the integer to be number of seconds since some time in the
past, like January 1, 1970 0:00. I don't really care what the date
from the past is, as long as it always uses the same one.

The "Informix Guide to SQL" says: "The database server stores the
internal format of the DATE or DATETIME column as an integer."

How do I get at that integer?

Thanks,
Mike
mikemu...@excite.com

[Csom Gyula]

You may use this syntax to cast DATE to INT:

col_name::INT

For instance:

CREATE TABLE mytable(col1 DATE);

INSERT INTO mytable VALUES('12/15/2000');
INSERT INTO mytable VALUES('12/17/2000');
INSERT INTO mytable VALUES('12/21/2000');

SELECT col1 date, col1::INT date_as_int
FROM mytable
WHERE col1::INT <= 36876;
SELECT UNIQUE TODAY today, TODAY::INT today_as_int
FROM mytable;

Csomi

[mi...@excite.com]

Hmm, this isn't working for me. Should this work for a DATETIME as
well as a DATE? I don't have a DATE field to test it on.

Also, I am using Informix 7.3. Should that matter?

The error I get is -201: Syntax error.

Thanks,
Mike

[allenj]

Yes, I think your going to need a 9.X engine to do a cast like Csomi
specifies.

I have not had a need to go from DATE to INT, but have needed to
go in the other direction.

Here's what I did:

CREATE PROCEDURE humantime(p_unixtime INT)
RETURNING CHAR(255) ;

DEFINE vhumantime CHAR(255);

--SET DEBUG FILE TO "/tmp/trace.sp";
--TRACE ON ;

LET vhumantime = "" ;
-- We have to subtract 14400 (4 hours) to adjust for gmt
-- The " VERSION" bit ensures that we get back exactly 1 row
SELECT datetime(1970-01-01 00:00:00) YEAR TO SECOND +
(p_unixtime - 14400) UNITS SECOND
into vhumantime
from systables
where tabname = " VERSION" ;

RETURN vhumantime ;

--TRACE OFF ;
END PROCEDURE

Maybe this will help you get started on the opposite function.

Allen W. Jantzen, DBA
Ned Davis Research

[Jonathan Leffler]

mi...@excite.com wrote:
>
> This can't be that hard. I've looked all over, but I can't figure out
> how to do this simple task.
>
> How do I convert a DATETIME to an integer, for use in the WHERE clause
> of a SELECT statement?
>
> Basically, I want to do something like:
>
> SELECT * FROM table
> WHERE INTEGER(some_datetime) / 1234 > 5678;
>
> Actually, it is more complicated than that, but if I could get that
> far, I can do the rest.
>
> I want the integer to be number of seconds since some time in the
> past, like January 1, 1970 0:00. I don't really care what the date
> from the past is, as long as it always uses the same one.

So, you want the stored procedure unixtime():

# "@(#)$Id: unixtime.spl,v 1.1 1998/09/24 20:15:24 jleffler Exp $"
#
# Stored procedure TO_UNIX_TIME written by Jonathan Leffler
# (jlef...@informix.com). You need to worry about the time zone
# where the database server is running -- the value of CURRENT is
# determined by that, and you need to compensate for it.

CREATE PROCEDURE to_unix_time(d DATETIME YEAR TO SECOND) RETURNING
INTEGER;
DEFINE n INTEGER;
DEFINE i1 INTERVAL DAY(9) TO DAY;
DEFINE i2 INTERVAL SECOND(6) TO SECOND;
DEFINE s1 CHAR(10);
DEFINE s2 CHAR(10);
LET i1 = EXTEND(d, YEAR TO DAY) - DATETIME(1970-01-01) YEAR TO DAY;
LET s1 = i1;
LET i2 = EXTEND(d, HOUR TO SECOND) - DATETIME(0) SECOND TO SECOND;
LET s2 = i2;
LET n = s1 * (24 * 60 * 60) + s2;
RETURN n;
END PROCEDURE;

Note that this works over a greater range of values than the simpler
alternative which just subtracts 1970-01-01 00:00:00 from d; that
variant breaks in September 2001 (exercise for the reader: why?). As
you might guess from the modification time on the file, this has been
posted to c.d.i before; it might even be available somewhere in the
c.d.i software archive.

> The "Informix Guide to SQL" says: "The database server stores the
> internal format of the DATE or DATETIME column as an integer."

You should probably report an erratum to d...@informix.com. If you are
dealing with DATE, then there is a stored integer value. At one (rather
low level), if you are dealing with a DATETIME without a fractional
second part, then there is also an integer stored in the database, but
it is stored in a decimal structure. The two stored values are also
quite different. I would never have chosen to call the integer stored
for DATETIME YEAR TO SECOND an integer without that piece of prompting.

> How do I get at that integer?
>
> Thanks,
> Mike
> mikemu...@excite.com

--
Yours,
Jonathan Leffler (Jonathan...@Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
"I don't suffer from insanity; I enjoy every minute of it!"

[allenj]

Jonathan

> You need to worry about the time zone

> where the database server is running -- the value of CURRENT is

> determined by that, and you need to compensate for it.

Could you be a bit more specific here?
How do I compensate for the time zone?

When I 'select CURRENT from wherever' I get the correct date/time.

??
Allen

[Jonathan Leffler]

allenj wrote:
> > You need to worry about the time zone
> > where the database server is running -- the value of CURRENT is
> > determined by that, and you need to compensate for it.
>
> Could you be a bit more specific here?
> How do I compensate for the time zone?

Maybe you don't need to. However, if you are accessing the database in
California from both England and Singapore, you have to worry about the
values that are calculated by the database server as CURRENT versus the
values that are calculated by the clients in the other (8 hours off
California time) zones.

Somewhere along the line, you have to worry about whether to add or
subtract 8 * 60 * 60 seconds from the values, and which values, and
when. I don't have more specific answers; I don't have a database
server running in a different time zone to play with. I suppose I could
start one with an alternative time zone and see what it takes to run
into problems -- but so perhaps could you. Or, indeed, simply move the
client software to a new timezone by setting TZ=GMT0BST or TZ=PST8PDT,
and then exploring what you get, both in the adjusted time zone and the
regular time zone. I set my timezone to GMT (UTC) and then did:

SQL[1803]: create table timestamp (now datetime year to second not
null);
SQL[1804]: insert into timestamp values(current year to second);
SQL[1805]: select * from timestamp;
2000-12-19 11:02:44
SQL[1806]: !date;
Tue Dec 19 19:03:01 GMT 2000
SQL[1807]: insert into timestamp values(datetime(2000-12-19 19:04:59)
year to second);
SQL[1808]: select * from timestamp;
2000-12-19 11:02:44
2000-12-19 19:04:59
SQL[1809]:

So, the current time was 19:02 in England, but the database server in
California inserted the value 11:02 into the database. Which value is
correct? You have to worry about this behaviour, and ensure that the
values inserted into the database are either identified with a timezone
or corrected to a standard timezone (eg UTC)
in the database and corrected to the user's local timezone when the
result is reported.

> When I 'select CURRENT from wherever' I get the correct date/time.

Are you in the same timezone as your database server? If so, you don't
need to worry about it. If you are not, then you do need to consider
whether it will be a problem, and establish that it will be, or won't
be, a problem.

> Jonathan Leffler wrote:
> >
> > So, you want the stored procedure unixtime():
> >
> > # "@(#)$Id: unixtime.spl,v 1.1 1998/09/24 20:15:24 jleffler Exp $"
> > #
> > # Stored procedure TO_UNIX_TIME written by Jonathan Leffler
> > # (jlef...@informix.com). You need to worry about the time zone
> > # where the database server is running -- the value of CURRENT is
> > # determined by that, and you need to compensate for it.
> >
> > CREATE PROCEDURE to_unix_time(d DATETIME YEAR TO SECOND) RETURNING
> > INTEGER;
> > DEFINE n INTEGER;
> > DEFINE i1 INTERVAL DAY(9) TO DAY;
> > DEFINE i2 INTERVAL SECOND(6) TO SECOND;
> > DEFINE s1 CHAR(10);
> > DEFINE s2 CHAR(10);
> > LET i1 = EXTEND(d, YEAR TO DAY) - DATETIME(1970-01-01) YEAR TO DAY;
> > LET s1 = i1;
> > LET i2 = EXTEND(d, HOUR TO SECOND) - DATETIME(0) SECOND TO SECOND;
> > LET s2 = i2;
> > LET n = s1 * (24 * 60 * 60) + s2;
> > RETURN n;
> > END PROCEDURE;

--