Re: HOURS function doesnt appear to work correctly-CLOSED
I did just do the math myself and it worked fine. ((Date_Time1 - Date_Time2) - (Days * 86400)) /60 /60 My concern was that HOURS() worked differently than MINUTES() or SECONDS() Which worked as I expected. The results and how they worked is inconsistent, which was why I posed the question. Thanks, Matt P. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Thursday, March 31, 2011 2:16 PM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Except for the number of days ;) i.e. (Jan 30, 2011 9:30 - Jan 1, 2011 7:30) / 3600 Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Thursday, March 31, 2011 12:12 PM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Well... Why not just do an integer calculation instead of trying to fool the HOURS-function? ($Date_Time2$ - $Date_Time1$) / 3600 It will allays give the difference correct. Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10): * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. I believe you can do it by adjusting for your time zone HOURS(01/01/1970 + (Date_Time2 - Date_Time1)) If you are in GMT it will add 0 to your results otherwise it will add the correct offset making the hours correct. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Thursday, March 31, 2011 10:16 AM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Hi, That is because the hours depend on your timezone. A diff of say 2 hours, would give you a date of 7200 seconds, which translates to January 1, 1970 02:00:00 GMT. The hours are 2 only if your client (or servers if it is a filter) is set for GMT. Best Regards - Misi, RRR AB, http://www.rrr.se -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Thursday, March 31, 2011 9:48 AM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Hi, Yes it does, but you are not giving it a timestamp as argument. Instead you are giving it the difference in seconds between two timestamps... -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Matthew Perrault Sent: Thursday, March 31, 2011 9:04 AM To: arslist@ARSLIST.ORG Subject: HOURS function doesnt appear to work correctly All, Found an oddity the other day. I was taking 2 Date/Time values and trying to break out days, hours, minutes, and seconds. I used the datediff function to pull out the number of days different, Then used the HOURS, MINUTES, and SECONDS functions. All except HOURS worked correctly. HOURS(Date_Time2 - Date_Time1) = 0 (should have been 6) MINUTES(Date_Time2 - Date_Time1) = 47 --Correct SECONDS(Date_Time2 - Date_Time1) = 23 --Correct. I was able to get around it by just doing the math myself, But I thought HOURS was supposed to return the number of hours in a Time Stamp. It's strange because it doesn't appear to be consistent in how the other functions work. Was wondering if this was a defect or if I was just not using it correctly. ARS 7.1 Patch 8 MS SQL Server 2005 Windows Server 2003 Thanks, Matt P.
Re: HOURS function doesnt appear to work correctly-CLOSED
Hi, No, it is not inconsistent. Seconds are seconds regardless of date, your timezone, daylighsavingstime, etc. Hours depend on all these things. The date is stored as the number of seconds since 1970-01-01 00:00:00 GMT. By doing the math (Date_Time1 - Date_Time2), you revert to 1970-01-01, and the hour of the day is very different depending on timezone etc. Why do you have the Days in your math? It should not be needed. The only thing need to count the hours between two timestamps are: IntegerField = (Date_Time1 - Date_Time2) / 3600 Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10): * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. I did just do the math myself and it worked fine. ((Date_Time1 - Date_Time2) - (Days * 86400)) /60 /60 My concern was that HOURS() worked differently than MINUTES() or SECONDS() Which worked as I expected. The results and how they worked is inconsistent, which was why I posed the question. Thanks, Matt P. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Thursday, March 31, 2011 2:16 PM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Except for the number of days ;) i.e. (Jan 30, 2011 9:30 - Jan 1, 2011 7:30) / 3600 Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Thursday, March 31, 2011 12:12 PM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Well... Why not just do an integer calculation instead of trying to fool the HOURS-function? ($Date_Time2$ - $Date_Time1$) / 3600 It will allays give the difference correct. Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10): * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. I believe you can do it by adjusting for your time zone HOURS(01/01/1970 + (Date_Time2 - Date_Time1)) If you are in GMT it will add 0 to your results otherwise it will add the correct offset making the hours correct. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Thursday, March 31, 2011 10:16 AM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Hi, That is because the hours depend on your timezone. A diff of say 2 hours, would give you a date of 7200 seconds, which translates to January 1, 1970 02:00:00 GMT. The hours are 2 only if your client (or servers if it is a filter) is set for GMT. Best Regards - Misi, RRR AB, http://www.rrr.se -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Thursday, March 31, 2011 9:48 AM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Hi, Yes it does, but you are not giving it a timestamp as argument. Instead you are giving it the difference in seconds between two timestamps... -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Matthew Perrault Sent: Thursday, March 31, 2011 9:04 AM To: arslist@ARSLIST.ORG Subject: HOURS function doesnt appear to work correctly All, Found an oddity the other day. I was taking 2 Date/Time values and trying to break out days, hours, minutes, and seconds. I used the datediff function to pull out the number of days different, Then used the HOURS, MINUTES, and SECONDS functions. All except HOURS worked correctly. HOURS(Date_Time2 - Date_Time1) = 0 (should have been 6) MINUTES(Date_Time2 - Date_Time1) = 47 --Correct SECONDS(Date_Time2 - Date_Time1) = 23 --Correct. I was able to get around it by just doing the math myself, But I thought HOURS was supposed to return the number of hours in a Time Stamp. It's strange because it doesn't appear to be consistent in how the other functions work. Was wondering if this was a defect or if I was just not using it correctly. ARS 7.1 Patch 8 MS SQL Server 2005 Windows Server 2003 Thanks, Matt P. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: Where the Answers Are
Re: HOURS function doesnt appear to work correctly-CLOSED
All times are in GMT. So time zone shouldn't play a part. I would say it was accurate/worked correctly if 1/1/2011 8 am and 1/5/2011 5 pm returned just the HOURS different between those 2 values (105 Hours). Instead of 0. Or if they gave the option in DATEDIFF for hours, which they don't. I would also be more inclined to agree, if MINUTES() worked the same way As Timezone can affect the Minutes between date calculations as well, but that worked correctly. The reason I used the days, is because the starting date may be several days before the closing date. And I'm already pulling out the # of days into its own field. So 4 days, 105 hours is not correct. It should read 4 days 9 hours. Therefore I have to remove the number of days. Either way I've got it working. I just figured I would post out on ARSList, to let some others know of this, and be aware of it. Thanks for trying to help. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Friday, April 01, 2011 11:20 AM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly-CLOSED Hi, No, it is not inconsistent. Seconds are seconds regardless of date, your timezone, daylighsavingstime, etc. Hours depend on all these things. The date is stored as the number of seconds since 1970-01-01 00:00:00 GMT. By doing the math (Date_Time1 - Date_Time2), you revert to 1970-01-01, and the hour of the day is very different depending on timezone etc. Why do you have the Days in your math? It should not be needed. The only thing need to count the hours between two timestamps are: IntegerField = (Date_Time1 - Date_Time2) / 3600 Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10): * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. I did just do the math myself and it worked fine. ((Date_Time1 - Date_Time2) - (Days * 86400)) /60 /60 My concern was that HOURS() worked differently than MINUTES() or SECONDS() Which worked as I expected. The results and how they worked is inconsistent, which was why I posed the question. Thanks, Matt P. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Thursday, March 31, 2011 2:16 PM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Except for the number of days ;) i.e. (Jan 30, 2011 9:30 - Jan 1, 2011 7:30) / 3600 Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Thursday, March 31, 2011 12:12 PM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Well... Why not just do an integer calculation instead of trying to fool the HOURS-function? ($Date_Time2$ - $Date_Time1$) / 3600 It will allays give the difference correct. Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10): * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. I believe you can do it by adjusting for your time zone HOURS(01/01/1970 + (Date_Time2 - Date_Time1)) If you are in GMT it will add 0 to your results otherwise it will add the correct offset making the hours correct. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Thursday, March 31, 2011 10:16 AM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Hi, That is because the hours depend on your timezone. A diff of say 2 hours, would give you a date of 7200 seconds, which translates to January 1, 1970 02:00:00 GMT. The hours are 2 only if your client (or servers if it is a filter) is set for GMT. Best Regards - Misi, RRR AB, http://www.rrr.se -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Thursday, March 31, 2011 9:48 AM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Hi, Yes it does, but you are not giving it a timestamp as argument. Instead you are giving it the difference in seconds between two timestamps... -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Matthew Perrault Sent: Thursday, March 31, 2011 9:04 AM To: arslist@ARSLIST.ORG Subject
Re: HOURS function doesnt appear to work correctly-CLOSED
Hi, If it is more than 23, I guess HOURS() would be useless. HOURS() gives you the hour of the day. How could you expect it to give an accurate result if you give it a delta as opposed to an absoulute number of seconds (timestamp)? Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10): * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. All times are in GMT. So time zone shouldn't play a part. I would say it was accurate/worked correctly if 1/1/2011 8 am and 1/5/2011 5 pm returned just the HOURS different between those 2 values (105 Hours). Instead of 0. Or if they gave the option in DATEDIFF for hours, which they don't. I would also be more inclined to agree, if MINUTES() worked the same way As Timezone can affect the Minutes between date calculations as well, but that worked correctly. The reason I used the days, is because the starting date may be several days before the closing date. And I'm already pulling out the # of days into its own field. So 4 days, 105 hours is not correct. It should read 4 days 9 hours. Therefore I have to remove the number of days. Either way I've got it working. I just figured I would post out on ARSList, to let some others know of this, and be aware of it. Thanks for trying to help. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Friday, April 01, 2011 11:20 AM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly-CLOSED Hi, No, it is not inconsistent. Seconds are seconds regardless of date, your timezone, daylighsavingstime, etc. Hours depend on all these things. The date is stored as the number of seconds since 1970-01-01 00:00:00 GMT. By doing the math (Date_Time1 - Date_Time2), you revert to 1970-01-01, and the hour of the day is very different depending on timezone etc. Why do you have the Days in your math? It should not be needed. The only thing need to count the hours between two timestamps are: IntegerField = (Date_Time1 - Date_Time2) / 3600 Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10): * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. I did just do the math myself and it worked fine. ((Date_Time1 - Date_Time2) - (Days * 86400)) /60 /60 My concern was that HOURS() worked differently than MINUTES() or SECONDS() Which worked as I expected. The results and how they worked is inconsistent, which was why I posed the question. Thanks, Matt P. -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Thursday, March 31, 2011 2:16 PM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Except for the number of days ;) i.e. (Jan 30, 2011 9:30 - Jan 1, 2011 7:30) / 3600 Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Thursday, March 31, 2011 12:12 PM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Well... Why not just do an integer calculation instead of trying to fool the HOURS-function? ($Date_Time2$ - $Date_Time1$) / 3600 It will allays give the difference correct. Best Regards - Misi, RRR AB, http://www.rrr.se Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10): * RRR|License - Not enough Remedy licenses? Save money by optimizing. * RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs. Find these products, and many free tools and utilities, at http://rrr.se. I believe you can do it by adjusting for your time zone HOURS(01/01/1970 + (Date_Time2 - Date_Time1)) If you are in GMT it will add 0 to your results otherwise it will add the correct offset making the hours correct. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Misi Mladoniczky Sent: Thursday, March 31, 2011 10:16 AM To: arslist@ARSLIST.ORG Subject: Re: HOURS function doesnt appear to work correctly Hi, That is because the hours depend on your timezone. A diff of say 2 hours, would give you a date of 7200 seconds, which translates to January 1, 1970 02:00:00 GMT. The hours are 2 only if your client (or servers if it is a filter) is set for GMT. Best Regards - Misi, RRR AB, http://www.rrr.se -Original Message
