Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Unanswered: {fn TIMESTAMPDIFF(...)} equivalent routine

    Does anyone out there know if Informix has an equivalent of the TIMESTAMPDIFF function?

    I'm converting our code from SQL SERVER to INFORMIX and have got around similar problems by writing my own functions (with some help from you guys).

    Now I could spend some time and do an equivalent for this but it would be quite complex what with varying months days and leap years etc so I was wondering if anyone out there has found a solution already.

    For example I have a table (stockbatches) with 1 row with:
    receiveddate = '2005-06-01 00:00:00'
    createddate = '2006-06-20 00:00:00'

    The below SQL produces a result of 384

    select {fn TIMESTAMPDIFF(SQL_TSI_DAY, receiveddate, createddate)}
    from stockbatches

    thanks for any help on this.

    Andy
    ahmatexeldotcodotuk

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Just convert to date data type...

    Code:
    create temp table tp01(
    dt1 datetime year to second,
    dt2 datetime year to second)
    ;
    insert into tp01 values ("2005-06-01 00:00:00", "2006-06-20 00:00:00")
    ;
    select *, (date(dt1)-date(dt2)) from tp01
    ;
    the result:
    Code:
    dt1                 dt2                 (expression)
    
    2005-06-01 00:00:00 2006-06-20 00:00:00       -384
    Last edited by ceinma; 01-24-08 at 17:11.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Thanks again ceinma.

    With that I've created my own Informix version of TIMESTAMPDIFF supporting DAY, MONTH and YEAR (all that I require - for now...).

    >>>
    create function timestampdiff (type char(20), pDate1 datetime year to second, pDate2 datetime year to second) returning integer;

    if type = 'SQL_TSI_DAY' then
    return date(pDate2) - date(pDate1);
    elif type = 'SQL_TSI_MONTH' then
    return (month(pDate2) - month(pDate1)) + ((year(pDate2) - year(pDate1)) * 12);
    elif type = 'SQL_TSI_YEAR' then
    return year(pDate2) - year(pDate1);
    end if;

    end function
    <<<

    And so when running Informix my runtime parser extracts "{fn TIMESTAMPDIFF(SQL_TSI_DAY, createddate, receiveddate)}" and places quotes around SQL_TSI_DAY in order to call my function.

    One last quick question, you can see I've had to pass SQL_TSI_DAY as a string - do you know if it is possible to globally define such variables in Informix? If it is then I would not have to alter the SQL string at all at runtime.

    thanks

    Andy

  4. #4
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    I'll elaborate a little more on my last post:
    >>>
    One last quick question, you can see I've had to pass SQL_TSI_DAY as a string - do you know if it is possible to globally define such variables in Informix? If it is then I would not have to alter the SQL string at all at runtime.
    <<<

    By variables I mean constants. So if I could do something along the lines of:

    create constant SQL_TSI_DAY = 1;
    create constant SQL_TSI_MONTH = 2;
    create constant SQL_TSI_YEAR = 3;

    - then I could call my timestampdiff function with SQL_TSI_DAY and not as a string of 'SQL_TSI_DAY'.

    Basically I don't want to have to alter the original SQL statement if at all possible, I want to leave it as:

    select {fn TIMESTAMPDIFF(SQL_TSI_DAY, createddate, receiveddate)}
    from stockbatches

    thanks

    Andy

  5. #5
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    No, this is not possible...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  6. #6
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Using my above timestampdiff function I get an error with the following:

    select receiveddate, createddate, {fn NOW()}, {fn TIMESTAMPDIFF2('SQL_TSI_DAY', createddate, {fn NOW()})}
    from stockbatches

    error is:

    "A syntax error has occurred."

    Seems that {fn NOW()} does not produce a date object or something.

    Anyone know how to fix this?

    thanks in advance

    Andy

  7. #7
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    replace now() for current
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

Posting Permissions

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