Oninit cdi / Archive
Talk to an expert →

fractions between two datetime columns

Posted in 2011

Topics: General Discussion

CHUAN LU — — source: IIUG Forums & Mailing Lists

Hi, Do anybody know how to calcluate total fractions between two "datetime year to fraction" columns in 11.5 ? thanks for your time.

↪ replying to CHUAN LU
Alexandre Marini — — source: IIUG Forums & Mailing Lists

Hello. Maybe the official docs help you, somehow..... http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlr.doc/ids _sqr_153.htm Best regards. Em 07/01/2011 02:32, CHUAN LU escreveu: > Hi, > > Do anybody know how to calcluate total fractions between two "datetime year to > fraction" columns in 11.5 ? thanks for your time. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11

↪ replying to CHUAN LU
Art Kagel — — source: IIUG Forums & Mailing Lists

(second_datetime - first_datetime)::interval fraction to fraction(5) Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Fri, Jan 7, 2011 at 1:32 AM, CHUAN LU <luchuan114@sina.com> wrote: > Hi, > > Do anybody know how to calcluate total fractions between two "datetime year > to > fraction" columns in 11.5 ? thanks for your time. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf3054ace3b0422604993f9e2b

↪ replying to Art Kagel
Jonathan Leffler — — source: IIUG Forums & Mailing Lists

On Fri, Jan 7, 2011 at 03:07, Art Kagel <art.kagel@gmail.com> wrote: > (second_datetime - first_datetime)::interval fraction to fraction(5) > That should only work if the difference is less than one second. You probably need: (second_time - first_time)::INTERVAL SECOND(9) TO FRACTION(5) That gives you coverage for time gaps up to about 30 years. I went to check...I don't much like what I found: select (datetime(2011-01-07 08:23:45.12345) year to fraction(5) - datetime(2011-01-07 08:23:44.12346) year to fraction(5))::INTERVAL FRACTION TO FRACTION(5) from dual; .99999 select (datetime(2011-01-07 08:23:45.12345) year to fraction(5) - datetime(2011-01-07 08:23:44.12345) year to fraction(5))::INTERVAL FRACTION TO FRACTION(5) from dual; .00000 select (datetime(2011-01-07 08:23:45.12345) year to fraction(5) - datetime(2011-01-07 08:23:44.12344) year to fraction(5))::INTERVAL FRACTION TO FRACTION(5) from dual; .00001 The alignment is clearer in fixed width fonts...but it seems that FRACTION TO FRACTION(5) simply drops the whole seconds. I think that is a bug. The corresponding behaviour for INTERVAL SECOND TO FRACTION(5) - which is a shorthand for INTERVAL SECOND(2) TO FRACTION(5) - is as follows: select (datetime(2011-01-07 08:23:45.12345) year to fraction(5) - datetime(2011-01-07 08:22:05.12346) year to fraction(5))::INTERVAL SECOND(2) TO FRACTION(5) from dual; 99.99999 select (datetime(2011-01-07 08:23:45.12345) year to fraction(5) - datetime(2011-01-07 08:22:05.12345) year to fraction(5))::INTERVAL SECOND(2) TO FRACTION(5) from dual; SQL -1265: Overflow occurred on a datetime or interval operation. SQLSTATE: IX000 at <<temp>>:8 select (datetime(2011-01-07 08:23:45.12345) year to fraction(5) - datetime(2011-01-07 08:22:05.12344) year to fraction(5))::INTERVAL SECOND(2) TO FRACTION(5) from dual; SQL -1265: Overflow occurred on a datetime or interval operation. SQLSTATE: IX000 at <<temp>>:11 I'm certainly not keen on the inconsistency between the two - whether anything will happen to fix the issue is rather more debatable. I tested IDS 11.70.FC1 (it happened to be on a Linux x86/64 machine, but I'd expect the same result anywhere, and regardless of version of IDS). > On Fri, Jan 7, 2011 at 1:32 AM, CHUAN LU <luchuan114@sina.com> wrote: > > Does anybody know how to calcluate total fractions between two "datetime > year > > to fraction" columns in 11.5 ? thanks for your 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." --001636c5c241e25dfd0499442dd1

↪ replying to Jonathan Leffler
CHUAN LU — — source: IIUG Forums & Mailing Lists

Thanks, The following is what I need. (second_time - first_time)::INTERVAL SECOND(9) TO FRACTION(5)

Related threads