"AT TIME ZONE"
A user asked for an Informix equivalent of PostgreSQL's "SELECT col AT TIME ZONE 'GMT'". The answer: Informix has no time zone support — DATETIME values are stored without any zone and the server assumes/computes everything in its own local time (DBINFO('get_tz') only reports the client's TZ, which the server ignores). Suggested workarounds were rolling your own SPL/UDT conversion using tztab data, or simply running the server in UTC and letting applications handle offsets; Art Kagel described an unfinished zoned-datetime UDT project. No built-in solution exists.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
In PostgreSQL 8.x I can view a time or date value in a specified time zone with the following syntax: SELECT start_date AT TIME ZONE 'GMT' FROM date_x WHERE date_id = 18850; I've searched around and around and been unable to find any equivalent syntax for Informix? Isn't there such a thing?
Informix does not support time zones. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Dec 7, 2009 at 9:49 AM, Adam Tauno Williams <adam@morrison-ind.com>wrote: > In PostgreSQL 8.x I can view a time or date value in a specified time > zone with the following syntax: > > SELECT start_date AT TIME ZONE 'GMT' FROM date_x WHERE date_id = 18850; > > I've searched around and around and been unable to find any equivalent > syntax for Informix? Isn't there such a thing? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001517447a74976cff047a29deed
> -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art Kagel > Sent: Monday, December 07, 2009 3:09 PM > To: ids@iiug.org > Subject: Re: "AT TIME ZONE" [18288] > > Informix does not support time zones. > > Art On the other hand, it wouldn't be all that hard to write your own function to mimic the effect. You would need to load a table with the data from a Unix/Linux tztab (or something similar) and use the $TZ environment variable or a little table hold your default to see what your current time zone is. --EEM > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other > organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any > entity > with which I am affiliated nor those of the entities themselves. > > On Mon, Dec 7, 2009 at 9:49 AM, Adam Tauno Williams > <adam@morrison-ind.com>wrote: > > > In PostgreSQL 8.x I can view a time or date value in a specified time > > zone with the following syntax: > > > > SELECT start_date AT TIME ZONE 'GMT' FROM date_x WHERE date_id = > 18850; > > > > I've searched around and around and been unable to find any > equivalent > > syntax for Informix? Isn't there such a thing? > > > > > > > > > *********************************************************************** > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001517447a74976cff047a29deed > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.
No need to load current time zone into a table. It is readily available... select dbinfo('get_tz') from systables where tabid = 1
That's only current time-zone. The OP was interested in fetching a time based on ANY time-zone. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Dec 8, 2009 at 1:02 PM, DAVE GRIFFEN <dgriffen@finishline.com>wrote: > No need to load current time zone into a table. It is readily available... > > select dbinfo('get_tz') > from systables > where tabid = 1 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5986bb7a485047a3ce608
> -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art Kagel > Sent: Tuesday, December 08, 2009 1:52 PM > To: ids@iiug.org > Subject: Re: RE: "AT TIME ZONE" [18301] > > That's only current time-zone. The OP was interested in fetching a time > based on ANY time-zone. > > Art True, but in order to be able to calculate time in another zone, you first need to know what time zone the data was recorded in. --EEM > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other > organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any > entity > with which I am affiliated nor those of the entities themselves. > > On Tue, Dec 8, 2009 at 1:02 PM, DAVE GRIFFEN > <dgriffen@finishline.com>wrote: > > > No need to load current time zone into a table. It is readily > available... > > > > select dbinfo('get_tz') > > from systables > > where tabid = 1 > > > > > > > > > *********************************************************************** > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001636c5986bb7a485047a3ce608 > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.
Yes, but if you record the time in UTC, converting it from local time, or the expressed TZ, when it is entered, then all time/date calculations and reportings in any TZ are consistent. Unfortunately, IDS records and stores DATETIME values without a timezone in whatever format/TZ they were entered in so there's no way to know what TZ a recorded time represents nor to know how to convert it to some other TZ. That was OK when most businesses were country-centric, but in this global age, when even the isolationist Mainland Chinese organizations conduct business globally, it doesn't fly any longer. I'm working on a zoned time data type, myself and I'll publish it once I've had time to work out all of the nits and gnats. If anyone has a need for a REAL time datatype, and wants to fund the work in exchange for the inclusion of specific features they need, please let me know privately. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Dec 8, 2009 at 4:25 PM, Everett Mills < Everett.Mills@nationalbeef.com> wrote: > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Art Kagel > > Sent: Tuesday, December 08, 2009 1:52 PM > > To: ids@iiug.org > > Subject: Re: RE: "AT TIME ZONE" [18301] > > > > That's only current time-zone. The OP was interested in fetching a time > > based on ANY time-zone. > > > > Art > > True, but in order to be able to calculate time in another zone, you first > need to know what time zone the data was recorded in. > > --EEM > > > > > Art S. Kagel > > Oninit (www.oninit.com) > > IIUG Board of Directors (art@iiug.org) > > > > Disclaimer: Please keep in mind that my own opinions are my own > > opinions and > > do not reflect on my employer, Oninit, the IIUG, nor any other > > organization > > with which I am associated either explicitly or implicitly. Neither do > > those opinions reflect those of other individuals affiliated with any > > entity > > with which I am affiliated nor those of the entities themselves. > > > > On Tue, Dec 8, 2009 at 1:02 PM, DAVE GRIFFEN > > <dgriffen@finishline.com>wrote: > > > > > No need to load current time zone into a table. It is readily > > available... > > > > > > select dbinfo('get_tz') > > > from systables > > > where tabid = 1 > > > > > > > > > > > > > > *********************************************************************** > > ******** > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001636c5986bb7a485047a3ce608 > > > > > > *********************************************************************** > > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015174a047ae7a87c047a3e5c62
On Tue, Dec 8, 2009 at 10:02, DAVE GRIFFEN <dgriffen@finishline.com> wrote: > No need to load current time zone into a table. It is readily available... > > select dbinfo('get_tz') > from systables > where tabid = 1 > Although this returns the time zone of the client, that value is ignored in the server. All server time computations are performed in the time zone of the server. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com 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." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. Jonathan Swift<http://www.brainyquote.com/quotes/authors/j/jonathan_swift.html>; - "May you live every day of your life." --000e0cd11310871b8b047aab689c
So is it worth having the engine store all datetimes as timezone GMT+0 and when the datetime is needed from a transaction it displays the datetime in local server time, unless overriden by an environment variable? Take care. Clifton > To: ids@iiug.org > From: jleffler.iiug@gmail.com > Subject: Re: RE: "AT TIME ZONE" [18369] > Date: Mon, 14 Dec 2009 02:42:03 -0500 > > On Tue, Dec 8, 2009 at 10:02, DAVE GRIFFEN <dgriffen@finishline.com> wrote: > > > No need to load current time zone into a table. It is readily available... > > > > select dbinfo('get_tz') > > from systables > > where tabid = 1 > > > > Although this returns the time zone of the client, that value is ignored in > the server. All server time computations are performed in the time zone of > the server. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > 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." > NB: Please do not use this email for correspondence. > I don't necessarily read it every week, even. > Jonathan > Swift<http://www.brainyquote.com/quotes/authors/j/jonathan_swift.html>; > - "May you live every day of your life." > > --000e0cd11310871b8b047aab689c > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Hotmail: Trusted email with Microsofts powerful SPAM protection. http://clk.atdmt.com/GBL/go/177141664/direct/01/
No! Whatever time you store the server ASSUMES that it is in local time. It does not conversions from TZ at all. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Dec 14, 2009 at 8:32 AM, Clifton Bean <clifton_bean@hotmail.com>wrote: > So is it worth having the engine store all datetimes as timezone GMT+0 and > when the datetime is needed from a transaction it displays the datetime in > local server time, unless overriden by an environment variable? > > Take care. > > Clifton > > > To: ids@iiug.org > > From: jleffler.iiug@gmail.com > > Subject: Re: RE: "AT TIME ZONE" [18369] > > Date: Mon, 14 Dec 2009 02:42:03 -0500 > > > > On Tue, Dec 8, 2009 at 10:02, DAVE GRIFFEN <dgriffen@finishline.com> > wrote: > > > > > No need to load current time zone into a table. It is readily > available... > > > > > > select dbinfo('get_tz') > > > from systables > > > where tabid = 1 > > > > > > > Although this returns the time zone of the client, that value is ignored > in > > the server. All server time computations are performed in the time zone > of > > the server. > > > > -- > > Jonathan Leffler #include <disclaimer.h> > > Email: jleffler@earthlink.net, jleffler@us.ibm.com > > 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." > > NB: Please do not use this email for correspondence. > > I don't necessarily read it every week, even. > > Jonathan > > Swift<http://www.brainyquote.com/quotes/authors/j/jonathan_swift.html>; > > - "May you live every day of your life." > > > > --000e0cd11310871b8b047aab689c > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > _________________________________________________________________ > Hotmail: Trusted email with Microsofts powerful SPAM protection. > http://clk.atdmt.com/GBL/go/177141664/direct/01/ > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0ce0f92886d20c047ab10ab5
Art: You Wrote: > Whatever time you store the server ASSUMES that it is in local time. Couldn't you write an SPL with two arguments, (TimeToConvert, Desired_TimeZone), and do a simple time offset? It doesn't seem that complicated to implement. An analysis of the data should yield which is the most common time zones and you can build a select statement with those zones first... If there is a more efficient way, I'd like to know. We have to do this kind of stuff fairly frequently for custom data codes. Rob -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Monday, December 14, 2009 8:25 AM To: ids@iiug.org Subject: Re: "AT TIME ZONE" [18371] No! Whatever time you store the server ASSUMES that it is in local time. It does not conversions from TZ at all. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Dec 14, 2009 at 8:32 AM, Clifton Bean <clifton_bean@hotmail.com>wrote: > So is it worth having the engine store all datetimes as timezone GMT+0 and > when the datetime is needed from a transaction it displays the datetime in > local server time, unless overriden by an environment variable? > > Take care. > > Clifton > > > To: ids@iiug.org > > From: jleffler.iiug@gmail.com > > Subject: Re: RE: "AT TIME ZONE" [18369] > > Date: Mon, 14 Dec 2009 02:42:03 -0500 > > > > On Tue, Dec 8, 2009 at 10:02, DAVE GRIFFEN <dgriffen@finishline.com> > wrote: > > > > > No need to load current time zone into a table. It is readily > available... > > > > > > select dbinfo('get_tz') > > > from systables > > > where tabid = 1 > > > > > > > Although this returns the time zone of the client, that value is ignored > in > > the server. All server time computations are performed in the time zone > of > > the server. > > > > -- > > Jonathan Leffler #include <disclaimer.h> > > Email: jleffler@earthlink.net, jleffler@us.ibm.com > > 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." > > NB: Please do not use this email for correspondence. > > I don't necessarily read it every week, even. > > Jonathan > > Swift<http://www.brainyquote.com/quotes/authors/j/jonathan_swift.html>; > > - "May you live every day of your life." > > > > --000e0cd11310871b8b047aab689c > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > _________________________________________________________________ > Hotmail: Trusted email with Microsoft's powerful SPAM protection. > http://clk.atdmt.com/GBL/go/177141664/direct/01/ > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0ce0f92886d20c047ab10ab5 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Art: You misread what I was sayin'. I was basically wondering "out loud" about a possible FEATURE REQUEST to IBM Informix. Take care. Clifton > To: ids@iiug.org > From: art.kagel@gmail.com > Subject: Re: "AT TIME ZONE" [18371] > Date: Mon, 14 Dec 2009 09:25:16 -0500 > > No! Whatever time you store the server ASSUMES that it is in local time. > It does not conversions from TZ at all. > > Art > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any entity > with which I am affiliated nor those of the entities themselves. > > On Mon, Dec 14, 2009 at 8:32 AM, Clifton Bean <clifton_bean@hotmail.com>wrote: > > > So is it worth having the engine store all datetimes as timezone GMT+0 and > > when the datetime is needed from a transaction it displays the datetime in > > local server time, unless overriden by an environment variable? > > > > Take care. > > > > Clifton > > > > > To: ids@iiug.org > > > From: jleffler.iiug@gmail.com > > > Subject: Re: RE: "AT TIME ZONE" [18369] > > > Date: Mon, 14 Dec 2009 02:42:03 -0500 > > > > > > On Tue, Dec 8, 2009 at 10:02, DAVE GRIFFEN <dgriffen@finishline.com> > > wrote: > > > > > > > No need to load current time zone into a table. It is readily > > available... > > > > > > > > select dbinfo('get_tz') > > > > from systables > > > > where tabid = 1 > > > > > > > > > > Although this returns the time zone of the client, that value is ignored > > in > > > the server. All server time computations are performed in the time zone > > of > > > the server. > > > > > > -- > > > Jonathan Leffler #include <disclaimer.h> > > > Email: jleffler@earthlink.net, jleffler@us.ibm.com > > > 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." > > > NB: Please do not use this email for correspondence. > > > I don't necessarily read it every week, even. > > > Jonathan > > > Swift<http://www.brainyquote.com/quotes/authors/j/jonathan_swift.html>; > > > - "May you live every day of your life." > > > > > > --000e0cd11310871b8b047aab689c > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > _________________________________________________________________ > > Hotmail: Trusted email with Microsofts powerful SPAM protection. > > http://clk.atdmt.com/GBL/go/177141664/direct/01/ > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --000e0ce0f92886d20c047ab10ab5 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Hotmail: Free, trusted and rich email service. http://clk.atdmt.com/GBL/go/171222984/direct/01/
On Tue, 2009-12-15 at 10:06 -0500, Konikoff, Robert W MAJ RET wrote: > Art: > You Wrote: > Whatever time you store the server ASSUMES that it is in local > time. > Couldn't you write an SPL with two arguments, (TimeToConvert, > Desired_TimeZone), and do a > simple time offset? It doesn't seem that complicated to implement. For one you'd need the time zone tables, and to maintain them. Which is ugly. Also if you don't know the timezone of TimeToConvert you can't convert it, unless you again assume local time. This is why zone aware datetime types are handy. It would seem the best practice is to put the server into GMT/UTC and make the application(s) deal with the offset. > An analysis of the data should yield which is the most common time zones > and you can build a select statement with those zones first... > If there is a more efficient way, I'd like to know. We have to do this kind of > stuff fairly frequently for custom data codes.
Rob, This issue is not as simple as it sounds on the surface. I've been dealing with time, time arithmetic, and timezone issues for over 20 years. It has become one of my favorite topics if for no other reasons than that 1- it keeps popping up and 2 - it is an endlessly debatable topic. It's a Pandora's box. Every time you think that you have resolved all of the issues, another one pops up. I've put some work into building a set of User Defined Types which will handle timezones correctly, but it's not complete. There are two basic types, one, DISTINCT OF DATETIME includes built-in conversions from DATE, DATETIME and the other type which convert everything entered into UTC for storage in a normal DATETIME. As a distinct type, if it is fetched into a raw DATETIME it converts into local time based on the server's TZ or the user's TZ both of which are available within the server. The other is an opaque type that stores the datetime data as entered plus the timezone represented. The default for conversion from DATETIME is to assume the server's TZ and store that. Conversion to text would return either ANSI 8601 format or W3C format (which are close), but which should be the default? Conversions from text will allow full and partial ANSI 8601 and W3C format filling in missing portions from current time or date. The second type may not even have to be a storable type but only a returnable type while only storing the UTC datetime. I haven't decided finally on that issue. I think that this is the correct way to handle things and if the conversion routines are written in 'C' the efficiency is not a problem. I already have a string to datetime conversion function I wrote long ago that understands timezones using the GNU timezone database and function library. It can handle just about anything you can throw at it including relative datetimes (like 'three days ago'), but it has to be made multi-thread safe, I have to modify the output format to output a DATETIME, and then there are the conversion decisions that have to be made. I haven't had much time I can dedicate to completing the work as billable work and the need to pursue billable work keep intruding. Ahh for the ideal world or the blush of youth where I didn't need to worry about such mundane things as feeding a family. I had a client who had major problems caused by the way IDS handles datetimes. They said they were interested in funding the work, but they backed out preferring to use built-in DATETIME and have continued to muddle through. I think they hoped I'd finish it for free and publish. I'd like to do that, but as I said, the real world encroaches. There's not much coding that has to be done. The entire project is perhaps a month of dedicated time to work out all of the kinks - time calculations have many non-obvious side effects that I have to take into consideration. For example is the default to return user TZ or server TZ? Another, what should the function return for time arithmetic when a user adds 1 day to the day before DST starts in the target timezone? If the UTC equivalent of 4PM local was stored do you return 4PM or 5PM? There are those who would argue for either option. And if it's to be configurable, should it be a site setting or dynamic - able to be different for different queries. If dynamic, how does one make that selection work in an SQL statement? Do I need to create a host language library (or libraries) to deal with all of this as well? Another, what does "datetime + 1 year" mean the year before a leap year? There are applications which would require the same date a year later and others which require exactly 365 days later. And if the current year is a leap year, does "next year" mean 365 days later or 366 days later? Anyway, thanks all for putting up with my rantings on one of my favorite topics. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Dec 15, 2009 at 10:06 AM, Konikoff, Robert W MAJ RET < rob.konikoff@us.army.mil> wrote: > Art: > You Wrote: > Whatever time you store the server ASSUMES that it is in local > time. > > Couldn't you write an SPL with two arguments, (TimeToConvert, > Desired_TimeZone), and do a > simple time offset? It doesn't seem that complicated to implement. An > analysis > of the > data should yield which is the most common time zones and you can build a > select > statement with those zones first... > > If there is a more efficient way, I'd like to know. We have to do this kind > of > stuff > fairly frequently for custom data codes. > Rob > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Monday, December 14, 2009 8:25 AM > To: ids@iiug.org > Subject: Re: "AT TIME ZONE" [18371] > > No! Whatever time you store the server ASSUMES that it is in local time. > It does not conversions from TZ at all. > > Art > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any > entity > with which I am affiliated nor those of the entities themselves. > > On Mon, Dec 14, 2009 at 8:32 AM, Clifton Bean <clifton_bean@hotmail.com > >wrote: > > > So is it worth having the engine store all datetimes as timezone GMT+0 > and > > when the datetime is needed from a transaction it displays the datetime > in > > local server time, unless overriden by an environment variable? > > > > Take care. > > > > Clifton > > > > > To: ids@iiug.org > > > From: jleffler.iiug@gmail.com > > > Subject: Re: RE: "AT TIME ZONE" [18369] > > > Date: Mon, 14 Dec 2009 02:42:03 -0500 > > > > > > On Tue, Dec 8, 2009 at 10:02, DAVE GRIFFEN <dgriffen@finishline.com> > > wrote: > > > > > > > No need to load current time zone into a table. It is readily > > available... > > > > > > > > select dbinfo('get_tz') > > > > from systables > > > > where tabid = 1 > > > > > > > > > > Although this returns the time zone of the client, that value is > ignored > > in > > > the server. All server time computations are performed in the time zone > > of > > > the server. > > > > > > -- > > > Jonathan Leffler #inc
Art: I enjoy the ranting! (http://baarf.com/, and http://www.miracleas.com/BAARF/RAID5_versus_RAID10.txt)! As to youth vs. feeding a family, I know all about billable hours and 6 minute increments on the time sheet. :) Thanks for the education. On the surface, you don't realize the intricacies of such a simple thing as time. We just upgraded to 7.31 last year since all our date math was handled in custom programming to handle Julian date math rather than 'real' date math. Rob -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Tuesday, December 15, 2009 11:15 AM To: ids@iiug.org Subject: Re: "AT TIME ZONE" [18394] Rob, This issue is not as simple as it sounds on the surface. I've been dealing with time, time arithmetic, and timezone issues for over 20 years. It has become one of my favorite topics if for no other reasons than that 1- it keeps popping up and 2 - it is an endlessly debatable topic. It's a Pandora's box. Every time you think that you have resolved all of the issues, another one pops up. I've put some work into building a set of User Defined Types which will handle timezones correctly, but it's not complete. There are two basic types, one, DISTINCT OF DATETIME includes built-in conversions from DATE, DATETIME and the other type which convert everything entered into UTC for storage in a normal DATETIME. As a distinct type, if it is fetched into a raw DATETIME it converts into local time based on the server's TZ or the user's TZ both of which are available within the server. The other is an opaque type that stores the datetime data as entered plus the timezone represented. The default for conversion from DATETIME is to assume the server's TZ and store that. Conversion to text would return either ANSI 8601 format or W3C format (which are close), but which should be the default? Conversions from text will allow full and partial ANSI 8601 and W3C format filling in missing portions from current time or date. The second type may not even have to be a storable type but only a returnable type while only storing the UTC datetime. I haven't decided finally on that issue. I think that this is the correct way to handle things and if the conversion routines are written in 'C' the efficiency is not a problem. I already have a string to datetime conversion function I wrote long ago that understands timezones using the GNU timezone database and function library. It can handle just about anything you can throw at it including relative datetimes (like 'three days ago'), but it has to be made multi-thread safe, I have to modify the output format to output a DATETIME, and then there are the conversion decisions that have to be made. I haven't had much time I can dedicate to completing the work as billable work and the need to pursue billable work keep intruding. Ahh for the ideal world or the blush of youth where I didn't need to worry about such mundane things as feeding a family. I had a client who had major problems caused by the way IDS handles datetimes. They said they were interested in funding the work, but they backed out preferring to use built-in DATETIME and have continued to muddle through. I think they hoped I'd finish it for free and publish. I'd like to do that, but as I said, the real world encroaches. There's not much coding that has to be done. The entire project is perhaps a month of dedicated time to work out all of the kinks - time calculations have many non-obvious side effects that I have to take into consideration. For example is the default to return user TZ or server TZ? Another, what should the function return for time arithmetic when a user adds 1 day to the day before DST starts in the target timezone? If the UTC equivalent of 4PM local was stored do you return 4PM or 5PM? There are those who would argue for either option. And if it's to be configurable, should it be a site setting or dynamic - able to be different for different queries. If dynamic, how does one make that selection work in an SQL statement? Do I need to create a host language library (or libraries) to deal with all of this as well? Another, what does "datetime + 1 year" mean the year before a leap year? There are applications which would require the same date a year later and others which require exactly 365 days later. And if the current year is a leap year, does "next year" mean 365 days later or 366 days later? Anyway, thanks all for putting up with my rantings on one of my favorite topics. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Dec 15, 2009 at 10:06 AM, Konikoff, Robert W MAJ RET < rob.konikoff@us.army.mil> wrote: > Art: > You Wrote: > Whatever time you store the server ASSUMES that it is in local > time. > > Couldn't you write an SPL with two arguments, (TimeToConvert, > Desired_TimeZone), and do a > simple time offset? It doesn't seem that complicated to implement. An > analysis > of the > data should yield which is the most common time zones and you can build a > select > statement with those zones first... > > If there is a more efficient way, I'd like to know. We have to do this kind > of > stuff > fairly frequently for custom data codes. > Rob > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Monday, December 14, 2009 8:25 AM > To: ids@iiug.org > Subject: Re: "AT TIME ZONE" [18371] > > No! Whatever time you store the server ASSUMES that it is in local time. > It does not conversions from TZ at all. > > Art > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any > entity > with which I am affiliated nor those of the entities themselves. > > On Mon, Dec 14, 2009 at 8:32 AM, Clifton Bean <clifton_bean@hotmail.com > >wrote: > > > So is it worth having the engine store all datetimes as timezone GMT+0 > and > > when the datetime is needed from a transaction it displays the datetime > in > > local server time, unless overriden by an environment variable? > > > > Take care. > > > > C