How to get current datetime in SPs

Posted in 2005

SHEHLA ARSHAD — Fri, 15 Jul 2005 00:05:10 — source: IIUG Forums & Mailing Lists

I would like the Informix Gurus to confirm if it is alright to use the following in a stored procedure to get current datetime.... select dbinfo('utc_to_datetime',sh_curtime) as current from sysmaster:sysshmvals I found this on google:comp.databases.informix and it does work. I just want to get a comfort feeling before I introduce this to the developers. thanks, Shehla -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of ANTHONY Sent: Wednesday, July 13, 2005 11:21 AM To: ids@iiug.org Subject: general index question [5412] I have a question about indexes in general. We have several tables that have an index, say, on columns a,b,c then an index on a, or an index on a,b. It has always been my understanding that the second index is redundant as the first index can be used to satisfy queries on a, or a,b. Is this correct? Is there any reason to duplicate the indices like this?

↪ replying to SHEHLA ARSHAD

Support Informix — Fri, 15 Jul 2005 08:47:10 — source: IIUG Forums & Mailing Lists

hi, yes it works without the dash in column cu-rtime. some other suggestions : > select first 1 current from systables ;2005-07-15 09:20:11.000 or with specification > select first 1 current year to second from systables ;2005-07-15 09:20:54 more suggestions on date and time : http://gethelp.devx.com/techtips/info_pro/10min/10min1000/10min1000-2.asp -----Original Message----- From: nobody@ace.iiug.org [mailto:nobody@ace.iiug.org] Sent: 15 July 2005 02:05 To: ids@iiug.org Subject: How to get current datetime in SPs [5433] I would like the Informix Gurus to confirm if it is alright to use the following in a stored procedure to get current datetime.... select dbinfo('utc_to_datetime',sh_cu-rtime) as current from sysmaster:sysshmvals I found this on google:comp.databases.informix and it does work. I just want to get a comfort feeling before I introduce this to the developers. thanks, Shehla -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of ANTHONY Sent: Wednesday, July 13, 2005 11:21 AM To: ids@iiug.org Subject: general index question [5412] I have a question about indexes in general. We have several tables that have an index, say, on columns a,b,c then an index on a, or an index on a,b. It has always been my understanding that the second index is redundant as the first index can be used to satisfy queries on a, or a,b. Is this correct? Is there any reason to duplicate the indices like this? ------_=_NextPart_001_01C58918.D701D890 Content-Type: text/html Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Dus-ascii"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2653.12"> <TITLE>RE: How to get current datetime in SPs [5433] </TITLE> </HEAD> <BODY> hi, yes it works without the dash in column = cu-rtime. some other suggestions : > select first 1 current from  systables = ; 2005-07-15 09:20:11.000 or with specification > select first 1 current year to second from = systables ; 2005-07-15 09:20:54 more suggestions on date and time : <A = HREF=3D"http://gethelp.devx.com/techtips/info_pro/10min/10min1000/10min1= 000-2.asp" = TARGET=3D"_blank">http://gethelp.devx.com/techtips/info_pro/10min/10min1= 000/10min1000-2.asp</A> -----Original Message----- From: nobody@ace.iiug.org [<A = HREF=3D"mailto:nobody@ace.iiug.org">mailto:nobody@ace.iiug.org</A>] = Sent: 15 July 2005 02:05 To: ids@iiug.org Subject: How to get current datetime in SPs [5433] = I would like the Informix Gurus to confirm if it is = alright to use the following in a stored procedure to get current = datetime.... select dbinfo('utc_to_datetime',sh_curtime) as = current from sysmaster:sysshmvals I found this on google:comp.databases.informix and it = does work. I just want to get a comfort feeling before I introduce this = to the developers. thanks, Shehla -----Original Message----- From: forum.subscriber@iiug.org [<A = HREF=3D"mailto:forum.subscriber@iiug.org">mailto:forum.subscriber@iiug.o= rg</A>]On Behalf Of ANTHONY Sent: Wednesday, July 13, 2005 11:21 AM To: ids@iiug.org Subject: general index question [5412] I have a question about indexes in general.  We = have several tables that have an index, say, on columns a,b,c  = then an index on a, or an index on a,b. It has always been my = understanding that the second index is redundant as the first index can = be used to satisfy queries on a, or a,b.  Is this correct?  = Is there any reason to duplicate the indices like this? </BODY> </HTML> ------_=_NextPart_001_01C58918.D701D890--

↪ replying to SHEHLA ARSHAD

