We have a vendor product and in their informix database, they have
their date, time and seconds separated in 3 different columns.

item_date (date field)
item_time (smallint field)
item_seconds (smallint field)

Sample data:
03/12/10 41 1303 (Ie... 03/12/10 00:41:13.03)
03/12/10 330 4900 (Ie... 03/12/10 03:30:49.00)
03/12/10 710 1001 (Ie... 03/12/10 07:10:10.01)
03/12/10 1336 3300 (Ie... 03/12/10 13:36:33.00)

My question is... I want to be able to pass in a Date, Time and
Seconds and only get records after that specific time. In a normal
database, I would just say, SELECT * FROM MYTABLE WHERE EventDateTime
and item_time >= '1244' and item_seconds >= '1100'

A guy I work with said I should try to convert the tree columns to a
julian date and then compare the julian dates.

Thanks for any help you can provide.

On Fri, Mar 12, 2010 at 14:43, J. Hart <unleashedmaniac (AT) gmail (DOT) com> wrote:


Ugh. Talk about deliberately making everyone's life hard!

Assuming the parameters are value_date, value_time and value_seconds, the
basic structure of the condition is:

WHERE ((value_date < item_date) OR
(value_date = item_date AND value_time < item_time) OR
(value_date = item_date AND value_time = item_time AND
value_seconds < item_seconds))

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler (AT) earthlink (DOT) net, jleffler (AT) us (DOT) ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.

i would build an spl for it... it aint pretty check the manual for
improvements..., here goes;
CREATE FUNCTION convittodatetime( thedate DATE, thehourminute
SMALLINT,
thesecconds SMALLINT)
RETURNING datetime YEAR TO FRACTION(2)
WITH (not variant);

DEFINE f_hours SMALLINT;
DEFINE c_hours CHAR(2);
DEFINE f_minutes smallint;
DEFINE c_minutes CHAR(2);
DEFINE f_secs SMALLINT;
DEFINE c_secs CHAR(2);
DEFINE f_fractionsecs SMALLINT;
DEFINE c_fractionsecs CHAR(2);
DEFINE f_datepiece char(10);
DEFINE retvalue DATETIME YEAR TO FRACTION(2);

ON EXCEPTION
RETURN "1970-01-01 00:00:00.0";
END EXCEPTION;

LET f_datepiece = thedate :: DATETIME YEAR TO DAY :: char(20);

LET f_minutes = mod(thehourminute,100);
IF f_minutes > 9 THEN
LET c_minutes = f_minutes;
ELSE
LET c_minutes = "0" || f_minutes;
END IF

LET f_hours = (thehourminute - f_minutes ) / 100;
IF f_hours > 9 THEN
LET c_hours = f_hours;
ELSE
LET c_hours = "0"|| f_hours;
END IF


LET f_fractionsecs=mod(thesecconds,100);
IF f_fractionsecs > 9 THEN
LET c_fractionsecs =f_fractionsecs ;
ELSE
LET c_fractionsecs = "0" || f_fractionsecs ;
END IF

LET f_secs = (thesecconds - f_fractionsecs) /100;
IF f_secs > 9 THEN
LET c_secs = f_secs;
ELSE
LET c_secs = "0"|| f_secs;
END IF

LET retvalue = f_datepiece || " "||
c_hours||":"||c_minutes||":"||c_secs||"."||c_fract ionsecs;

RETURN retvalue;
END FUNCTION;

create table tx ( a date, b smallint, c smallint);
insert into tx values (today,1301,1501);
insert into tx values (today,101,501);


select * from tx where convittodatetime (a,b,c) > current ;
-- gives one row... assume you run this in the morning...
select * from tx where convittodatetime(a,b,c) < current ;
-- gives one row... assume you run this in the morning...

You could also use the spl above to build an index ...
Oh and if there is a mistake in the data it does return
"1970-01-01 00:00:00.0";


Superboer.


On 13 mrt, 05:13, Jonathan Leffler <jleffler.i... (AT) gmail (DOT) com> wrote:

Huh?

Ok, so the first question... can you alter the table and add a timestamp and an after update trigger? Assuming an insert would also trigger the update trigger to fire?

Then run a simple update statement that updates the columns and builds a timestamp based on the 3 values?
Now you have a simple way to do the queries?

