ODBC connection into an Avaya Informix database
A user pulling Avaya CMS (CentreVu Supervisor) data from an Informix database via ODBC into Access 97 asked how to convert login/logout timestamps stored as long integers (e.g. 1162373160) into readable dates. Replies identified them as Unix epoch seconds since 01/01/1970: in Informix you can write a stored procedure adding the value as an interval of seconds to the base datetime (Jonathan Leffler posted such a routine, archived at IIUG), or in Excel/MS tools simply compute value/86400 + DATE(1970,1,1) and apply a date/time format. Also clarified that IIUG is the International Informix Users' Group.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I am using an ODBC connection to get user information from an Avaya (CentreVue Supervisor) system, (it's using Informix 2000). I work in a call center where we use login/logout times for agents. The table that I'm getting data from gives it in an long integer format. (i.e. 1162373160 would be 11/1/06 9:26AM - login AND 1162373168 would be 11/1/06 9:33AM - logout) Also, I'm using Access 97 as a capture point. Is there any way to decode the integer date ? like with an Access or SQL query? Any help would be greatly appreciated.
If you do a small stored procedure, I guess it's doable. It would be something like: select myfunc(date_col) from... my 2 cents... jgp > -----Original Message----- > From: informix-list-bounces@iiug.org [mailto:informix-list- > bounces@iiug.org] On Behalf Of jahillia@yahoo.com > Sent: Tuesday, November 14, 2006 21:51 > To: informix-list@iiug.org > Subject: ODBC connection into an Avaya Informix database > > Hi, > I am using an ODBC connection to get user information from an Avaya > (CentreVue Supervisor) system, (it's using Informix 2000). I work in a > call center where we use login/logout times for agents. The table that > I'm getting data from gives it in an long integer format. (i.e. > 1162373160 would be 11/1/06 9:26AM - login AND 1162373168 would be > 11/1/06 9:33AM - logout) > > Also, I'm using Access 97 as a capture point. Is there any way to > decode the integer date ? like with an Access or SQL query? > > Any help would be greatly appreciated. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list
jahillia@yahoo.com wrote: > Hi, > I am using an ODBC connection to get user information from an Avaya > (CentreVue Supervisor) system, (it's using Informix 2000). I work in a > call center where we use login/logout times for agents. The table that > I'm getting data from gives it in an long integer format. (i.e. > 1162373160 would be 11/1/06 9:26AM - login AND 1162373168 would be > 11/1/06 9:33AM - logout) > > Also, I'm using Access 97 as a capture point. Is there any way to > decode the integer date ? like with an Access or SQL query? > > Any help would be greatly appreciated. If it contains seconds since some basedate (like the UNIX base of Dec. 31, 1969 or the Windows base date of ...) you can use a stored procedure to add it to a datetime of the basedate after converting it to an interval of that many seconds. Jonathan Leffler's posted such a procedure to this newsgroup many times including once in the last month or two. Poke in the IIUG Archive. Art S. Kagel
Art S. Kagel wrote: > jahillia@yahoo.com wrote: > > Hi, > > I am using an ODBC connection to get user information from an Avaya > > (CentreVue Supervisor) system, (it's using Informix 2000). I work in a > > call center where we use login/logout times for agents. The table that > > I'm getting data from gives it in an long integer format. (i.e. > > 1162373160 would be 11/1/06 9:26AM - login AND 1162373168 would be > > 11/1/06 9:33AM - logout) > > > > Also, I'm using Access 97 as a capture point. Is there any way to > > decode the integer date ? like with an Access or SQL query? > > > > Any help would be greatly appreciated. > > If it contains seconds since some basedate (like the UNIX base of Dec. 31, > 1969 or the Windows base date of ...) you can use a stored procedure to add > it to a datetime of the basedate after converting it to an interval of that > many seconds. Jonathan Leffler's posted such a procedure to this newsgroup > many times including once in the last month or two. Poke in the IIUG Archive. > > Art S. Kagel Thanks Art, I have poked through quite a few messages and have been befuddled at best. I will look for Jonathan's procedure, but I'm still a little new to these boards. What's IIUG? Is the comp.databases.informix the right group to find the info. Thanks again...
jahillia@yahoo.com wrote: > Art S. Kagel wrote: > > jahillia@yahoo.com wrote: > > > Hi, > > > I am using an ODBC connection to get user information from an Avaya > > > (CentreVue Supervisor) system, (it's using Informix 2000). I work in a > > > call center where we use login/logout times for agents. The table that > > > I'm getting data from gives it in an long integer format. (i.e. > > > 1162373160 would be 11/1/06 9:26AM - login AND 1162373168 would be > > > 11/1/06 9:33AM - logout) > > > > > > Also, I'm using Access 97 as a capture point. Is there any way to > > > decode the integer date ? like with an Access or SQL query? > > > > > > Any help would be greatly appreciated. > > > > If it contains seconds since some basedate (like the UNIX base of Dec. 31, > > 1969 or the Windows base date of ...) you can use a stored procedure to add > > it to a datetime of the basedate after converting it to an interval of that > > many seconds. Jonathan Leffler's posted such a procedure to this newsgroup > > many times including once in the last month or two. Poke in the IIUG Archive. > > > > Art S. Kagel > > > Thanks Art, > I have poked through quite a few messages and have been befuddled at > best. I will look for Jonathan's procedure, but I'm still a little new > to these boards. What's IIUG? Is the comp.databases.informix the right > group to find the info. > > Thanks again... In reading above, I may have not been real clear. I'm not so much working in the Informix database as I am trying to understand data coming out of it. I have attached the table through and ODBC connection and am pulling data from there. Inside of the table there are two columns that I am trying to "decipher". Inside of the CMS reporting engine it takes the integer values and translates it to a specific date and time. I am trying to figure out how to do it. I'm sorry if I'm not making it clear.
<jahillia@yahoo.com> schrieb im Newsbeitrag news:1163604211.208544.136640@f16g2000cwb.googlegroups.com... > > jahillia@yahoo.com wrote: > > Art S. Kagel wrote: > > > jahillia@yahoo.com wrote: > > > > Hi, > > > > I am using an ODBC connection to get user information from an Avaya > > > > (CentreVue Supervisor) system, (it's using Informix 2000). I work in a > > > > call center where we use login/logout times for agents. The table that > > > > I'm getting data from gives it in an long integer format. (i.e. > > > > 1162373160 would be 11/1/06 9:26AM - login AND 1162373168 would be > > > > 11/1/06 9:33AM - logout) > > > > > > > > Also, I'm using Access 97 as a capture point. Is there any way to > > > > decode the integer date ? like with an Access or SQL query? > > > > > > > > Any help would be greatly appreciated. > > > > > > If it contains seconds since some basedate (like the UNIX base of Dec. 31, > > > 1969 or the Windows base date of ...) you can use a stored procedure to add > > > it to a datetime of the basedate after converting it to an interval of that > > > many seconds. Jonathan Leffler's posted such a procedure to this newsgroup > > > many times including once in the last month or two. Poke in the IIUG Archive. > > > > > > Art S. Kagel > > > > > > Thanks Art, > > I have poked through quite a few messages and have been befuddled at > > best. I will look for Jonathan's procedure, but I'm still a little new > > to these boards. What's IIUG? Is the comp.databases.informix the right > > group to find the info. > > > > Thanks again... > > In reading above, I may have not been real clear. I'm not so much > working in the Informix database as I am trying to understand data > coming out of it. I have attached the table through and ODBC connection > and am pulling data from there. Inside of the table there are two > columns that I am trying to "decipher". Inside of the CMS reporting > engine it takes the integer values and translates it to a specific date > and time. I am trying to figure out how to do it. I'm sorry if I'm not > making it clear. > ok, IIUG is the Informix International User Group, (http://www.iiug.org) Your problem is transferring the integer 1162373160 to the "real" date and time, correct? This integer is based as "seconds from 01/01/1970 00:00 (second 0) For Example: put the integer in Excel into Cell A1. Cell A2 is the formula : A1 / 86400 + "01/01/1970" that is: divide the seconds by 86400 (seconds per day) and add the date of 01/01/1970 to get the date base to the base Excel (and other M$ Stuff) is working with (01/01/1900) Now format cell A2 with a date and time format you like. Thats all. This should give you the idea how to handle these date an time values. I don't know what you are working with, but M$ tools should be able to handle it the way like Excel does. regards, Joerg
jahillia@yahoo.com wrote: > Art S. Kagel wrote: > >>jahillia@yahoo.com wrote: >> >>>Hi, >>>I am using an ODBC connection to get user information from an Avaya >>>(CentreVue Supervisor) system, (it's using Informix 2000). I work in a >>>call center where we use login/logout times for agents. The table that >>>I'm getting data from gives it in an long integer format. (i.e. >>>1162373160 would be 11/1/06 9:26AM - login AND 1162373168 would be >>>11/1/06 9:33AM - logout) >>> >>>Also, I'm using Access 97 as a capture point. Is there any way to >>>decode the integer date ? like with an Access or SQL query? >>> >>>Any help would be greatly appreciated. >> >>If it contains seconds since some basedate (like the UNIX base of Dec. 31, >>1969 or the Windows base date of ...) you can use a stored procedure to add >>it to a datetime of the basedate after converting it to an interval of that >>many seconds. Jonathan Leffler's posted such a procedure to this newsgroup >>many times including once in the last month or two. Poke in the IIUG Archive. >> >>Art S. Kagel > > > > Thanks Art, > I have poked through quite a few messages and have been befuddled at > best. I will look for Jonathan's procedure, but I'm still a little new > to these boards. What's IIUG? Is the comp.databases.informix the right > group to find the info. IIUG is the International Informix Users' Group (www.iiug.org). It is a user run and funded organization by and for the Informix community. Membership is free (sign up online). There are several technical and non-technical forums available to members through the IIUG web site. The IDS Forum is the equivalent of this newsgroup (CDI or comp.databases.informix) and there is also an email gateway to CDI (informix-list@iiug.org). There are also archives of the forums and of this newsgroup on the site. The site also posts news of interest to the Informix user community and hosts the IIUG Software Repository a collection of scripts and programs contributed by the user community to help make each others' lives easier. Please join as IBM listens to us and if you can get involved. It is fine to post questions like yours here and most of the folk that monitor the IIUG Forums also monitor CDI. Art S. Kagel
"Art S. Kagel" <kagel@bloomberg.net> wrote in message news:455B524B.70100@bloomberg.net... > IIUG is the International Informix Users' Group (www.iiug.org). It is a > user run and funded organization Er ....?