Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012
    Posts
    5

    Unanswered: sql to informix conversion

    SELECT DATEADD("ss", last_time, '1970-01-01'); is the SQl expression that converts LOCAL UNIX TIME into regular time format. I need informix equivalent of this expression.
    I need to fit that informix expression in place of ?? in below query.

    SELECT *
    FROM haglog WHERE ACD=1 AND ROW_DATE= ' 01/01/1900' and ??? between 2300 and 2359 ;

    Thanks in advance,
    harry

  2. #2
    Join Date
    Jul 2012
    Posts
    5
    I am running this expression in Informix side.

  3. #3
    Join Date
    Nov 2010
    Posts
    16
    Quote Originally Posted by harry001 View Post
    SELECT DATEADD("ss", last_time, '1970-01-01'); is the SQl expression that converts LOCAL UNIX TIME into regular time format. I need informix equivalent of this expression.
    I need to fit that informix expression in place of ?? in below query.

    SELECT *
    FROM haglog WHERE ACD=1 AND ROW_DATE= ' 01/01/1900' and ??? between 2300 and 2359 ;

    Thanks in advance,
    harry
    Not sure what is "last_time"... but I suppose it's the number of seconds since 1970-01-01. The same can be found in Informix in sysmaster table called sysshmvals.

    For this value, you can write:

    SELECT DBINFO('utc_to_datetime', sh_curtime) FROM sysmaster:sysshmvals;

    So, I believe it will be easy for you to adapt this to your specific situation.

    Be aware of DST... not sure how DBINFO handles this.
    Regards

  4. #4
    Join Date
    Jul 2012
    Posts
    2
    Your question isn't very clear. Superficially, you seem to have a column, last_time, that contains a number of seconds since the Unix Epoch (1970-01-01 00:00:00 +00:00). There are ways to convert such a value into an Informix DATETIME YEAR TO SECOND, such as the DBINFO('utc_to_datetime', last_time) function. However, this function has a 32-bit INTEGER limitation, which is a nuisance (I only just found that out!).

    A more complex function can be written in SPL to handle an arbitrary date in the range supported by Informix dates (0001-01-01 to 9999-12-31 on the proleptic Gregorian calendar):

    {
    # "@(#)$Id: frunixtime.spl,v 1.2 2002/09/25 18:10:48 jleffler Exp $"
    #
    # Stored procedure FROM_UNIX_TIME written by Jonathan Leffler
    # (jleffler@us.ibm.com) as counterpart to TO_UNIX_TIME.
    #
    # If you run this procedure with no arguments (use the default), you
    # need to worry about the time zone the database server is using because
    # the value of CURRENT is determined by that, and you need to compensate
    # for it if you are using a different time zone.
    #
    # Note that this version works for dates after 2001-09-09 when the
    # interval between 1970-01-01 00:00:00+00:00 and current exceeds the
    # range of INTERVAL SECOND(9) TO SECOND. Accepting DECIMAL(18,5) allows
    # it to work for all valid datetime values including fractional seconds.
    # In the UTC time zone, the 'Unix time' of 9999-12-31 23:59:59 is
    # 253402300799 (12 digits); the equivalent for 0001-01-01 00:00:00 is
    # -62135596800 (11 digits). The integer part of both these values is
    # unrepresentable in a 32-bit integer, of course, so most Unix systems
    # won't handle this range, and the so-called 'Proleptic Gregorian
    # Calendar' used to calculate the dates ignores locale-dependent details
    # such as the loss of days that occurred during the switch between the
    # Julian and Gregorian calendar, but those are minutiae that most people
    # can ignore most of the time.
    }

    CREATE PROCEDURE from_unix_time(v DECIMAL(18,5) DEFAULT 0)
    RETURNING DATETIME YEAR TO FRACTION(5);
    DEFINE n DATETIME YEAR TO FRACTION(5);
    DEFINE i1 INTEGER;
    DEFINE i2 DECIMAL(11,5);
    LET i1 = v / (24 * 60 * 60);
    LET i2 = v - (i1 * 24 * 60 * 60);
    LET n = DATETIME(1970-01-01 00:00:00.00000) YEAR TO FRACTION(5);
    LET n = n + i1 UNITS DAY;
    LET n = n + i2 UNITS FRACTION(5);
    RETURN n;
    END PROCEDURE;

    As you can see, I wrote that a while ago now (approximately a decade ago, in fact).

    It is then very unclear what the remainder of your requirements are. Are you looking for events between 23:00 and 23:59 on 1900-01-01? If so, you run into problems with 32-bit integers only allowing you back to 1901-12-13 (hence the mention of the 32-bit limit).

    If you are wanting to find DATETIME value that fall between 23:00 and 23:59 on any day, then you can use EXTEND (to contract the range...oh well, the function does the job, regardless of its name)

    ... WHERE EXTEND(datetime_value, HOUR TO MINUTE) BETWEEN DATETIME(23:00) HOUR TO MINUTE AND DATETIME(23:59) HOUR TO MINUTE

    The time notation in Informix is nothing if not verbose, but you can do most of the things you need to do if you understand what's going on.

Posting Permissions

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