A History of Dates, Essbase and Why Unix time sucks

Summary
A multi-page rant about time, leap seconds, the amount of acid the Unix developers took in the 70’s and Essbase vs planning time.

Note: As per the previous blog post – to my American audience – in this post I will not be using the arbitrary retarded roller coaster  of US date formats, instead I’ll generally be using the significantly more ‘standardised’ dd\mm\yyyy format or yyyymmdd as needed. In interests of fairness, I’ll probably flag the ones that are potentially confusing, but rest assured, I’m not creating new months.

Where it all begins
Since the beginning of computing – the interaction of dates within a binary computer system has caused issues. Unix time in particular has an interesting history – in summary, the original timing mechanism was designed to calculate 1/60th of a second – with the epoch time being 01/01/1971. However they quickly realised that utilising a 32-bit integer only gives you around 3 years of ‘time’ to play in – so the timing was changed to every second and the epoch date changed to 01/01/1970 . Entertainingly this means we face another Y2k bug in 2038 when we hit the 32-bit overflow tracking in seconds.

Essbase dates are a variant on Unix Time – aka the number of seconds since 01/01/1970. That is to say, Essbase dates are rendered as a sum of all the seconds since 1970, with the exception of leap seconds, of which there have been 25 since 1970, with another due in June 2015.

Confused yet? To be honest, it’s getting a little bit arbitrary…still, it could be worse

In a sense this is similar to the date method inherit to Excel whereby dates aren’t really dates, but are sequential numbers counting the number of days since the 1st of January 1900.

As an aside I always wondered why they picked that date. As it turns out, they did that in excel v1.0 (released in 1987) to match up with Lotus 1-2-3 (the incumbent at the time). However, not only did they pick that date to match up with Lotus – they also deliberately included the same error: both Lotus and Excel include 29/02/1900 – despite the fact that it doesn’t exist!

To make this story slightly more entertaining – when VBA was released in 1993 it was decided to ‘fix’ the incorrectly added 29/02/1900 – however in order to keep the date function consistent between VBA and Excel, the date value in VBA actually starts on the 31/12/1899!

Back to the point: one of the other key differences between Excel and Essbase is the ability of Excel to actually use those dates in a sensible way. Excel has the concepts of Day(), Month(),Year(), EDate() – all easily available and all exactly as you’d expect functionally. Essbase…doesn’t.

Lets examine @DATEROLL for instance

Functionally very similar to EDate() – it’s designed to add a number of x (where x can be defined as period type) to a date. This example adds 10 months to the date.

So in Excel:

EDate(MyDate1,10)

In Essbase:

MyDate2=@DateRoll(MyDate1, DP_MONTH, 10);

That’s still not ‘too bad’ right? Lets try something a bit trickier. Find the first day of the next month after a defined date.

In excel:
Roll the month, take off the number of days in the entered month and add 1

MyDate2=EDATE(MyDate1,1)-DAY(MyDate1)+1

Alternatively – find the end of the current month and add 1!

MyDate2=EOMONTH(MyDate1,0)+1

Okay, now in Essbase:

Var DateNextMonth
DateNextMonth = @DateRoll(MyDate1,DP_DAY,1);
MyDate2=@DateRoll(DateNextMonth,DP_DAY,((@DATEPART(MyDate1,DP_DAY) * -1)+1));

Getting needlessly messy but it’s not too bad. So what’s the problem you ask?

Bringing Planning into the Mix

Some/most of you will be aware of the ‘date’ data type in Planning. Gives you the awesome ability to load dates to a planning dataform – exceptionally useful for depreciation models, employee costing / payroll models, birthday reminder applications (seriously – consider it – automated financial reporting emails with birthdays on them, it’d be awesome. I wouldn’t forget my mother’s birthday ever again!).

The issue is – for reasons that are unknown and are honestly baffling – the formatting of dates from Planning do not match a valid Essbase date format.

These are the available Essbase Date formats.

The one missing? Yep – the format that Planning uses when it pushes data to Essbase: “YYYYMMDD”

So what does all that mean?
Basically when you’ve got dates coming from a Planning application you need to deal with the fact that the incoming format isn’t the same as what Essbase expects. And as such, you’re going to need to massage it.

Let’s look back up at our example above, finding the first day of the next month after a defined date.

Option 1 – Converting Planning Dates to Essbase Numbers

VAR NewDate1inSecs = 0;
VAR NewDate1inSecsRolled = 0;
Var NewDate2inSecs

