Re: Datetime comparison & arithmetic
Chris (Christian Brauer) asked for a straight single-SELECT way to combine a DATE column and a DATETIME HOUR TO SECOND column into one datetime for ordering. Richard Harnden suggested extending the date to year to second and adding the time minus today. Jonathan Leffler suggested converting the time to an interval by subtracting DATETIME(0) SECOND TO SECOND. Chris confirmed it worked in his 4GL application; solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Easy (not) : select ( extend(a_date, year to second) + (extend(a_time, year to second) - today) ) from ... >>> Christian Brauer <Christian.Brauer@Dresdner-Bank.com> 03/10/00 12:41pm >>> Hello CDI-Members, I don't find any acceptable solution for the following query: table mytable (has lots of data, structure cannot be changed anymore !) a_date DATE, a_time DATETIME HOUR TO SECOND, a_key INTEGER, stuff CHAR(...) I'd like to retrieve some data ordered by point of time (given in following pseudo-code): SELECT Who_Can_Help_To_Combine(a_date, a_time), key FROM mytable ORDER BY 1 The following solutions came to my mind: - use a stored procedure which uses string buffers an datetime conversion - use a temporary table in some way to combine the two date/datetime value Does anyone know an STRAIGHT method to convert a_date,a_time to a_datetime (perhaps using EXTEND,DATETIME,UNITS,MDY....) in my single select ? Any help (except supposing the 2 "solutions" mentioned above) or funny comments (hi Clown) are really welcomed... Read you, Chris (who once thought, that Yesterday and 16:00 would be: 16:00 on Yesterday...)
Richard harnden wrote: > Easy (not) : > > select ( extend(a_date, year to second) > + (extend(a_time, year to second) - today) ) > from ... Does this work? You can only add intervals to DATETIME values. So, the key is to convert the DATETIME HOUR TO SECOND into an INTERVAL HOUR TO SECOND: SELECT extend(a_date, year to second) + (a_time - DATETIME(0) SECOND TO SECOND) The difference between two datetime values is an interval. If you can change the DATETIME column into an interval, life will probably be easier... > >>> Christian Brauer <Christian.Brauer@Dresdner-Bank.com> 03/10/00 12:41pm >>> > I don't find any acceptable solution for the following query: > > table mytable (has lots of data, structure cannot be changed anymore > !) > a_date DATE, > a_time DATETIME HOUR TO SECOND, > a_key INTEGER, > stuff CHAR(...) > > I'd like to retrieve some data ordered by point of time (given in > following > pseudo-code): > SELECT Who_Can_Help_To_Combine(a_date, a_time), key > FROM mytable > ORDER BY 1 > > The following solutions came to my mind: > - use a stored procedure which uses string buffers an datetime > conversion > - use a temporary table in some way to combine the two date/datetime > value > > Does anyone know an STRAIGHT method to convert a_date,a_time to > a_datetime > (perhaps using EXTEND,DATETIME,UNITS,MDY....) in my single select ? > > Any help (except supposing the 2 "solutions" mentioned above) or > funny comments (hi Clown) are really welcomed... -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>
> select ( extend(a_date, year to second) > + (extend(a_time, year to second) - today) ) Thank you for your hints - I missed to subtract 'today' when using EXTEND on Friday. Problem is solved now. Bye folks, Chris
> > select ( extend(a_date, year to second) > > + (extend(a_time, year to second) - today) ) > > from ... > > Does this work? You can only add intervals to DATETIME values. > > The difference between two datetime values is an interval. If you can change the > DATETIME > column into an interval, life will probably be easier... As you pointed out the manual says so, that's why I asked for an EASY solution after studying the online docs. Nevertheless, I tried the statement above and it seems to work in my 4GL application without an error. Bye, Chris