AFC - Abacus Formula Compiler for Java

Dates, Times, Durations and Time Zones

Apart from setting the current runtime time-zone, there are other caveats with dates and times in Java and Excel. Pure times need special treatment, in particular.

Time-Of-Day and Durations

It is problematic to use java.util.Date as a container for time-only values, like a time of day or a time duration. Java always does time-zone adjustment, so one has to be very careful to only use java.util.Date with the UTC time zone for time-only values. We therefore don’t think it is a good idea to use java.util.Date here.

AFC therefore accepts and returns time-only values as a number of milliseconds in a long, not in a java.util.Date. To make it clear that you are passing a time value, you have to annotate the method in question with Milliseconds. For time of day values, you simply pass it the number of milliseconds since 00:00 am.

As an example, we check the local time of day someone was born:

B C
14 TimeOfBirth 12:00
(TimeOfBirth)
15 Demarcation 12:00
16 IsBeforeTime false
=C14<C15
(IsBeforeTime)

Here’s the input interface:

@Milliseconds
public long time()
{
  return this.millis;
}

And the input value setup code:

long millis = (_hour * 60L + _minute) * 60L * 1000L;

(If you still want to use java.util.Date for time-only values and are careful to always do this in the UTC time zone, then calling Date.getTime() will return the proper value to pass to AFC, and new Date(long) will convert a value from AFC to a java.util.Date.)

Local and Global Time

As described in the background information below, we need to differentiate local and global times in Excel date/time constants. The examples below assume a hypothetical need to classify people by elements of their date and time of birth.

Date only

We check if people were born before 1981/01/01. Here’s a very simple sheet for this:

B C
8 DateOfBirth 1/1/81
(DateOfBirth)
9 Demarcation 1/1/81
10 IsBeforeDate false
=C8<C9
(IsBeforeDate)

Local time is perfectly suitable for this.

In fact, time-zone adjustment of the constant “1981/1/1” would be a problem here. Say we set up the input for the date of birth as follows:

Calendar calendar = Calendar.getInstance( _timeZone );
calendar.clear();
calendar.set( _y, _m, _d );

A time-zone adjustment of the constant would occasionally lead to wrong results. This is because the comparison is with 1981/01/01 00:00 am internally. And 00:00 am in the sheet designer’s time-zone would not always be 00:00 am in the user’s time-zone. So people born exactly on 1981/01/01 from time-zones where 00:00 am is before 00:00 am in the designer’s time-zone would be considered born before 1981/01/01.

Point in time

Now we check if people were born before 1981/01/01 12:00 am CET. The constant is now a global point in time. So we do need time-zone adjustment. Here’s the sheet:

B C
20 DateTimeOfBirth 1/1/81 12:00
(DateTimeOfBirth)
21 Demarcation (CET) 01 01 1981 12:00:00
22 IsBeforeDateTime false
=C20<C21
(IsBeforeDateTime)

As shown, this will not work properly because AFC still treats the constant as local time. So we need a way to make AFC treat the constant as global time. When I say “we” here, I really mean us as sheet designers, not as application programmers. So the setup is something we do in the sheet, not the application code:

B C
3 GlobalTimeZoneName CET
(GlobalTimeZoneName)
4 GlobalTimeFormat 01 01 2000 11:12:00
(GlobalTimeFormat)

Here’s the recipe:

  • Choose a new cell in the sheet.
  • Name it GlobalTimeFormat.
  • Enter a sample date/time into it.
  • Apply a distinctive date format to it. A good format includes date, time, and a designation of your (the sheet designer’s) time zone. I use TT.MM.JJJJ hh:mm "CET" (German format string).
  • Apply the same distinctive format to all cells you want AFC to treat as global time, and only those.
  • Choose another new cell in the sheet.
  • Name it GlobalTimeZoneName.
  • Enter into it the identifier of the time zone your global times are specified in, for example “CET”, “EET”, “PST”, “EST”. See TimeZone getTimeZone(String) in the java.util.TimeZone class for details.

Now the constant is considered global time and converted to UTC when the sheet is compiled. Then, when the compiled engine run, it is again adjusted for the current runtime time-zone.

If you don’t set up a cell named GlobalTimeZoneName, AFC simply uses the default time zone active when compiling the sheet. This may not be what you wanted.

Durations Between Points In Time

Because Excel and AFC treat date/time values as local time, computing a difference between two such values sometimes differs from what you would get in Java. The reason is daylight saving. In Java, when you do

