IDS Forum

Re: "AT TIME ZONE"

Posted By: Art Kagel
Date: Tuesday, 15 December 2009, at 12:14 p.m.

In Response To: RE: "AT TIME ZONE" (Konikoff, Robert W MAJ RET)

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 #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.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--000e0cd1ff3eeb5e3f047ac786ac

Messages In This Thread

• "AT TIME ZONE"
  Adam Tauno Williams -- Monday, 7 December 2009, at 9:49 a.m.
  • Re: "AT TIME ZONE"
    Art Kagel -- Monday, 7 December 2009, at 4:09 p.m.
    • RE: "AT TIME ZONE"
      Everett Mills -- Monday, 7 December 2009, at 4:40 p.m.
      • Re: RE: "AT TIME ZONE"
        DAVE GRIFFEN -- Tuesday, 8 December 2009, at 1:02 p.m.
        • Re: RE: "AT TIME ZONE"
          Art Kagel -- Tuesday, 8 December 2009, at 2:51 p.m.
          • RE: RE: "AT TIME ZONE"
            Everett Mills -- Tuesday, 8 December 2009, at 4:25 p.m.
            • Re: RE: "AT TIME ZONE"
              Art Kagel -- Tuesday, 8 December 2009, at 4:36 p.m.
        • Re: RE: "AT TIME ZONE"
          Jonathan Leffler -- Monday, 14 December 2009, at 2:42 a.m.
          • RE: "AT TIME ZONE"
            Clifton Bean -- Monday, 14 December 2009, at 8:32 a.m.
            • Re: "AT TIME ZONE"
              Art Kagel -- Monday, 14 December 2009, at 9:25 a.m.
              • RE: "AT TIME ZONE"
                Konikoff, Robert W MAJ RET -- Tuesday, 15 December 2009, at 10:06 a.m.
                • RE: "AT TIME ZONE"
                  Adam Tauno Williams -- Tuesday, 15 December 2009, at 11:28 a.m.
                • Re: "AT TIME ZONE"
                  Art Kagel -- Tuesday, 15 December 2009, at 12:14 p.m.
                  • RE: "AT TIME ZONE"
                    Konikoff, Robert W MAJ RET -- Tuesday, 15 December 2009, at 12:50 p.m.
              • RE: "AT TIME ZONE"
                Clifton Bean -- Tuesday, 15 December 2009, at 10:34 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.