Laila Holt Larsen — Thu, 25 Aug 2005 12:32:51 — source: IIUG Forums & Mailing Lists

Hi To get the current time, I would use the following sql select current from systables where tabid = 1; Kind regards Laila Holt Larsen mailto:lhl@djoef.dk The Association of Danish Lawyers and Economists IT-department +45 33959944 -----Oprindelig meddelelse----- Fra: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] På vegne af SHEHLA ARSHAD Sendt: 15. juli 2005 02:05 Til: ids@iiug.org Emne: How to get current datetime in SPs [5433] I would like the Informix Gurus to confirm if it is alright to use the following in a stored procedure to get current datetime.... select dbinfo('utc_to_datetime',sh_curtime) as current from sysmaster:sysshmvals I found this on google:comp.databases.informix and it does work. I just want to get a comfort feeling before I introduce this to the developers. thanks, Shehla -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On Behalf Of ANTHONY Sent: Wednesday, July 13, 2005 11:21 AM To: ids@iiug.org Subject: general index question [5412] I have a question about indexes in general. We have several tables that have an index, say, on columns a,b,c then an index on a, or an index on a,b. It has always been my understanding that the second index is redundant as the first index can be used to satisfy queries on a, or a,b. Is this correct? Is there any reason to duplicate the indices like this?

↪ replying to Laila Holt Larsen

Link, David A. \\\\(Omaha\\\\) — Thu, 25 Aug 2005 13:55:54 — source: IIUG Forums & Mailing Lists

Laila, Your solution works fine outside of a stored procedure but if you called it multiple times from within a stored procedure you would get the same answer each time. The value would be from the time that the stored procedure was entered. Shehla, We use that method frequently with no problems. David -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of Laila Holt .... Sent: Thursday, August 25, 2005 7:33 AM To: ids@iiug.org Subject: SV: How to get current datetime in SPs [5667] Hi To get the current time, I would use the following sql select current from systables where tabid = 1; Kind regards Laila Holt Larsen mailto:lhl@djoef.dk The Association of Danish Lawyers and Economists IT-department +45 33959944 -----Oprindelig meddelelse----- Fra: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] På vegne af SHEHLA ARSHAD Sendt: 15. juli 2005 02:05 Til: ids@iiug.org Emne: How to get current datetime in SPs [5433] I would like the Informix Gurus to confirm if it is alright to use the following in a stored procedure to get current datetime.... select dbinfo('utc_to_datetime',sh_curtime) as current from sysmaster:sysshmvals I found this on google:comp.databases.informix and it does work. I just want to get a comfort feeling before I introduce this to the developers. thanks, Shehla

↪ replying to Link, David A. \\\\(Omaha\\\\)

Everett Mills — Thu, 25 Aug 2005 14:31:19 — source: IIUG Forums & Mailing Lists

Informix stores the current time at the beginning of the SP. You may have to have your SP call a shell script that writes the time to a file you can read into the SP, say into a temp table. --EEM > -----Original Message----- > From: Link, David.... [mailto:DALink@west.com] > Sent: Thursday, August 25, 2005 8:56 AM > To: ids@iiug.org > Subject: RE: How to get current datetime in SPs [5668] > > Laila, > > Your solution works fine outside of a stored procedure but if you called > it multiple times from within a stored procedure you would get the same > answer each time. The value would be from the time that the stored > procedure was entered. > > Shehla, > > We use that method frequently with no problems. > > David > > -----Original Message----- > From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On > Behalf Of Laila Holt .... > Sent: Thursday, August 25, 2005 7:33 AM > To: ids@iiug.org > Subject: SV: How to get current datetime in SPs [5667] > > Hi > > To get the current time, I would use the following sql > > select current > from systables > where tabid = 1;> > Kind regards > Laila Holt Larsen > mailto:lhl@djoef.dk > The Association of Danish Lawyers and Economists > IT-department > +45 33959944 > > > -----Oprindelig meddelelse----- > Fra: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] På vegne > af SHEHLA ARSHAD > Sendt: 15. juli 2005 02:05 > Til: ids@iiug.org > Emne: How to get current datetime in SPs [5433] > > > I would like the Informix Gurus to confirm if it is alright to use the > following in a stored procedure to get current datetime.... > > select dbinfo('utc_to_datetime',sh_curtime) as current from > sysmaster:sysshmvals > > I found this on google:comp.databases.informix and it does work. I just > want to get a comfort feeling before I introduce this to the developers. > > thanks, > Shehla > >