Calculate minutes

[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

[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...

[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

[orlando]

Hi Mark,

thanks a lot. Thats looks good.

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

[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

[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.

[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

[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

[orlando]

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

[MarenC]

Hi Orlando,

but how do you get the other serial number?

Maren

[orlando]

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