Comparing by Date, Time & Seconds columns
A vendor schema stored timestamps as three columns (DATE, plus SMALLINT HHMM and SMALLINT SSff), making "everything after a given instant" queries awkward. Jonathan Leffler gave the standard cascading comparison: (val_date < item_date) OR (dates equal AND val_time < item_time) OR (dates and times equal AND val_seconds < item_seconds), later posting test SQL verifying it. Others suggested an SPL function to build a DATETIME, or a trigger/extra column, but the DB was a read-only HDR secondary (temp tables failed, likely no temp dbspace configured). The OP confirmed he used Jonathan's WHERE-clause solution.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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 >= '03/12/2010 13:01.000', but I don't know how to do it with these three columns. And, you can't just do WHERE item_date >= '03/12/10' 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@gmail.com> wrote: > 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 > >= '03/12/2010 13:01.000', but I don't know how to do it with these > three columns. And, you can't just do WHERE item_date >= '03/12/10' > 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. > 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@earthlink.net, jleffler@us.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_fractionsecs;
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...@gmail.com> wrote:
> On Fri, Mar 12, 2010 at 14:43, J. Hart <unleashedman...@gmail.com> wrote:
> > 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
> > >= '03/12/2010 13:01.000', but I don't know how to do it with these
> > three columns. And, you can't just do WHERE item_date >= '03/12/10'
> > 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.
>
> 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: jleff...@earthlink.net, jleff...@us.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.
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 values of 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
> From: superboer7@t-online.de
> Subject: Re: Comparing by Date, Time & Seconds columns
> Date: Mon, 15 Mar 2010 01:15:53 -0700
> To: informix-list@iiug.org
>
> 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_fractionsecs;
>
> 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...@gmail.com> wrote:
> > On Fri, Mar 12, 2010 at 14:43, J. Hart <unleashedman...@gmail.com> wrote:
> > > 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
> > > >= '03/12/2010 13:01.000', but I don't know how to do it with these
> > > three columns. And, you can't just do WHERE item_date >= '03/12/10'
> > > 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.
> >
> > 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: jleff...@earthlink.net, jleff...@us.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.
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
_________________________________________________________________
The New Busy is not the old busy. Search, chat and e-mail from your inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID27925::T:WLMTAGL:ON:WL:en-US:WM_HMP:032010_3
Hello Ian,
> Hint: Why do you mod the minutes by 100 when you only have allowable values of 0-59? ;-)
because:
03/12/10 1336 3300 (Ie... 03/12/10 13:36:33.00)
1336 3300 will be 13:36:33.00
and yes one can have
1373 3300 .. which does not fit in a time 73 minutes does not exist;
59 is the limit which causes an exception which will
return "1970-01-01 00:00:00.0"; .
for what the spl is worth the question was howto
SELECT * FROM MYTABLE WHERE EventDateTime
>= '03/12/2010 13:01.000',
which will be:
SELECT * FROM MYTABLE WHERE
convittodatetime(item_date ,item_time ,item_seconds )>= "2010-03-12 13:01:00:.00"
when i read the question i guess that is what was asked???
comments are welcome specially from J.Hart.
Superboer.
On 15 mrt, 13:00, Ian Michael Gumby <im_gu...@hotmail.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 values of 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
>
>
>
> > From: superbo...@t-online.de
> > Subject: Re: Comparing by Date, Time & Seconds columns
> > Date: Mon, 15 Mar 2010 01:15:53 -0700
> > To: informix-l...@iiug.org
>
> > 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_fractionsecs;
>
> > 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...@gmail.com> wrote:
> > > On Fri, Mar 12, 2010 at 14:43, J. Hart <unleashedman...@gmail.com> wrote:
> > > > 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
> > > > >= '03/12/2010 13:01.000', but I don't know how to do it with these
> > > > three columns. And, you can't just do WHERE item_date >= '03/12/10'
> > > > 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.
>
> > > 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: jleff...@earthlink.net, jleff...@us.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.
>
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list
>
> _________________________________________________________________
> The New Busy is not the old busy. Search, chat and e-mail from your inbox.http://www.windowslive.com/campaign/thenewbusy?ocid=PID27925::T:WLMTA...
On Mar 12, 3:43 pm, "J. Hart" <unleashedman...@gmail.com> wrote: > 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>= '03/12/2010 13:01.000', but I don't know how to do it with these > > three columns. And, you can't just do WHERE item_date >= '03/12/10' > 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. 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: > On Mar 12, 3:43 pm, "J. Hart"<unleashedman...@gmail.com> wrote: >> 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>= '03/12/2010 13:01.000', but I don't know how to do it with these >> >> three columns. And, you can't just do WHERE item_date>= '03/12/10' >> 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. > > > > 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. 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
> 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 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:
>
>
> On Fri, Mar 12, 2010 at 14:43, J. Hart <unleashedmaniac@gmail.com
> <mailto:unleashedmaniac@gmail.com>> wrote:
>
> 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
> >= '03/12/2010 13:01.000', but I don't know how to do it with these
> three columns. And, you can't just do WHERE item_date >= '03/12/10'
> 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.
>
>
>
> 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))
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...@spamweary.invalid> wrote: > On 15/03/2010 15:11, J. Hart wrote: > > > > > > > On Mar 12, 3:43 pm, "J. Hart"<unleashedman...@gmail.com> wrote: > >> 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>= '03/12/2010 13:01.000', but I don't know how to do it with these > > >> three columns. And, you can't just do WHERE item_date>= '03/12/10' > >> 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. > > > 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. > > 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- Hide quoted text - > > - Show quoted text - No, I ended up using Jonathan's solution afterall. James
On Mar 16, 9:26 am, jrenaut <jpren...@yahoo.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
>
> 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
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
On Mar 17, 3:32 pm, "J. Hart" <unleashedman...@gmail.com> wrote:
> On Mar 16, 9:26 am, jrenaut <jpren...@yahoo.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.
>
> > 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.
>
> 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"
It strongly suggests you do not have a temp dbspace configured on the
DR secondary.
(And I hope that's a typo in your copying of the error message and not
in the error message itself!)
Jonathan Leffler