UTC Datetime to Timezone conversion
Mark asked whether Informix has a built-in function to convert a DATETIME stored in UTC to a specific time zone. Answers: you can simply add an INTERVAL offset (e.g. + INTERVAL(5:30) HOUR TO MINUTE), optionally driven by a zone-offset lookup table, but maintaining DST/zone rules (Olson data changes) is the hard part. Art suggested DBINFO('utc_to_datetime'), which Jonathan clarified takes a Unix epoch seconds argument and uses the server's TZ, not the client's. Gary posted sample utc2local/local2utc stored procedures built on DBINFO for his UTC+10/+11 case. Mark considered the question answered.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
All, is there any function in Informix that can take a datetime stored in UTC and convert to a timezone specific time ? Thanks in advance , Mark
On Mon, May 16, 2011 at 06:56, MARK JALKIEWICZ <mark.jalkiewicz@verizon.net>wrote: > is there any function in Informix that can take a datetime stored in UTC > and > convert to a timezone specific time ? > Yes, or no...suppose you want to convert to IST (India Standard Time, UTC+05:30), you could write: SELECT utc_datetime + INTERVAL(+5:30) HOUR TO MINUTE FROM TheTable If you define a table that maps time zone name to time zone offset from UTC, then you could handle time zone names too. The difficulty is the volatility of the mapping table - you have to deal with changes in rules for time zone switching, which happens a lot. There's a time zone database called the Olson database which changes up to about 20 times a year as different countries change their minds about when to switch between winter and summer (standard and daylight saving) time. Sometimes, the decision occurs a day or so before the change occurs. Sometimes, as with Samoa at the end of 2011 (http://www.worldtimezone.com/dst_news/, http://www.bbc.co.uk/worldservice/learningenglish/language/wordsinthenews/2011/0 5/110513_witn_samoa_time_page.shtml), it is predicted in advance and will have radical affects (there'll be a whole day missing as Samoa moves from UTC-11 to UCT+13; when it does so, it will skip a whole day. Also, you have to deal with historic changes vs future (predicted) changes. It gets quite complex, not least because humans are inventive about the rules that might be applied. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --000e0cd48c8a98e32704a3652c70
dbinfo('utc_to_datetime')
Conveys using the default timezone.
Art
On May 16, 2011 8:56 AM, "MARK JALKIEWICZ" <mark.jalkiewicz@verizon.net>
wrote:
> All,
>
> is there any function in Informix that can take a datetime stored in UTC
and
> convert to a timezone specific time ?
>
> Thanks in advance ,
>
> Mark
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--bcaec50166b7f3a74404a36693ed
On Mon, May 16, 2011 at 08:47, Art Kagel <art.kagel@gmail.com> wrote:
> dbinfo('utc_to_datetime')>
This requires two arguments - the second should be a Unix timestamp (integer
number of seconds since 1970-01-01 00:00:00Z).
For example:
select dbinfo("utc_to_datetime", 2132222222) from dual
2037-07-26 11:57:02
select dbinfo("utc_to_datetime", 0) from dual
1970-01-01 00:00:00
The results are also in Zulu (UTC) time since I run my servers in UTC.
> Conveys using the default timezone.
>
The default timezone is the value of TZ in the server's own environment -
unrelated to the client's timezone.
> On May 16, 2011 8:56 AM, "MARK JALKIEWICZ" <mark.jalkiewicz@verizon.net>
> wrote:
> > is there any function in Informix that can take a datetime stored in UTC
> and
> > convert to a timezone specific time ?
>
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--0016e646908c5afe8604a368eba9
Art & Jonathan, Thanks a million.. Mark
I was looking for this years ago, found a few SP created by thinking was
Jonathan and changed a bit for our situation to the two SP below. They've been
working (in IDS 7.3) ok to me. We are UTC+10 hour in usual time and +11 hour
in daylight saving time. You shall set these two variables in local2utc() by
your local time. Do test your time conversion functions carefully especially
when it is a lunar year eg Feb 28,29. Please let me know if there is any
issues.
Regards,
Gary
-----
CREATE PROCEDURE utc2local(dt DATETIME YEAR TO FRACTION(3))
RETURNING DATETIME YEAR TO FRACTION(3);
DEFINE ut DECIMAL(12);
define s varchar(12);
define m int;
DEFINE dd INTERVAL DAY(9) TO DAY;
DEFINE hh INTERVAL HOUR TO HOUR;
DEFINE mm INTERVAL MINUTE TO MINUTE;
DEFINE ss INTERVAL SECOND TO FRACTION(5);
DEFINE st,rv DATETIME YEAR TO FRACTION(5);
let st = DATETIME(1970-01-01 00:00:00.00000) YEAR TO FRACTION(5);
let dd = extend(dt,year to day) - st;
let s = dd;
let m = s;
let ut = m*86400;
let hh = extend(dt,hour to hour) - st;
let s = hh;
let m = s;
let ut = ut + m*3600;
let mm = extend(dt,minute to minute) - st;
let s = mm;
let m = s;
let ut = ut + m*60;
let ss = extend(dt,second to fraction(5)) - DATETIME(0.00000) SECOND TO
FRACTION(5);
let rv = extend(DBINFO('utc_to_datetime',ut),YEAR TO FRACTION(5)) + ss;
RETURN rv;
END PROCEDURE;
CREATE PROCEDURE local2utc(dt DATETIME YEAR TO FRACTION(3))
RETURNING DATETIME YEAR TO FRACTION(3);
DEFINE ut,rv DATETIME YEAR TO FRACTION(5);
let rv = dt - 10 units hour; --time difference in hours
let ut = utc2local(rv);
if dt = ut then
return rv;
else
let rv = dt - 11 units hour; --time difference in hours when in daylight
saving period.
return rv;
end if;
END PROCEDURE;