Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2014
    Posts
    2

    Unanswered: interval between times in different time zones Informix

    I have an Informix db populated by a hard-coded program. I cannot change the way the data is stored, only how I pull from it.

    I have a problem with one field that is stored in UTC time format. Basically, the problem is that this particular field, completionTime, stores a time stamp in UTC when the end users accomplish a particular task. Those who have tasked me with my work have asked me to pull the length of time since the task was done. so this should be a simple matter of subtracting sysdate - completionTime, except that completion time is stored in UTC sysdate is stored in the local timezone, so there is a 5-hour offset.

    My first solution was to add 5 hours, easy peasy, but then this was messed up when daylight savings started. So my current solution is something like this psuedo-code:

    if sysdate - (subquery returning max(completionTime)) + 5 hrs > 3600 then
    return sysdate - completionTime + 4 hours
    else
    return sysdate - completionTime + 5 hours
    end

    This works, but the problem is this logic is in-line with the query so I am executing this subquery for every row of the recordset. Fortunately there are only 22 or so records for the current project, only ~20ms to execute, but I can foresee a time when this goes up to a couple thousand records.

    So there has got to be a better way to run this. I've tried using Informix's "set environment" timezone setting before querying, and that gives an error. There is no UTC version of current or sysdate for Informix (like T-SQL's getUTCDate() function or MySQL's UTC_DATE() function), so I can't simplify my query with sysdateUTC - completionTime. I don't think my user has access to create local variables, but even if I did, my logic would have to be similar. Is there a better way to do this?

    Jared

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


    I think you want to use the dbinfo("utc_to_datetime",utctime) stored procedure.

    considering myutctime as an integer variable, this would give something like:

    Code:
    SELECT dbinfo("utc_to_datetime",myutctime)
    FROM systables
    WHERE tabid = 1
    this piece will return a datetime data type, based on the TZ of the Informix server.

  3. #3
    Join Date
    Sep 2014
    Posts
    2
    That is a huge step in the right direction. But what is the best way to convert my datetime field to an integer from epoch? using your previous suggestions, this monster calculation is the best I can come up with:

    cast(cast(cast((sysdate - dbinfo("utc_to_datetime", cast(cast(cast((ASD.completionTime - TO_DATE('Friday January 1, 2010 0:00',
    '%A %B %d, %Y %R')) as interval second(9) to second) as char(10)) as int) +1262304000)) as interval second(9) to second) as char(10)) as int) as timeinstate

    That is 6 hairy casts and manually calculating the number of seconds from the epoch to now. This isn't what you are suggesting, right?

    Jared

  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Jared,

    The base is that you need to operate a substraction between two data of the same datatype.
    Either you convert both into a datetime datatype, or or convert both into an INTEGER/EPOCH style and get this into an INTERVAL datatype

    The first solution can be achieved with dbinfo('utc_to_datetime')

    The second way can be achieved with a stored procedure written by my Mister Jonathan Leffler, who is a longtimer in Informixian and IBMer (also the gardian or Perl DBD for Informix).

    Check this post
    BTW you could name this procedure 'datetime_to_utc',although it might appear as a reserved word in a feature version.

    I think you now have all the elements to achieve your goal.
    Eric

Tags for this Thread

Posting Permissions

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