Hour difference between two dates

Posted in 2010

nierjesh kumar — Fri, 23 Apr 2010 11:49:25 — source: IIUG Forums & Mailing Lists

Dear All I am using Informix 11.50 and I want to calculate the no of hours and second between two dates when I use following query select (extend(datetime(2020-01-01 18:16:00) year to second ,hour to minute)-extend(datetime(2010-01-01 15:21:00) year to second ,hour to minute)) as hours from dual it returns 2:55 but I want that It should return total no of hours between specified dates i.e. 84962 waiting for positive response. Regards- Nierjesh --0016361e7f36ab85560484e604f6

↪ replying to nierjesh kumar

Jack Parker — Fri, 23 Apr 2010 12:27:58 — source: IIUG Forums & Mailing Lists

You will need to break it into component parts. ((date - date)*24) + hour - hour You have the hour - hour component. Extend the same values year to day and subtract. j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of nierjesh kumar Sent: Friday, April 23, 2010 7:49 AM To: ids@iiug.org Subject: Hour difference between two dates [19808] Dear All I am using Informix 11.50 and I want to calculate the no of hours and second between two dates when I use following query select (extend(datetime(2020-01-01 18:16:00) year to second ,hour to minute)-extend(datetime(2010-01-01 15:21:00) year to second ,hour to minute)) as hours from dual it returns 2:55 but I want that It should return total no of hours between specified dates i.e. 84962 waiting for positive response. Regards- Nierjesh --0016361e7f36ab85560484e604f6 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to nierjesh kumar

Art Kagel — Fri, 23 Apr 2010 12:34:53 — source: IIUG Forums & Mailing Lists

select (datetime(2020-01-01 18:16:00) year to second - datetime(2010-01-01 15:21:00) year to second)::INTERVAL HOUR(9) TO HOUR from systables where tabid = 1; Art Art S. Kagel Advanced DataTools (www.advancedatatools.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, 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, Apr 23, 2010 at 7:49 AM, nierjesh kumar <nierjeshkumar@gmail.com>wrote: > Dear All > > I am using Informix 11.50 and I want to calculate the no of hours and > second between two dates > > when I use following query > > select (extend(datetime(2020-01-01 18:16:00) year to second ,hour to > minute)-extend(datetime(2010-01-01 15:21:00) year to second ,hour to > minute)) as hours from dual > it returns 2:55 > > but I want that It should return total no of hours between specified dates > i.e. 84962 > > waiting for positive response. > > Regards- > > Nierjesh > > --0016361e7f36ab85560484e604f6 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00504502cd4830c4050484e6a785

↪ replying to Art Kagel

nierjesh kumar — Fri, 23 Apr 2010 12:44:44 — source: IIUG Forums & Mailing Lists

Thanks Sir. On Fri, Apr 23, 2010 at 6:04 PM, Art Kagel <art.kagel@gmail.com> wrote: > select (datetime(2020-01-01 18:16:00) year to second - datetime(2010-01-01 > 15:21:00) year to second)::INTERVAL HOUR(9) TO HOUR > from systables > where tabid = 1; > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.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, 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, Apr 23, 2010 at 7:49 AM, nierjesh kumar > <nierjeshkumar@gmail.com>wrote: > > > Dear All > > > > I am using Informix 11.50 and I want to calculate the no of hours and > > second between two dates > > > > when I use following query > > > > select (extend(datetime(2020-01-01 18:16:00) year to second ,hour to > > minute)-extend(datetime(2010-01-01 15:21:00) year to second ,hour to > > minute)) as hours from dual > > it returns 2:55 > > > > but I want that It should return total no of hours between specified > dates > > i.e. 84962 > > > > waiting for positive response. > > > > Regards- > > > > Nierjesh > > > > --0016361e7f36ab85560484e604f6 > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --00504502cd4830c4050484e6a785 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016363b877c8437c00484e6ca97

↪ replying to Art Kagel

Jonathan Leffler — Fri, 23 Apr 2010 16:33:30 — source: IIUG Forums & Mailing Lists

On Fri, Apr 23, 2010 at 05:34, Art Kagel <art.kagel@gmail.com> wrote: > select (datetime(2020-01-01 18:16:00) year to second - datetime(2010-01-01 > 15:21:00) year to second)::INTERVAL HOUR(9) TO HOUR > from systables > where tabid = 1; > This is the basic answer - thanks, Art. The SQL has to be formatted so the DATETIME is on a single line, of course: SELECT (DATETIME(2020-01-01 18:16:00) YEAR TO SECOND - DATETIME(2010-01-01 15:21:00) YEAR TO SECOND)::INTERVAL HOUR(9) TO HOUR FROM Systables WHERE TabID = 1; This produces the answer: 87650 We have 10 years (including 2 leap years, 2012 and 2016 - 2020 is a leap year, but the leap day hasn't occurred in January) plus 2 hours, which should be: 10 * 365 * 24 + 2 * 24 + 2 = 87650 How did you derive your required output of 84962 hours? Also note that this returns an INTERVAL; if you want a number, you have to go via an intermediate CHAR representation: SELECT (DATETIME(2020-01-01 18:16:00) YEAR TO SECOND - DATETIME(2010-01-01 15:21:00) YEAR TO SECOND)::INTERVAL HOUR(9) TO HOUR::VARCHAR(32)::DECIMAL(9,0) FROM Systables WHERE TabID = 1; You also mentioned 'number of hours and seconds between two dates'... What did you have in mind here? Not interested in the minutes? Wanting the seconds over the number of hours? On Fri, Apr 23, 2010 at 7:49 AM, nierjesh kumar > <nierjeshkumar@gmail.com>wrote: > > I am using Informix 11.50 and I want to calculate the no of hours and > > second between two dates > > > > when I use following query > > > > select (extend(datetime(2020-01-01 18:16:00) year to second ,hour to > > minute)-extend(datetime(2010-01-01 15:21:00) year to second ,hour to > > minute)) as hours from dual > > it returns 2:55 > > > > but I want that It should return total no of hours between specified > dates > > i.e. 84962 > -- 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. --0016e6476656990f670484e9fc62