Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2016
    Posts
    2

    Lightbulb Unanswered: Sample query for convert int to time for informix

    I have a column in table data stored as int, i wanted to convert this value into time HH:MMS format.

    Also is there any function to find Average time for a column datatype is varchar

    0:04:28
    0:05:36
    0:02:49
    0:03:02
    0:02:15
    0:03:23
    0:04:19
    0:01:42
    0:00:00
    0:02:32
    0:04:01
    0:05:11

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    392
    Provided Answers: 1
    Hi kallelly,


    you probably want to use DBINFO and utc_to_datetime, which converts an integer unix epoch integer to a YYYY-MM-DD HH:MMS datetime.

    Then
    trying using the SUBSTR function to grab the 8 last characters,

    something like:
    SELECT DBINFO("utc_to_datetime", your_integer_value)
    FROM 'sysmaster:"informix".sysdual'

    check here for utc_to_datetime

    I have no engine available to test, but these are the possible directions. you may have to cast the datetime to a char, using ::

    I do not understand your second question.

    Regards,

    eric
    Last edited by begooden-it; 10-26-16 at 08:51.

  3. #3
    Join Date
    Oct 2016
    Posts
    2

    Sample query for int to hh:mm:ss conversion in informix

    I am using Informix database where the data stored in int datatype for the hh:mm:ss

    For example talktime of agent value stored in 240339 as int datatype in the filed called talktime and same data stored in the another filed as varchar format as 0:04:00 in the filed name convtalktime.

    I had used the below query for converting the int to hh:mm:ss

    select extend( dbinfo( "UTC_TO_DATETIME", talkduration ), Hour to minute ) from AgentTable where name ="ABC";

    the result was 00:15:00

    My real problem is, I need to find the average talkduration and these are the database values for each category talk duration

    Name TalkDuration (int) TalkDuration (Varchar Datatype)
    ABC 240339 0:04:00
    DEF 194664 0:03:14
    GHI 8450 0:00:08
    JKL 0 0:00:00

    The average should be 00:01:50 or 00:01:51

    one more observation is when I had converted JKL value i got the result as 05:30:00 which is my timezone of India. So i believe need to mention the timezone in the query.

    So can some can help me on this.


    Sheejan Kallely

  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    392
    Provided Answers: 1
    Code:
    UTC_TO_DATETIME
    returns a
    Code:
    DATETIME YEAR TO SECOND
    value.
    UTC means Universal Coordinated Time, synonymous to GMT. It is a time, not an interval.
    THis function serves to convert generally the system time which is effectively an integer storing the number of units since Jan 01 1970.

    If you handle
    Code:
    INTERVALs
    , this is a different story, and you need to find out what is the real meaning of the INTEGER you are talking about.

    to obtain average with INTERVALs, you just need to use the AVERAGE (AVG) aggregate function in the query:
    Code:
    SELECT AVG(interval_column_name) FROM table_name WHERE xxxx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •