Time arithmetic query

Hi all, I'm completely stumped on time arithmetic in Informix. I'm using version 9 of Informix. I have a table named TRANS with a column called SDTIME. It is a datetime datatype. I want to generate queries that can tell me which queries have a SDTIME value that is less than 2 minutes from the current time. (Or it could be 90 minutes or 6 hours etc.) I have been fumbling around going in circles. Can anyone help me?

psala@swin.edu.au wrote: > Hi all, I'm completely stumped on time arithmetic in Informix. > > I'm using version 9 of Informix. > > I have a table named TRANS with a column called SDTIME. It is a > datetime datatype. > > I want to generate queries that can tell me which queries have a > SDTIME value that is less than > 2 minutes from the current time. (Or it could be 90 minutes or 6 hours > etc.) Something like this should do it: select * from trans where sdtime < current + 2 units minute HTH, -- Carsten Haese http://informixdb.sourceforge.net

Try this: Select * from trans where sdtime > current - 2 units minute Or Select * from trans where sdtime > current - 6 units hour -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of psala@swin.edu.au Sent: Wednesday, 16 April 2008 1:07 p.m. To: informix-list@iiug.org Subject: Time arithmetic query Hi all, I'm completely stumped on time arithmetic in Informix. I'm using version 9 of Informix. I have a table named TRANS with a column called SDTIME. It is a datetime datatype. I want to generate queries that can tell me which queries have a SDTIME value that is less than 2 minutes from the current time. (Or it could be 90 minutes or 6 hours etc.) I have been fumbling around going in circles. Can anyone help me? _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list DISCLAIMER: This email contains confidential information and may be legally privileged. If you are not the intended recipient or have received this email in error, please notify the sender immediately and destroy this email. You may not use, disclose or copy this email or its attachments in any way. Any opinions expressed in this email are those of the author and are not necessarily those of the Fonterra Co-operative Group. http://www.fonterra.com/

Carsten Haese wrote: > psala@swin.edu.au wrote: >> Hi all, I'm completely stumped on time arithmetic in Informix. >> >> I'm using version 9 of Informix. >> >> I have a table named TRANS with a column called SDTIME. It is a >> datetime datatype. >> >> I want to generate queries that can tell me which queries have a >> SDTIME value that is less than >> 2 minutes from the current time. (Or it could be 90 minutes or 6 hours >> etc.) After reviewing Jarrod's answer, I realized that "less than 2 minutes from the current time" is rather ambiguous, and I'm not sure whether my answer or Jarrod's answer is what you actually need, or if you even need something like "sdtime between (current-2 units minute) and (current+2 units minute)". Anyway, some combination of comparing sdtime to the results of adding/subtracting (x units minute) to/from current should give you what you need, whatever that may be. -- Carsten Haese http://informixdb.sourceforge.net

Thanks guys, I can't test until tomorrow. Thanks for your help