DLSInput input = new DLSInput();
Date w = input.inWinter();
Date s = input.inSummer();
long diffHours = (s.getTime() - w.getTime()) / 1000 / 3600;
assertEquals( JAVA_DIFF_HOURS, diffHours );

where

private static final long JAVA_DIFF_HOURS = 4343L;
private static final long EXCEL_DIFF_HOURS = 4344L;
public class DLSInput {

  public Date inWinter() {
    Calendar w = new GregorianCalendar( CET );
    w.clear();
    w.set( 1981, 0, 1, 12, 0 );
    return w.getTime();
  }

  public Date inSummer() {
    Calendar s = new GregorianCalendar( CET );
    s.clear();
    s.set( 1981, 6, 1, 12, 0 );
    return s.getTime();
  }

}

you get a difference that takes the daylight saving in summer into account. The same thing in Excel does not:

B C
26 InWinter 1/1/81 12:00
(InWinter)
27 InSummer 7/1/81 12:00
(InSummer)
28 Const in winter (CET) 01 01 1981 12:00:00
29 Const in summer (CET) 01 07 1981 12:00:00
30 Const in winter (local) 1/1/81 12:00
31 Const in summer (local) 7/1/81 12:00
32 InputDiff 4344
=(C27-C26)*24.0
(InputDiff)
33 ConstCETDiff 4344
=(C29-C28)*24.0
(ConstCETDiff)
34 ConstLocalDiff 4344
=(C31-C30)*24.0
(ConstLocalDiff)

and neither does AFC:

DLSInput input = new DLSInput();
DLSOutput out = (DLSOutput) factory.newComputation( input );
assertEquals( "input", EXCEL_DIFF_HOURS, out.inputDiff() );
assertEquals( "CET", EXCEL_DIFF_HOURS, out.constCETDiff() );
assertEquals( "local", EXCEL_DIFF_HOURS, out.constLocalDiff() );

Background

Excel and Java have different ideas about date/time values. Consider the value “1983/02/21 3:45 am” (in yyyy/mm/dd notation).

Excel

If you enter this date into an Excel sheet in Zurich (GMT+1), save the sheet, and then reopen it in Kharkov (GMT+2), it still reads “1983/02/21 3:45 am” (assuming the date format is set to yyyy/mm/dd in both places). So a date/time in Excel represents a value in relation to your local time zone, wherever you are. We will call this a local date/time. Significantly, its string representation does not indicate a time zone.

This is suitable for

  • plain dates (no time component), or
  • plain times of day or durations (no date component).

Java

However, if you convert the string “1983/02/21 3:45 am” to a java.util.Date in Zurich (GMT+1), serialize it, then deserialize it in Kharkov (GMT+2) and convert it back to a string, you will get “1983/02/21 4:45 am”. So a java.util.Date represents an absolute point in time, internally expressed as a UTC value. We will call this a global time (we don’t call it global date/time because it makes no sense without a date). Its complete string representation contains a time zone, so “1983/02/21 2:45 am UTC” or “1983/02/21 4:45 am EET”.

This is suitable for

  • points in time (both date and time component).

Conversion

Trouble strikes when we convert values. Consider an Excel cell with the value “1981/01/01 00:00 am” in it. You compare this cell to a date obtained from an input method. Did you mean to compare local time, so essentially compare the calendar day only? Or did you mean to compare global time, so “1981/01/01 00:00 am CET” (assuming you compile the sheet in CET)?

Currently, AFC makes no attempt to be especially clever. It simply treats Excel date/time fields like Excel does. (We have thought hard about cleverer rules, but found none that was totally satisfying.) But you can override this behaviour.

A conversion thus becomes a source of trouble if

  • the sheet contains hard-coded date/time values which are meant to be absolute time references but treated by Excel and AFC as relative,
  • it is designed in one time zone (so the absolute references are expressed in that time zone), and
  • the resulting engine is run in another time zone.

So we need a change in time zone. Users accessing a server-side service from all over the world cause this. Moving compiled engines to different time zones does, too.

Do we have plausible AFC usage scenarios where there are hard-coded date/time cells meant to be absolute?

  • One class of scenario uses demarcation dates, which make a computation go one way if a specified date is before the demarcation date, and another way if it is after (see the examples above).
  • Another class involves lookup tables, where the compiled engine returns date/time values depending on some input data. This class would probably be solved more typically in databases, but people might still want to use AFC for it.