I got intrigued by this question. There is a TIMESTAMPDIFF in Db2 - it says it returns an estimate of the number of whatever unit you specify. So if you do this in RSS -
values(timestampdiff(16, char(timestamp('2017-03-02') - timestamp('2017-02-02'))));
the answer is 30, because timestamp subtraction gives a timestamp duration with 1 month (because the day of each date is the same).
If you do this -
values(timestampdiff(16, char(timestamp('2017-03-01') - timestamp('2017-02-02'))));
the answer is 27, because it gives you the actual days in the timestamp duration. Hence, the estimate - if the duration reports months, the assumption is 30 days in a month, if years, 365 days in a year.
We can use the day, month, etc., functions on a duration to extract that part of it. So this statement -
values(month(timestamp('2017-03-02') - timestamp('2017-02-02')));
returns a 1, while this -
values(day(timestamp('2017-03-02') - timestamp('2017-02-02')));
returns a 0.
Basically you can get the same estimate as TIMESTAMPDIFF does for 2 dates by something like this -
year(date-duration) * 365 + month(date-duration) * 30 + day(date-duration)
Using days function works because gives 1 more than the number of days since 0001-01-01.
DATEDIFF in SQL Server is a somewhat similar function to TIMESTAMPDIFF (yes, you purists, I said similar!) And it gives the consistent number of days - so between Feb 1 and Mar 1 it gives 28 or 29. I wish we had the like on the i. Now a question is, what does the SQL standard define?
Cheers
Vern
On Thu, 14 Sep, 2023 at 12:37 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:
To: 'midrange systems technical discussion'
Bryan,
There is no direct way to convert a numeric value into a date time representation. If you only want a character representation, you could try something like this:
Translate(VarChar_Format(Dec(Timestamp1 - Timestamp2, 10, 0), '00,00,00,00'), ':', ',')
Note: VarChar_Format is normally used for to convert timestamps into character representation or to convert numeric values into a character representation.
Since the number of digits between the group separator (,) is not checked you could use it to build a character representation of a date/time value. But the group separator is always a comma, so if you need a colon, you have to convert it (with either the TRANSLATE or REPLACE function).
If you only deal with times, you could also try the following (the result is a real time!)
Time('00.00.00<
http://00.00.00>') + (Midnight_Seconds(Time1) - Midnight_Seconds(Time2)) Seconds
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization – Education – Consulting on IBM i
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Bryan Dietz
Sent: Wednesday, 13 September 2023 22:35
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Subject: Re: SQL Data Subtraction question
Birgitta, as a one off to your comment.
I've used the subtraction date but have not been able to find a decent way to edit the resulting value that I have for calculating the wall clock time for job:
digits(dec(t3.MESSAGE_TIMESTAMP - t2.MESSAGE_TIMESTAMP)) as Duration_ddhhmmss
into something like dd:hh:mm:ss
Bryan
Birgitta Hauser wrote on 9/13/2023 12:15 AM:
If you subtract 2 dates with SQL the result will be an 8 digit numeric
value representing the difference in Years, months, days. i.e. in your
example 103 means 1 Month and 3 Days.
Birgitta Hauser
Modernization – Education – Consulting on IBM i
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx> To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
midrange.com
