Decoding of Encoded Values in System Tables
The poster couldn't interpret two sysmaster:syssessions columns: 'connected', an integer like 1100256621, and 'hostname', which looked encrypted. Respondents explained 'connected' is a UNIX time_t (seconds since 1970-01-01), convertible in SQL with DBINFO('utc_to_datetime', connected) or by adding an interval to DATETIME(1970-01-01 00:00:00) (dividing by 60 to avoid overflow on 10-digit values), or in ESQL/C via ctime()/localtime(). Hostname isn't encrypted; the poster later found his values were simply IP addresses shown in hexadecimal.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, Does somebody know how can I decode datetime values in system tables? Integer value of column Connected in the table sysmaster:syssessions is like 1100256621. I do not know how can I convert this value to datetime format. In the same table is also the column named Hostname. I think, its value is encrypted, too. Can somebody help me with these problems? With regards Leo
That's a UNIX time_t, ie it's system time. Read it in ESQL/C and pass it to the ctime() function to get a string representing the time in the UDT timezone. To get local time pass it to the localtime() function which returns a struct tm and pass that to asctime(). Art S. Kagel ----- Original Message ----- From: Jozef Horyl <jho@pn.dflex.sk> At: 11/12 7:56 > Hello, > Does somebody know how can I decode datetime values in system tables? > > Integer value of column Connected in the table sysmaster:syssessions is > like 1100256621. > I do not know how can I convert this value to datetime format. > > In the same table is also the column named Hostname. I think, its value > is encrypted, too. > > Can somebody help me with these problems? > > With regards > > Leo
Jozef
Horyl <jho@pn.dflex.sk> wrote:
> Hello,
> Does somebody know how can I decode datetime values in system tables?
>
> Integer value of column Connected in the table sysmaster:syssessions is
> like 1100256621.
> I do not know how can I convert this value to datetime format.
That value is the number of seconds since 1970-01-01 00:00:00.
This question has come up a number of times; take a look through
Google at comp.database.informix for a few solutions and some very
good descriptions of how to handle this. Two quick solutions,
however, are as follows (from DB-Access):
SELECT sid, username, tty, DBINFO("UTC_TO_DATETIME", connected)
FROM syssessions;
SELECT sid, username, tty,DATETIME(1970-01-01 00:00:00) YEAR TO SECOND +
(connected / 60) UNITS MINUTE
FROM syssessions;
Note: In the second example, I had to divide the value in 'connected'
by 60 to work in minutes rather than seconds. Sometime back in 2001
the number of seconds moved up to 10 digits - too large for interval
second(9) to second. (Jonathan Leffler talks about this in at least
one of his responses to this subject.)
There is a lot more to it than what I've listed above. A lot more.
See the discussions at c.d.i. for some excellent background and
discussions about how to deal with system time, GMT, UTC, local time,
etc. You should also consider the solutions offered by Art Kagel.
> In the same table is also the column named Hostname. I think, its value
> is encrypted, too.
Why do you think hostname is encyrpted?
> Can somebody help me with these problems?
--
June Hunt
Hi, the first one is easy. It very much looks like the time in seconds since the epoch. Thus your date would be something like Fri Nov 12 11:50:21 2004. There are several C-functions dealing with this kind of conversion. For a start you can read the man pages for "ctime()" (which is what I used for above conversion). Perl has similar functions as well ... The Hostname encryption I don't really know. Probably someone else can tell. Regards, Martin -- Martin Fuerderer IBM Informix Development Munich Data Management Solutions forum.subscriber@iiug.org wrote on 12.11.2004 13:34:10: > Hello, > Does somebody know how can I decode datetime values in system tables? > > Integer value of column Connected in the table sysmaster:syssessions is > like 1100256621. > I do not know how can I convert this value to datetime format. > > In the same table is also the column named Hostname. I think, its value > is encrypted, too. > > Can somebody help me with these problems? > > With regards > > Leo > >
... or in SQL you can use SELECT DBINFO('utc_to_datetime',connected) from syssessions <where sid = .....> --- "ART KAGEL, ...." <KAGEL@bloomberg.net> wrote: > > That's a UNIX time_t, ie it's system time. Read it > in ESQL/C and pass it to the > ctime() function to get a string representing the > time in the UDT timezone. To > get local time pass it to the localtime() function > which returns a struct tm > and pass that to asctime(). > > Art S. Kagel > > ----- Original Message ----- > From: Jozef Horyl <jho@pn.dflex.sk> > At: 11/12 7:56 > > > Hello, > > Does somebody know how can I decode datetime > values in system tables? > > > > Integer value of column Connected in the table > sysmaster:syssessions is > > like 1100256621. > > I do not know how can I convert this value to > datetime format. > > > > In the same table is also the column named > Hostname. I think, its value > > is encrypted, too. > > > > Can somebody help me with these problems? > > > > With regards > > > > Leo > > > > >
> > ----- Original Message ----- > > From: Jozef Horyl <jho@pn.dflex.sk> > > At: 11/12 7:56 > > > > > [...] in the table sysmaster:syssessions [...] > > > > > > In the same table is also the column named > > > Hostname. I think, its value is encrypted, too. Unlikely. On my machine, the host name turns up as text (anubis). If it is numeric on yours, it is probably some variation on the theme of the I/P address. Without seeing the value, its hard to say whether you're seeing hex notation with no dots, or decimal notation (distinct from dotted-decimal which would be the preferred form for an IPv4 address). -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 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!"
Thanks, I already know solution. Hostnames are browsed as IP addresses in hexadecimal. Jozef Horyl -----Original Message----- From: Andreas.KUTSCHE@spar.at [mailto:andreas.kutsche@spar.at] Sent: 15.11.2004 09:45 To: jho@pn.dflex.sk; ids@iiug.org Subject: AW: Decoding of Encoded Values in System Tables [3673] > Hello, > Does somebody know how can I decode datetime values in system tables? > > Integer value of column Connected in the table > sysmaster:syssessions is > like 1100256621. > I do not know how can I convert this value to datetime format. > > In the same table is also the column named Hostname. I think, > its value > is encrypted, too. > I don't think that the hostname is encrypted. Normally you get the name you expect. But maybe the connected client provides a 'strange' hostname? Or maybe there is a problem with character settings? I haven't seen an unreadable hostname yet, but we often see unreadable/senseless entries for the tty column in syssessions. Especially when the connections come from an Coldfusion Client. Regards, Andreas Kutsche ________ Information from NOD32 ________ This message was checked by NOD32 Antivirus System for Linux Mail Server. http://www.nod32.com