Win::OLE Dates

barbie on 2007-01-03T18:30:29

I have a requirement to read the headers of email messages in Outlook. Thankfully, I have Mail::Outlook to do the majority of what I need, but it has been missing some functionality for sometime. I think I may have made some notes as to why it isn't there, but they've long since got buried somewhere. The extra functionality is the Sent or Received date of an email.

The Outlook API nicely provides SentOn and ReceivedTime, but the values returned are not what I expected. It returns a scalar value, e.g. 30567860, which when using localtime returns 'Sun Dec 20 19:05:32 1970'. However the mail in question displays 'Sat 04/12/2004 21:48' in Outlook itself. It would appear there is some other conversion required on the date value to correctly transform it into a human readable date value.

Anyone any idea what conversion is required and maybe even know of a Perl module that does it for me? There is nothing obvious on CPAN and both Google and the MSDN Library haven't turned up anything useful :(

Once I figure this out I'll be able to implement it into Mail::Outlook at last :)

---

Win32::OLE Dates are the days since 1900

Corion on 2007-01-03T19:12:42

I searched for OLE Date and got this link which confirmed my suspicion that OLE dates are just like Excel dates, the number of days since 1900. The fractional part is the fraction of a day. Mac Excel is Just Different, and I guess the same will go for all other Mac Office products that use 1904 as epoch.

Re:OLE Dates are the days since just before 1900

link on 2007-01-03T23:58:19

http://blogs.msdn.com/ericlippert/archive/2003/09/16/53013.aspx

Sounds kinda messy to me.

Re:Win32::OLE Dates are the days since 1900

barbie on 2007-01-04T12:29:24

It would appear that Excel and Outlook dates are different, as the integer returned is greater than the number of days since 1900. Note that the value returned in this instance is not a float.

Existing code

Alias on 2007-01-04T11:33:13

I believe I at one point had to write an implementation for one weird Microsoft DateTime format in Archive::Zip, have a look in there.

And it was indeed something very very odd.

Re:Existing code

barbie on 2007-01-04T12:36:03

I found the particular set of routines you mentioned, but alas they don't appear to apply to the date values returned by Outlook :( Using your algorithm I get 'Thu Jan 4 12:24:00 2007' +/- up to 2 minutes for every entry during 2004, 2005 and 2006.

There are a few websites where they mention odd handling of the Win32 dates, but none that I've found that mention the algorithms used to convert into any human readable format or even a Unix format :(

Win32::OLE::Variant ?

BinGOs on 2007-01-04T20:07:10

I had a problem with the lastLogin field of user accounts when querying ADSI with Win32::OLE.

The trick was to use Win32::OLE::Variant:

use Win32::OLE::Variant;
use Time::Local;

#snipped for brevity

my $unixtime = date2unix( Variant(VT_DATE, $object->Get("lastLogin")) );

exit 0;

sub date2unix {
  my $date = shift || return -11644473600;

  if ($date =~ /^([0-9]{2})\/([0-9]{2})\/([0-9]{4}) ([0-9]{2}):([0-9]{2}):([0-9]{2})/) {
     return timelocal($6,$5,$4,$1,$2-1,$3);
  }
}

If that helps at all.

Re:Win32::OLE::Variant ?

barbie on 2007-01-05T15:01:50

Excellent. Doesn't work for all values, but works for most. I think I now owe you much beer :)

A new version of Mail::Outlook out soon ;)