FW: How to convert a DATETIME to an integer?

Your can't convert to datetime directly to integer but you can to date. -- This is an exapmple using literal datetimes CREATE TABLE mytable(col1 DATETIME YEAR TO SECOND); INSERT INTO mytable VALUES( DATETIME( 2000-12-16 13:12:00 ) YEAR TO SECOND ); INSERT INTO mytable VALUES( DATETIME( 2000-12-17 23:12:00 ) YEAR TO SECOND ); INSERT INTO mytable VALUES( DATETIME( 2000-12-17 10:10:00 ) YEAR TO SECOND ); INSERT INTO mytable VALUES( DATETIME( 2000-12-18 13:12:00 ) YEAR TO SECOND ); SELECT * FROM mytable WHERE DATE( col1 ) > 36876; SELECT * FROM mytable WHERE DATE( col1 ) > 36875; SELECT * FROM mytable WHERE DATE( col1 ) > 36874; But easier to read to user MDY function instead of using integers SELECT * FROM mytable WHERE DATE( col1 ) > MDY(12,16,2000); But in that manner you can query to the date part of your datetime values. ( See example ) Another comment. If you have index on col1 and use the date function the optimizer does not use the index because you reference through function! If you want to query the time part of the datetime value: SELECT * FROM mytable WHERE EXTEND( col1 , HOUR TO MINUTE) BETWEEN DATETIME( 13:10:00 ) HOUR TO SECOND AND DATETIME( 14:00:00 ) HOUR TOSECOND; I hope it help! -----Eredeti 'zenet----- Felad': mike@excite.com [mailto:mike@excite.com] Elk'ldve: 2000. december 17. 18:08 C'mzett: informix-list@iiug.org T'rgy: Re: How to convert a DATETIME to an integer? 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 Earlier, Csom Gyula <Csom@interface.hu> wrote: > >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 > >-----Original Message----- >From: mike@excite.com [mailto:mike@excite.com] >Sent: Sunday, December 17, 2000 1:01 AM >To: informix-list@iiug.org >Subject: How to convert a DATETIME to an integer? > > >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 >mikemulvaney@excite.com

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

mike@excite.com wrote: > > 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. [snip] I'm embarassed to post this because it's such an abysmal hack, but: create procedure pTimeSlot ( p_dt datetime year to second ) returning int; define v_num_secs interval second(8) to second; define v_secs int; define v_str varchar(10); let v_num_secs = p_dt - datetime(2000-01-01 00:00:00) year to second; -- can't convert interval to int, but can do interval to string ... let v_str = v_num_secs; -- ... and string to int let v_secs = v_str; return v_secs; end procedure; Note that this will only handle dates up to 99999999 seconds from '2000-01-01 00:00:00' (about 3 years...) Chris -- ************************************************************** Chris Hall Email: Chris.Hall@OrbisUK.com Orbis Tel: +44 208 742 1600 http://www.OrbisUK.com Fax: +44 208 742 2649