RE: FW: How to convert a DATETIME to an integer?
Why not solve it the way it's supposed to be solved?
create table foo2
(col1 datetime year to second);
insert into foo2 select current year to second from systables where tabid=1;
select * from foo2 where col1 < current year to second + 2000 units second;
col1
2000-12-18 10:27:32
cheers
j.
> -----Original Message-----
> From: mike@excite.com [mailto:mike@excite.com]
> Sent: Sunday, December 17, 2000 7:46 PM
> To: informix-list@iiug.org
> Subject: Re: FW: How to convert a DATETIME to an integer?
>
>
> Earlier, Wolf P'ter <peter.wolf@capsys.hu> wrote:
> >
> >
> >Your can't convert to datetime directly to integer but you
> can to date.
>
> Ouch. I'm not sure how to do what I want to do without converting the
> datetime to an integer.
>
> Here is the problem that I am trying to solve. There are two items of
> information in the database: first_run (a DATETIME) and period (an
> integer representing a number of minutes. These two things define a
> repeating task. for example, I might have a weekly task that runs
> every Monday morning at 3:00 AM. For this one, the first_run is
> "12-4-2000 3:00" and period is (60 minutes * 24 hours * 7 days) =
> 10080.
>
> Now here is the function I am trying to write. Given two datetimes,
> date1 and date2, I want to find out if the given task will execute
> between those two times. So for my example above, if the two times
> are "12-11-2000 3:00" and "12-11-2000 4:00" (12-11 is a monday, too),
> then the function should return "true". But if the times are
> "12-26-2000 2:00" and "12-29-2000 12:00" (A Tuesday through a Friday),
> then it should return false.
>
> Basically, date1 and date2 define a window; I want to find all the
> tasks that should execute in that window. I don't care if a task
> should execute more than once; all I want to know is if it would
> execute at all.
>
> So here is where converting a datetime to an integer comes into play.
> Using my variables from above, I want to find an integer n that makes
> this statement true:
>
> (date1 - first_time) / period <= n <= (date2 - first_time) / period
>
> Where date1, first_time, and date2 are all integer representations of
> the respective dates. Note that I am not looking for an INTERVAL,
> really. But if I can convert the INTERVAL to an integer before
> dividing by the period, that would be cool too.
>
> I don't care how big the gap is, or anything like that. I just care
> if there is an integer in there.
>
> Quick aside: I'm not much of a database programmer. There might be a
> clever way to do this in sql, which would be great. I implemented
> this algorithm in perl, and it works fine. But it is slow, because I
> have to get everything out of the database and then deal with it in
> perl. Anyway, back to the story.
>
> So the way to determine if there is an integer or not is to do the
> math for the two ends of the equation, and then TRUNC them both.
> After the TRUNC, if part2 is greater than part1, then there is an
> integer in between.
>
> I'm pretty sure I can do the TRUNC. I'm having trouble getting the
> integers before I can even do the subtraction/division, though.
>
> Thanks for any help,
> Mike
> mikemulvaney@excite.com
>
>
>