Page 1 of 1
Calculate minutes
Posted: Fri Jul 10, 2020 9:54 am
by orlando
Hi!
I'm having a major brain bug right now.
I'd like to use rules to calculate minutes.
If I start with a timestamp like 202007101030 (value in cube) as base and create a new timestamp e.g. 202007101059 and subtract the difference, then the 29 minutes are calculated correctly.
If the clock jumps to 11, then the timestamp
is 202007101100, the difference is 70.
Mathematically this is correct, but unfortunately an hour does not have 100 minutes but 60.
I'm pretty sure I've done this before - but friday.
Can someone please push me in the right direction.
Thanks!
orlando
Re: Calculate minutes
Posted: Fri Jul 10, 2020 11:00 am
by scrumthing
Why don't you try to use the date time functions in rules?
https://www.ibm.com/support/knowledgece ... timst.html
If you can convert your cube value to a serial date you can use
Code: Select all
TIMVL(datetime, type, ExtendedYears)
to calculated minutes, hours, seconds...
Hope that helps...
Re: Calculate minutes
Posted: Fri Jul 10, 2020 12:38 pm
by Mark RMBC
Hi Orlando,
Well, assuming you want to calculate the difference in minutes between 2 dates, then TIMVL isn't going to help you as far as I can see.
Though maybe you could have put something like that in the topic title
Here is how I would approach it:
I would break the value down between the date and time part,
so 20200710 would be the date part.
I would then take the difference between the 2 date parts and multiply by 1440 (convert to minutes)
With the time I would do the following,
take the Hour portion and multiply by 1/24 and take the minute portion and multiply by 1/24/60
Then add the date part to the time part
so in your example the date part would be 0 * 1440 = 0
Your hour part would be:
latest time = 11 * (1/24) = 0.458333333
First Time = 10 * (1/24) = 0.416666667
Minute Part
latest time = 0 * (1/24/60) = 0
First Time = 30 * (1/24/60) = 0.020833333
Latest time Minutes = (0.458333333 + 0) * 1440 = 660
First time Minutes = (0.416666667 + 0.020833333) * 1440 = 630
Latest Time - First Time Minutes = 30
Date difference + Time Difference = 0 + 30
regards,
Mark
Re: Calculate minutes
Posted: Fri Jul 10, 2020 12:51 pm
by orlando
Hi Mark,
thanks a lot. Thats looks good.
Mark RMBC wrote: ↑Fri Jul 10, 2020 12:38 pm
Hi Orlando,
Well, assuming you want to calculate the difference in minutes between 2 dates, then TIMVL isn't going to help you as far as I can see.
Though maybe you could have put something like that in the topic title
As I said, huge brain bug today
Here is how I would approach it:
I would break the value down between the date and time part,
so 20200710 would be the date part.
I would then take the difference between the 2 date parts and multiply by 1440 (convert to minutes)
With the time I would do the following,
take the Hour portion and multiply by 1/24 and take the minute portion and multiply by 1/24/60
Then add the date part to the time part
so in your example the date part would be 0 * 1440 = 0
Your hour part would be:
latest time = 11 * (1/24) = 0.458333333
First Time = 10 * (1/24) = 0.416666667
Minute Part
latest time = 0 * (1/24/60) = 0
First Time = 30 * (1/24/60) = 0.020833333
Latest time Minutes = (0.458333333 + 0) * 1440 = 660
First time Minutes = (0.416666667 + 0.020833333) * 1440 = 630
Latest Time - First Time Minutes = 30
Date difference + Time Difference = 0 + 30
i will try that on monday.
For this week it's enough - I'm going to the beer garden
best regards
orlando
Re: Calculate minutes
Posted: Fri Jul 10, 2020 2:10 pm
by ascheevel
Are you doing this via rule or TI process?
If TI process, you can use the ParseDate function to convert your datetime strings to serial numbers and then take the difference times 1440. I didn't specify a NewDateFormatter, so the below example uses the default but you can specify one as well.
Code: Select all
# initial start/stop cube values
nStart = 202007101030;
nStop = 202007101100;
# convert to serial (returns TM1 serial not extended years, assumes 24hour time)
nStartSerial = ParseDate(NumberToString(nStart), 'YYYYMMddHHmm');
nStopSerial = ParseDate(NumberToString(nStop), 'YYYYMMddHHmm');
# multiply difference in serial numbers * 1440 minutes per day
nMinutes = (nStartSerial - nStopSerial) * 1440;
# 30 = (22106.4375 - 22106.45833) * 1440
Good article from Wim on using ParseDate:
https://www.wimgielis.com/tm1_newdateformatter_EN.htm
Syntax reference for formatting date times:
http://userguide.icu-project.org/formatparse/datetime
Re: Calculate minutes
Posted: Sat Jul 11, 2020 5:33 pm
by gtonkin
This may work for you:
Code: Select all
[Minutes]=N:
(SUBST(DB(<EndTime>),9,2)-SUBST(DB(<StartTime>),9,2))*60 +
SUBST(DB(<EndTime>),11,2)-SUBST(DB(<StartTime>),11,2)
Assuming you are not crossing days, months or years. If you plan to, you could add those and multiply days by 24*60 etc. etc.
Hope it works for you.
Re: Calculate minutes
Posted: Mon Jul 13, 2020 10:02 am
by orlando
Hello, everyone,
thanks for the many answers, that was a neat brain bug.
That's what i do now:
From the start date I get the serial number (e.g. 22109,484490741) and write it into the database
Via rule I then get the current serial number (eg. 22109,492106481)
Then I calculate
['minutes'] = ['actSerial']*1440-['startSerial]*1440
thanks for pushing me in the right direction!
best regards,
orlando
Re: Calculate minutes
Posted: Mon Jul 13, 2020 11:28 am
by MarenC
Interesting, how do you get the serial date to the decimal place in the rule?
Do you have a lookup table for this?
Maren
Re: Calculate minutes
Posted: Mon Jul 13, 2020 12:11 pm
by orlando
MarenC wrote: ↑Mon Jul 13, 2020 11:28 am
Interesting, how do you get the serial date to the decimal place in the rule?
Do you have a lookup table for this?
Maren
Hi Maren,
no lookup table - it's all done by tm1
['SerialAct'] = now();
best regards,
orlando
Re: Calculate minutes
Posted: Mon Jul 13, 2020 12:14 pm
by MarenC
Hi Orlando,
but how do you get the other serial number?
Maren
Re: Calculate minutes
Posted: Mon Jul 13, 2020 12:35 pm
by orlando
MarenC wrote: ↑Mon Jul 13, 2020 12:14 pm
Hi Orlando,
but how do you get the other serial number?
Maren
Hi Maren,
ah - now've got you.
SerialStart is stored in a cube via TI - so yes it's a lookup cube
nSerial = now();
CellPutN(nSerial, sCube, sElem, 'SerialStart');
SerialAct is a rule
['SerialAct'] = now();
best regards,
orlando