How can I calculate difference between two dates?

Paul Wyatt

I want to calculate the difference in days, hours, minutes and seconds between two datetime stamps and I would be very interested to know how others have approached this.  Currently I seem to be contemplating a solution which first subtracts one date from another to get a decimal.  Then I would convert that decimal to days, taken from the original decimal and convert to hours, etc.  This is very convoluted and so I was hoping that someone could help me out.

Samuele Marchetto

Hi, if you need to calculate the difference in a report, you can use Nexel:

 

 

where the formula "=datevalue([@b;*;*])-datevalue([@a;*;*])" calculate difference between block b and a.

 

Another way could be to calculate it using ETL in a Datareader applying function DAY(), HOUR(), MINUTE(), SECOND() on the difference between timestamp fields.

Paul Wyatt

Thanks Samuele Marchetto,

 

I like the nexel approach - I keep meaning to find reasons to use it but end up testing in it only to replace the solution with a dataflow or datareader process later. 

 

I was playing with the datareader date functions that you mentioned and that is another way and a method that will come in handy later and for another process I will be incorporating.

 

As a result of playing with your suggestions and so thinking about my needs more deeply, I was able to devise a method that uses a dataview to calculate the different date parts with algorithms.  As there is nothing, that I have found, on this matter and as the method is dynamic, I have submitted an Inspiration which outlines and discusses the method I chose to ensure it is more easily found by all.

 

However, for completeness and in summary, I created a dataview with several algorithms like this:

 

Block	Content	Type	Formula	Format
a.	Date1	Cube		Numeric
b.	Date2	Cube		Numeric
c	Result	Algorithm	b-a	Numeric
d.	Days	Algorithm	int(c)	Numeric
e.	Hours	Algorithm	mod(c,1)*24	Numeric
f.	Minutes	Algorithm	mod(e,1)*60	Numeric
g.	Seconds	Algorithm	mod(f,1)*60	Numeric

 

The cubes are populated with the numeric value of each of the dates so that calculations can be carried out.

 

Again, thanks for your response and so quickly too.

Unknown

Hi Marcsamu,

This works perfectly.

I just want to mention that it's important that cubes b and a in your example are text cubes.
I got some problems with the DATAVALUE() function before due to the wrong data type of my cubes.

Anastasia Pesyakova

@Matthew Sobon hi Matt, please see the example of the dates difference calculation.