CODEWITHSUNDEEP
xmlfirebird
scottwed
scottwed

Reputation: 198

Compute human readable timestamp from floating point (EasyIP)?

I need to convert some date+timestamps in XML data extracted from Easy-IP. I don't recognize the raw values, they appear to be a floating point value, but the whole number portion is too small for UNIX style.

I will probably encounter extracted data from this type of system again, so I'd like to have a standard way of converting it that doesn't rely upon having direct access to the database (which could be different or inaccessible)

I believe the back-end database is Firebird, but according to their documentation, they start counting up from Jan 1st, 1753.

Some sample values (each line is a different entity), which should all refer to activities that occurred in the last 15 years, and probably much more recent: 

CREATED="39660.2632087847" UPDATED="39660.2632160185"
CREATED="39660.2631284838" UPDATED="39750.4032429514"
FIRST_SUCCESSFUL_CONTACT="39668.128960544"  LAST_SUCCESSFUL_CONTACT="41301.0505147685"
FIRST_SUCCESSFUL_CONTACT="39668.1289603588" LAST_SUCCESSFUL_CONTACT="41301.0505142245"

My hope is that someone else has already seen this before, and not to pose a puzzle. Bonus points if you have a reliable way to convert to a date+timestamp using XSL, Java, or (less preferred) PERL.

Upvotes: 1

Views: 173

Answers (2)

scottwed
scottwed

Reputation: 198

I couldn't find an accurate example of this conversion for Java. Here's a simplified version of what I'll be using:

public static String convertEasyIPTime (double dateTimeDouble, String timeZoneCode) {

  TimeZone tz = TimeZone.getTimeZone(timeZoneCode);

  long days = (long) dateTimeDouble;
  long adjustedDays = days - 25569; // Days between Jan 1st 1753 (Delphi) and Dec 31st 1970 (Java)

  long datePortion = adjustedDays * 24 * 60 * 60 * 1000;
  long timePortion =  (long) ((dateTimeDouble - days) * 60 * 60 * 24 * 1000);

  int zoneAndDSTOffset = tz.getOffset(datePortion);

  Date output = new Date (datePortion + timePortion - zoneAndDSTOffset);
  DateFormat formatter = new SimpleDateFormat("dd MMM yyyy HH:mm:ss z");  
  formatter.setTimeZone(TimeZone.getTimeZone(timeZoneCode)); 
  return formatter.format(output);
}

A sample of calling it:

System.out.println(convertEasyIPTime (39940.1295844213d, "CST"));

Upvotes: 0

jachguate
jachguate

Reputation: 17203

My guess is the values come from the internal Delphi TDateTime representation:

Delphi programs internally represents TDateTime values as a floating point value, the integral part represents the number of days since Dec-30-1899 and the fractional part is the fraction of the day since midnight.

So, for example,

  • 39660 is Jul-31-2008
  • 0.2632087847 is 6:19:01.239 AM

Thus, your first example is 2008-07-31 06:19:01

I don't think the data is a Internal Firebird representation of dates, so my guess is also the original system don't used the TimeStamp data type, but a Double precision or other numeric type to store the dates.

With all this said, it looks like the simplest way to convert the dates is to write a program in Delphi to change the date representation. For example, to calculate the shown dates, I wrote:

procedure TForm1.Button1Click(Sender: TObject);
var
  Dt: TDateTime;
begin
  Dt := 39660.2632087847;
  ShowMessage(FormatDateTime('dd/mm/yyyy hh:nn:ss.zzz', Dt));
end;

If you don't have Delphi at hand, there are other tools which use similar representations, and you have now all the info to do the Math by yourself if you're out of luck. A conversion to UnixTime is not particularly difficult.

Upvotes: 2

Related Questions