/* Convert Newdate1 to secs */
NewDate1inSecs = @DATEROLL(0, DP_MONTH, (((@INT("NewDate1"/10000) - 1970) * 12) + 
@INT(@MOD("NewDate1",10000)/100) -1)) + 
(86400 * (@MOD("NewDate1",100) -1));
/* Roll NewDate1 to the next month */
NewDate1inSecsRolled = @DateRoll(NewDate1inSecs,DP_DAY,1);
/* Net back the days to the first day */
NewDate2inSecs=@DateRoll(NewDate1inSecsRolled,DP_DAY,((@DATEPART(NewDate1inSecs,DP_DAY) * -1)+1));

/* Convert my date 2 back to a planning date format */
Var NewDate2Format
Var NewDate2Yr
Var NewDate2mth
Var NewDate2day

NewDate2Format = @FormatDate(NewDate2inSecs,"yymmdd");
NewDate2Yr = @INT(NewDate2Format/10000);
NewDate2mth = @INT((NewDate2Format - NewDate2Yr*10000)/100);
NewDate2day = @MOD(NewDate2Format,100);

NewDate2= 20000000 + (NewDate2Yr * 10000) + (NewDate2mth * 100) + NewDate2day;

Okay, that’s getting silly. Needing to convert in and out of dates every time is simply going to complexify your code. At this point, you might as well just start splitting the Planning date into strings and not bothering with the inbuilt essbase date functions.

Option 2 – Using Math against Planning Date strings

VAR yr = 0;
VAR mth = 0;

/* Calc within a block */
NewDate2(
/* Find the Year */
yr = @INT(NewDate1/10000);
/* Find the month */
mth = @INT((NewDate1 - Yr*10000)/100);
/* If December */
IF(mth == 12)
	NewDate2 = ((yr+1)*10000) + (1*100) + 1;
/* Else roll the month */
ELSE
	NewDate2 = (yr*10000) + ((mth+1)*100) + 1;
ENDIF

Definitely simpler – but manipulating strings is a bit inelegant!

Let’s get exotic

Okay, so what if you really want to/have to use the Essbase date functionality. Let’s have a look at the other options.

In 11.1.2.1 you’re very limited as the only HSP function you’ve got available is HSPDateToString – however what that does is very useful. Basically that will take a Planning Date string (YYYYMMDD) and format it as a DD/MM/YYYY. That in itself is fairly useless, but in combination with TODATEEX you can convert a planning date directly into an Essbase “number of seconds” value. Which can then in turn be used in the Essbase functions!

NewDate1inSecs=@TODATEEX(“dd/mm/yyyy”,@HspDateToString(“NewDate1”));

This still has the underlying problem that you end up with an Essbase Date as the answer rather than a Planning one, but is useful for functions like @DATEDIFF

From 11.1.2.2
From 11.1.2.2 onwards, you can use start to use some of the ill-documented Custom Defined Functions:

[CDATA[ @HspDateRoll(startDate, noOfPeriods, periodType)]];
[CDATA[ Returns the next date, resulting from adding noOfPeriods to StartDate.]]

Okay – similar format to DateRoll – but you don’t have to modify the date on the way in. Instead it uses the Planning dates and RETURNS a Planning date. So returning to our original problem – finding the first day of the next month after a defined date.

Var DateNextMonth
DateNextMonth = @HSPDateRoll(MyDate1,DP_DAY,1);
MyDate2=@HSPDateRoll(DateNextMonth,DP_DAY,((@DATEPART(MyDate1,DP_DAY) * -1)+1));

Much easier!

We’ve also got the Planning equivalent of @DateDiff in @HSPDateDiff. This gives us a the difference between two planning dates – using the same functional format as DateDiff but using Planning Dates
NumofWeeksDiff = @HSPDateDiff(MyDate1, MyDate2, DP_WEEK);

Conclusion
As you can see – the default behavior of Essbase and Planning dates interaction is fiddly at best. However Oracle have definitely added some new functionality in recent versions that gets over most of the underlying issues.

In the next post we’ll look at some of the 11.1.2.4 & Cloud Functions that provide ‘real’ date functionality, and basically allow for pretty much any work with dates.

Finally – in researching this post I’m heavily indebted to the following authors whom have written excellent articles about dates (and a variety of other things!) Check them out.
Jason Biard
Alp Burak Beder

Cheers,
Pete