Granted the OP said that this was a vendor written application. (Most likely did this brain dead thing because they were trying to write something that wasn't vendor specific...) So if you have support from a 3rd party vendor, the next time they alter their schema, you may lose the triggers and altered table.

But thats the general idea.

With respect to your spl?
I'd check it. You make it more difficult that you should. ;-)
Hint: Why do you mod the minutes by 100 when you only have allowable valuesof 0-59? ;-)

Granted I don't play with Informix that much anymore, so what do I know?
(Hint: I'd do an initial value check on allowable range and then build the timestamp. ;-)

-G

__________________________________________________ _______________
The New Busy is not the old busy. Search, chat and e-mail from your inbox..
http://www.windowslive.com/campaign/...M_HMP:032010_3

On Mar 12, 3:43*pm, "J. Hart" <unleashedman... (AT) gmail (DOT) com> wrote:


Oh, I forgot to add in that the informix database is a read only DR
server. So, we are not able to alter the table structure, create
stored procedures/functions or temp tables... It's a pain in the neck
dealing with the vendor and informix. I wish there was an easy
solution for replicating informix to MS SQL.

James

On 15/03/2010 15:11, J. Hart wrote:
The problem is your database not your DBMS. I expect Jonathan Leffler's
solution would apply to that database structure regardless of whether it
was implemented using IBM Informix IDS or Microsoft SQL server.

Are you having some problem applying Jonathan's solution?

--
RGB

As somewhat of a side note, if you have a temporary dbspace that was
created with the -t option, then even on a read only server of a HDR
pair, you can still create/use temporary tables. You just need to
ensure they are built with the "with no log" option and they should
work fine.

Jacques Renaut
IBM Informix Advanced Support
APD Team

On 3/12/10 8:13 PM, Jonathan Leffler wrote:
FWIW, after posting that, I double checked that it was correct using
this code. The second query reverses the condition.

BEGIN WORK;
CREATE TABLE item_info
(
item_date DATE NOT NULL,
item_time SMALLINT NOT NULL CHECK(item_time >= 0 AND item_time
< 2400 AND MOD(item_time, 100) < 60),
item_seconds SMALLINT NOT NULL CHECK(item_seconds >= 0 AND
item_seconds < 6000)
);
INSERT INTO item_info VALUES('2010-03-14', 0530, 4223);
INSERT INTO item_info VALUES('2010-03-14', 2130, 0);

CREATE TABLE value_info
(
value_date DATE NOT NULL,
value_time SMALLINT NOT NULL CHECK(value_time >= 0 AND
value_time < 2400 AND MOD(value_time, 100) < 60),
value_seconds SMALLINT NOT NULL CHECK(value_seconds >= 0 AND
value_seconds < 6000)
);

INSERT INTO value_info VALUES('2010-03-13', 0530, 4223);
INSERT INTO value_info VALUES('2010-03-14', 0, 0);
INSERT INTO value_info VALUES('2010-03-14', 1200, 0);
INSERT INTO value_info VALUES('2010-03-14', 0529, 0);
INSERT INTO value_info VALUES('2010-03-14', 0530, 0);
INSERT INTO value_info VALUES('2010-03-14', 0530, 4222);
INSERT INTO value_info VALUES('2010-03-14', 0530, 4223);
INSERT INTO value_info VALUES('2010-03-14', 0530, 4224);
INSERT INTO value_info VALUES('2010-03-14', 0530, 5821);
INSERT INTO value_info VALUES('2010-03-14', 0531, 0);
INSERT INTO value_info VALUES('2010-03-15', 0, 0);

SELECT * FROM item_info CROSS JOIN value_info
WHERE ((value_date < item_date) OR
(value_date = item_date AND value_time < item_time) OR
(value_date = item_date AND value_time = item_time AND
value_seconds < item_seconds))
;

SELECT * FROM item_info CROSS JOIN value_info
WHERE ((value_date > item_date) OR
(value_date = item_date AND value_time > item_time) OR
(value_date = item_date AND value_time = item_time AND
value_seconds > item_seconds))
;

ROLLBACK WORK;

On Mar 16, 3:03*am, RedGrittyBrick <RedGrittyBr... (AT) spamweary (DOT) invalid>
wrote:


No, I ended up using Jonathan's solution afterall.

James

On Mar 16, 9:26*am, jrenaut <jpren... (AT) yahoo (DOT) com> wrote:

Hey Jacques,

I tried this...

CREATE TEMP TABLE tab2 (fname CHAR(15), lname CHAR(15))
WITH NO LOG

and got the following error: "Could not open or crate a temporary
file. ISAM error: operation illegal on a DR Secondary"

James