0

I got data from a Persavive V12 Db. The dates are in a weird format. They are like float :

Example of date in table : 39787.0

Do someone know this kind of format ? It seems to be the number of days from 1901-01-01.

Since the dates are float, is it possible to add days from a date in PHP ? Like :

$from = '1901-01-01';
$date = date('Y-m-d', strtotime($from .' +'. $float .' days'));

I got always 1970-01-01. Is there another way ?

Vincent Decaux
  • 9,857
  • 6
  • 56
  • 84
  • strtotime will output a unix timestamp meaning the first day posible is 1970 01 01 what is the solfware writing this date ? Maybe we can search what is the date format. – Nicolas Racine Aug 11 '16 at 18:00
  • Is the field in the Pervasive database marked as Float in the DDFs? Pervasive has had a Date field for many years and it's not a Float. . Is this from an application you developed or third party? Have you contacted the vendor of the app to find out the date format? – mirtheil Aug 11 '16 at 18:01
  • Unlucky I don't have access to the DB know, just got data from the db but no DDF. I will try to find the date format in my Pervasive server local. – Vincent Decaux Aug 11 '16 at 18:12

2 Answers2

0

My first answer so don't hurt me much. I'm not quite sure what you want to do (put back in the database or perform further operations so I only posted one solution)

The number you posted 39787.0 is a unix timestamp. You need to use mktime to convert what you want to a number (And you need date to convert it to a readable form). Since mktime uses 1901-01-01 as a starting date as well you have to make a couple of changes.

I was a biz lazy and didn't include all the code for all fields but this should allow you modify to add whatever you want (and possibly clean it up to a single line if you want). I made it in long form so it's easy to read.

    <?

    $olddate = 39787.0;
    $hour = 0;
    $min =  0;
    $sec =  0;
    $month =  0;
    $year = 0;
    $day = 12;
    $month += date("m",$olddate);
    $day += date("d",$olddate);
    $year += date("Y",$olddate);
    $x = date("Ymd",mktime($hour,$min,$sec,$month,$day,$year));
    echo $x;

    ?>
Forbs
  • 1,256
  • 1
  • 7
  • 9
  • It's not an Unix Timestamp. Since timestamp are more like `1470943987`. And if it was, you just have to use date('d-m-Y', $timestamp), no need of mktime. – Vincent Decaux Aug 11 '16 at 19:33
0

If 39787.0 is in the database, what value is that in a real date (using the application)? If it's 2008-12-05, the it's a VB Date. Do you know what the application was written in? I've seen people use the VB Date data type and store it in an 8 byte float in Btrieve (back in 2003). If it's a VB Date, 1 is 1899-12-31. You should be able to add the number to 1899-12-30 to get the correct date value. Something like:

<?php
$date = new DateTime('1899-12-30');
$date->add(new DateInterval('P39787D'));
echo $date->format('Y-m-d') . "\n";
?>
mirtheil
  • 8,952
  • 1
  • 30
  • 29