Martin’s Blog

A while ago I was working on a PHP web-application that uses a Pervasive SQL database which belonged to a Magic eDeveloper application. I encountered a really weird date format in the database which I couldn’t really place. I had a hunch that it might be the amount of days since AD (01-01-0000). This turned out to be right. Me and a friend devised a way to convert these dates to a unix timestamp in the following manner:

• Determine the amount of days from AD (01-01-0000) to the Unix epoch (01-01-1970). Outcome: 719163.
• Subtract the weird date with the amount determined above.
• Multiply the outcome by the amount of seconds in a day (86400).

Presto, there you have your unix timestamp.

The code in PHP:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

// A simple way to convert Magic date to a unix timestamp and vice versa
define("AD_TO_UNIXEPOCH", 719163);
define("SECONDS_IN_DAY", 86400);
 
// Function that converts the days to unixtime
function days2unixtime($date) {
   return ($date - AD_TO_UNIXEPOCH) * SECONDS_IN_DAY;
}
 
// The other way around
function unixtime2days($date) {
   return AD_TO_UNIXEPOCH + round($date / SECONDS_IN_DAY, 0);
}
 
// Proof of concept
$magicdate = 732468;
$unixtime = days2unixtime($magicdate);
print strftime("%d-%m-%Y", $unixtime) . "\n"; // Prepare for a hellish output ;)
print unixtime2days($unixtime); // And converted back

NB: For dates before the Unix epoch you will get a negative result.

Hope this helps someone who’s encountered the same problem.
If you know a better solution please leave a comment or mail.

Tags: conversion, convert, Date Format, Magic eDeveloper, Pervasive, PHP

This entry was posted on Friday, December 21st, 2007 at 7:07 pm and is filed under code. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.