Interval Question
Good morning everyone, I have been tasked with converting a SQL Server (Vers. 7) database to Informix. The data conversion was sloppy but is now complete. There are many stored procedures and I am rewriting them for Informix. One of them has an expression, as part of the where clause, that specifies to only get rows where a timestamp column is less than the current time plus a time zone adjustment value. The original SQL Server expression is: ... and ra.endtimestamp < dateadd(hh, w.timezoneadjustment, getdate()) ... I thought that the equivalent Informix SPL expression would be: ... and ra.endtimestamp < datetime(CURRENT) year to fraction(3) + INTERVAL (w.timezoneadjustment) hour to hour ... but that reutrns a syntax error. Another related question: Will the CURRENT keyword return the current system time or only the time that was current at compilation? If it is the latter, does someone have a handy way to get the current system time in a stored procedure (a system call perhaps?). Here is some pertinent information: HP-UX 11.0 IDS 9.21.FC4XQ ra.endtimestamp is datetime year to fraction(3) w.timezoneadjustment is integer Does anyone know if what I am doing is allowed in stored procedures. Am I properly converting this expression? Any help would be appreciated. Thanks Rob Schmitz 913-345-6281 Rob.B.Schmitz@mail.sprint.com
Rob.B.Schmi.... wrote: > Good morning everyone, > > I have been tasked with converting a SQL Server (Vers. 7) database to > Informix. The data conversion was sloppy but is now complete. There > are many stored procedures and I am rewriting them for Informix. One of > them has an expression, as part of the where clause, that specifies to > only get rows where a timestamp column is less than the current time > plus a time zone adjustment value. The original SQL Server expression > is: > > ... and ra.endtimestamp < dateadd(hh, w.timezoneadjustment, > getdate()) ... > > I thought that the equivalent Informix SPL expression would be: > > ... and ra.endtimestamp < datetime(CURRENT) year to fraction(3) + > INTERVAL (w.timezoneadjustment) hour to hour ... > > but that reutrns a syntax error. If endtimestamp is of type DATETIME and timezoneadjustment is of type INTERVAL, then you probably need something like: ... AND ra.endtimestamp < (CURRENT YEAR TO FRACTION(3) + w.timezoneadjustment) ... If timezoneadjustment is of type INT, in terms of a number of hours, then you probably need something like: ... AND ra.endtimestamp < (CURRENT YEAR TO FRACTION(3) + w.timezoneadjustment UNITS HOUR) ... > Another related question: Will the CURRENT keyword return the current > system time or only the time that was current at compilation? If it is > the latter, does someone have a handy way to get the current system time > in a stored procedure (a system call perhaps?). The CURRENT value will be fixed at the time you called the SP. If you are performing a single task and then returning, then that is probably okay for you. If you are looping, then the CURRENT value will be the same for each iteration of the loop. > Here is some pertinent information: > HP-UX 11.0 > IDS 9.21.FC4XQ > ra.endtimestamp is datetime year to fraction(3) > w.timezoneadjustment is integer Oh, I see. Thanks for providing that info. > Does anyone know if what I am doing is allowed in stored procedures. Am > I properly converting this expression? I would think that storing the timezoneadjustment column as type INTERVAL might make life a bit easier, but not a lot. :-) Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+
What is the type of w.timezoneadjustment? If it is an interval anyway, then you simply add it. If it is a DATETIME of some sort, you're on dogy territory (it should be an INTERVAL to deal with postive and negative time zones), but you need to convert it to an interval before adding - probably by subtracting DATETIME(00:00) HOUR TO MINUTE from it. The notation you're using is for INTERVAL literal values, and the text inside the parentheses must be a string of digits and appropriate punctuators - in your case 00 or -01 or +01 or variants on that. I note that there are countries using half-hour time zones (Newfoundland in Canada, India), and countries using quarter-hour time zones (Nepal or Bhutan - mainly to be different from India). So, a timezoneadjustment of type INTERVAL HOUR TO MINUTE deals with all current time zones. Historically, there were time zones with offsets specified to a fraction of a second (Amsterdam, for example). The CURRENT keyword in a stored procedure will return the current time as determined by the time zone the server runs in at the time when the (statement containing the invocation of the) stored procedure starts running. If you are not absolutely sure what time zone that is, then I suggest you take steps to ensure that you do know which time zone is set when the server is run. ...and, checking I've covered everything, I see that timezoneadjustment is an integer. In that case, you use + w.timezoneadjustment UNITS HOUR and start worrying about your people in Newfoundland etc... -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management Solutions 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+-------------------------------> | | "Rob.B.Schmi...." | | | <Rob.B.Schmitz@mail.| | | sprint.com> | | | Sent by: | | | forum.subscriber@iiu| | | g.org | | | | | | | | | 03/04/2003 08:20 AM | | | | |---------+-------------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Interval Question [576] | | | >------------------------------------------------------------------------------- --------------------------------------------------------------| Good morning everyone, I have been tasked with converting a SQL Server (Vers. 7) database to Informix. The data conversion was sloppy but is now complete. There are many stored procedures and I am rewriting them for Informix. One of them has an expression, as part of the where clause, that specifies to only get rows where a timestamp column is less than the current time plus a time zone adjustment value. The original SQL Server expression is: ... and ra.endtimestamp < dateadd(hh, w.timezoneadjustment, getdate()) ... I thought that the equivalent Informix SPL expression would be: ... and ra.endtimestamp < datetime(CURRENT) year to fraction(3) + INTERVAL (w.timezoneadjustment) hour to hour ... but that reutrns a syntax error. Another related question: Will the CURRENT keyword return the current system time or only the time that was current at compilation? If it is the latter, does someone have a handy way to get the current system time in a stored procedure (a system call perhaps?). Here is some pertinent information: HP-UX 11.0 IDS 9.21.FC4XQ ra.endtimestamp is datetime year to fraction(3) w.timezoneadjustment is integer Does anyone know if what I am doing is allowed in stored procedures. Am I properly converting this expression? Any help would be appreciated. Thanks Rob Schmitz 913-345-6281 Rob.B.Schmitz@mail.sprint.com
Rob.B.Schmi.... wrote: > Good morning everyone, > > I have been tasked with converting a SQL Server (Vers. 7) database to > Informix. The data conversion was sloppy but is now complete. There > are many stored procedures and I am rewriting them for Informix. One of > them has an expression, as part of the where clause, that specifies to > only get rows where a timestamp column is less than the current time > plus a time zone adjustment value. The original SQL Server expression > is: > > ... and ra.endtimestamp < dateadd(hh, w.timezoneadjustment, > getdate()) ... > > I thought that the equivalent Informix SPL expression would be: > > ... and ra.endtimestamp < datetime(CURRENT) year to fraction(3) + > INTERVAL (w.timezoneadjustment) hour to hour ... I bet you know an answer. It must looks like 'current year to fraction(3) + interval(w.timezoneadjustment) hour to hour'. So 'datetime(CURRENT) ... ' was an error ... > > but that reutrns a syntax error. > > Another related question: Will the CURRENT keyword return the current > system time or only the time that was current at compilation? If it is > the latter, does someone have a handy way to get the current system time > in a stored procedure (a system call perhaps?). > > Here is some pertinent information: > HP-UX 11.0 > IDS 9.21.FC4XQ > ra.endtimestamp is datetime year to fraction(3) > w.timezoneadjustment is integer > > Does anyone know if what I am doing is allowed in stored procedures. Am > I properly converting this expression? > > Any help would be appreciated. > > Thanks > > Rob Schmitz > 913-345-6281 > Rob.B.Schmitz@mail.sprint.com > > > >