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
return sysdate - completionTime + 5 hours
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?
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?
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.