Windows does not support dates prior to midnight (00:00:00), January 1, 1970

"Tine M�����������������������" wrote:
> Table 5-1. RA.dat: Registrations and Applications
> Column Data Element Content Definition Used in Our Example?
> 12 Date Constructed mm/dd/yyyy Yes
> 13 Date Dismantled mm/dd/yyyy Yes
>
> EVEN that I could import the .dat files ONLINE (Unix) but not OFFLINE
> (Windows) the dates are NOT showed before or the date January 1, 1970 and I
> don't think now that there is a solution to this.:-(

There is. The problem is that both you and the author of the book are
looking in the wrong direction in search of the answer.

Attempting to use the PHP date() or strtotime() functions is pointless.
Not only do you have the limitation of the range of timestamps
available, the original data already contains a date. The only problem
is that it's not in the format that you want. Just change the order of
the elements.

It's not clear from your explanation whether you are already extracting
the date, but let's say your data is in this format:

Date Constructed 3/12/1848 Yes

<?php
$row1 = 'Date Constructed 3/12/1848 Yes';

function extractDate($row) {
// find a date in mm/dd/yyyy format
if (preg_match('/\d{1,2}\/\d{1,2}\/\d{4}/', $row, $match)) {
// if found, split the date into an array
$date = explode('/', $match[0]);
// reformat the array in yyyy-mm-dd format
return sprintf('%02d-%02d-%d', $date[2], $date[0], $date[1]);
}
// if date cannot be found, return error message instead
else {
return 'Date not available';
}
}
echo extractDate($row1); // displays 1848-03-12
?>

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

>>"David Powers" wrote
>> I'm sorry, Tine, but I do not have the time to read through your table
>> definitions and more than 100 lines of code

OK thats fair enough. I will try to explain it easier.:-)

>>Dawid Powers wrote
>>Where are you getting the original values of $row[12] and $row[13], and
>>what do they contain?

Table 5-1. RA.dat: Registrations and Applications
Column Data Element Content Definition Used in Our Example?
12 Date Constructed mm/dd/yyyy Yes
13 Date Dismantled mm/dd/yyyy Yes

EVEN that I could import the .dat files ONLINE (Unix) but not OFFLINE
(Windows) the dates are NOT showed before or the date January 1, 1970 and I
don't think now that there is a solution to this.:-(
For importing the .dat files offline I changed "Date" to "varchar" so I
could import the data without the error.

I have read this from a book:
?Caution You may be wondering whether it's possible to work with dates prior
to the Unix epoch (00:00:00 UTC
January 1, 1970). Indeed it is, at least if you're using a Unix-based
system. On Windows, due to an integer
overflow issue, an error will occur if you attempt to use the
timestamp-oriented functions in this chapter in
conjunction with dates prior to the epoch definition.

So now I give up but if I find the solution I will write it in the forum.:-)

/Tine

"Tine M�����������������������" wrote:
> It's
> some excercises I'm making from this fantastic book
> http://googlemapsbook.com/ - Chapter 5 and the .dot files we use is from
> here http://wireless.fcc.gov/uls/data/complete/r_tower.zip. The programmer
> of the book told me to just delete the dates because there are not going to
> be used in the exercise

I'm sorry, Tine, but I do not have the time to read through your table
definitions and more than 100 lines of code, particularly since the
author of the book has already told you the dates are unimportant. When
working with any technology, you need to understand what can and cannot
be done.The normal way to handle dates outside of the PHP limitations is
to store and format them in MySQL, which supports dates from 1000-01-01
to 9999-12-31.

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

"David Powers" wrote
> Where are you getting the original values of $row[12] and $row[13], and
> what do they contain?
>
>> I don't belive that there is no solution because there will people who
>> have
>> to use informations prior to January 1, 1970 so there MUST be a solution.
>
> There probably is a solution, but without knowing what it is that you're
> trying to do, it's not possible to help.



Thanks for your reply. I hope I have given you information enough but ask
for more if it's not enough for you to understand what my problem is. It's
some excercises I'm making from this fantastic book
http://googlemapsbook.com/ - Chapter 5 and the .dot files we use is from
here http://wireless.fcc.gov/uls/data/complete/r_tower.zip. The programmer
of the book told me to just delete the dates because there are not going to
be used in the exercise but now I have used so much time that I like to get
it solved for learning and then when I get a real project where I need to
use Windows then I maybe know the solution.:-)

sql-table file
---------------------------------------------------------------
CREATE TABLE fcc_location (
loc_id int(10) unsigned NOT NULL auto_increment,
unique_si_loc bigint(20) NOT NULL default '0',
lat_deg int(11) default '0',
lat_min int(11) default '0',
lat_sec float default '0',
lat_dir char(1) default NULL,
latitude double default '0',
long_deg int(11) default '0',
long_min int(11) default '0',
long_sec float default '0',
long_dir char(1) default NULL,
longitude double default '0',
PRIMARY KEY (loc_id),
KEY unique_si (unique_si_loc)
) ENGINE=MyISAM ;

CREATE TABLE fcc_owner (
owner_id int(10) unsigned NOT NULL auto_increment,
unique_si_own bigint(20) NOT NULL default '0',
owner_name varchar(200) default NULL,
owner_address varchar(35) default NULL,
owner_city varchar(20) default NULL,
owner_state char(2) default NULL,
owner_zip varchar(10) default NULL,
PRIMARY KEY (owner_id),
KEY unique_si (unique_si_own)
) ENGINE=MyISAM ;

CREATE TABLE fcc_structure (
struc_id int(10) unsigned NOT NULL auto_increment,
unique_si bigint(20) NOT NULL default '0',
date_constr date default '0000-00-00',
date_removed date default '0000-00-00',
struc_address varchar(80) default NULL,
struc_city varchar(20) default NULL,
struc_state char(2) default NULL,
struc_height double default '0',
struc_elevation double NOT NULL default '0',
struc_ohag double NOT NULL default '0',
struc_ohamsl double default '0',
struc_type varchar(6) default NULL,
PRIMARY KEY (struc_id),
KEY unique_si (unique_si),
KEY struc_state (struc_state)
) ENGINE=MyISAM;
---------------------------------------------------------------





index.php
---------------------------------------------------------------
<?php

set_time_limit(0); // this could take a while

//ONLINE
// Connect to the database
require($_SERVER['DOCUMENT_ROOT'] . '/db_credentials.php');
$conn = mysql_connect($db_server, $db_user, $db_pass);
mysql_select_db($db_name, $conn);


// Open the Physical Location Coordinates file
$handle = @fopen("../Data/RA.dat","r");
if ($handle) {
while (!feof($handle)) {
$buffer = fgets($handle, 4096);
$row = explode("|",$buffer);
if ($row[3] > 0) {
// Modify things before we insert them
$row[12] = date("Y-m-d",strtotime($row[12]));
$row[13] = date("Y-m-d",strtotime($row[13]));
$row[23] = addslashes($row[23]);
$row[24] = addslashes($row[24]);
$row[30] = addslashes($row[30]);

// Formulate our query
$query = "INSERT INTO fcc_structure (unique_si, date_constr,
date_removed, struc_address, struc_city, struc_state, struc_height,
struc_elevation, struc_ohag, struc_ohamsl, struc_type)
VALUES ({$row[4]}, '{$row[12]}', '{$row[13]}', '{$row[23]}',
'{$row[24]}', '{$row[25]}', '{$row[26]}', '{$row[27]}', '{$row[28]}',
'{$row[29]}', '{$row[30]}')";

// Execute our query
$result = @mysql_query($query);
if (!$result) echo("ERROR: Duplicate structure info #{$row[4]} <br>\n");
}
}
fclose($handle);
}

echo "Done Structures. <br>\n";

// Open the Ownership Data file
$handle = @fopen("../Data/EN.dat","r");
if ($handle) {
while (!feof($handle)) {
$buffer = fgets($handle, 4096);
$row = explode("|",$buffer);
if ($row[3] > 0) {
$row[7] = addslashes($row[7]);
$row[14] = addslashes($row[14]);
$row[16] = addslashes($row[16]);
$query = "INSERT INTO fcc_owner (unique_si_own, owner_name,
owner_address, owner_city, owner_state, owner_zip) VALUES ({$row[4]},
'{$row[7]}', '{$row[14]}','{$row[16]}', '{$row[17]}', '{$row[18]}')";
$result = @mysql_query($query);
if (!$result) {
// Newer information later in the file: UPDATE instead
$query = "UPDATE fcc_owner SET owner_name='{$row[7]}',
owner_address='{$row[14]}', owner_city='{$row[16]}',
owner_state='{$row[17]}', owner_zip='{$row[18]}'
WHERE unique_si_own={$row[4]}";
$result = @mysql_query($query);
if (!$result)
echo "Failure to import ownership for struc. #{$row[4]}<br>\n";
else
echo "Updated ownership for struc. #{$row[4]} <br>\n";
}
}
}
fclose($handle);
}

echo "Done Ownership. <br>\n";

// Open the Physical Locations file
$handle = @fopen("../Data/CO.dat","r");
if ($handle) {
while (!feof($handle)) {
$buffer = fgets($handle, 4096);
$row = explode("|",$buffer);
if ($row[3] > 0) {
if ($row[9] == "S") $sign = -1; else $sign = 1;
$dec_lat = $sign*($row[6]+$row[7]/60+$row[8]/3600);
if ($row[14] == "W") $sign = -1; else $sign = 1;
$dec_long = $sign*($row[11]+$row[12]/60+$row[13]/3600);
$query = "INSERT INTO fcc_location (unique_si_loc, lat_deg, lat_min,
lat_sec, lat_dir, latitude, long_deg, long_min, long_sec,
long_dir, longitude) VALUES ({$row[4]},'{$row[6]}', '{$row[7]}',
'{$row[8]}', '{$row[9]}', '$dec_lat','{$row[11]}', '{$row[12]}',
'{$row[13]}', '{$row[14]}', '$dec_long')";
$result = @mysql_query($query);
if (!$result) {
// Newer information later in the file: UPDATE instead
$query = "UPDATE fcc_location SET lat_deg='{$row[6]}',
lat_min='{$row[7]}', lat_deg='{$row[8]}', lat_dir='{$row[9]}',
latitude='$dec_lat', long_deg='{$row[11]}', long_min='{$row[12]}',
long_sec='{$row[13]}', long_dir='{$row[14]}', longitude='$dec_long'
WHERE unique_si_loc='{$row[4]}'";
$result = @mysql_query($query);
if (!$result)
echo "Failure to import location for struc. #{$row[4]} <br>\n";
else
echo "Updated location for struc. #{$row[4]} <br>\n";
}
}
}
fclose($handle);
}

echo "Done Locations. <br>\n";

?>
---------------------------------------------------------------

"Tine M�����������������������" wrote:
> When I test the file on Unix no problem but now I want to test it on my own
> desktop which is XP Pro and now I got this message
> http://www.tinemuller.dk/div/datofejl.gif and on line 20 and 21 I have this:
> 20 ----- $row[12] = date("Y-m-d",strtotime($row[12]));
> 21 ----- $row[13] = date("Y-m-d",strtotime($row[13]));

Where are you getting the original values of $row[12] and $row[13], and
what do they contain?

> I don't belive that there is no solution because there will people who have
> to use informations prior to January 1, 1970 so there MUST be a solution.

There probably is a solution, but without knowing what it is that you're
trying to do, it's not possible to help.

--
David Powers
Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
http://foundationphp.com/

Could you not use datetime instead of timestamp?


On Wed, 18 Oct 2006 06:43:55 +0200, "Tine Müller" <tinem@email.dk>
wrote:

>I can't figure out how to solve this problem.
>
>When I test the file on Unix no problem but now I want to test it on my own
>desktop which is XP Pro and now I got this message
> http://www.tinemuller.dk/div/datofejl.gif and on line 20 and 21 I have this:
>20 ----- $row[12] = date("Y-m-d",strtotime($row[12]));
>21 ----- $row[13] = date("Y-m-d",strtotime($row[13]));
>
>I have used days to figure out to solve this problem and have read somewhere
>to look at this site http://phplens.com/phpeverywhere/adodb_date_library but
>I'm a beginner of PHP MySQL so I don't know what to change in the code. Can
>someone help, please before I go nuts?:-)
>
>I don't belive that there is no solution because there will people who have
>to use informations prior to January 1, 1970 so there MUST be a solution.
--
Steve
steve at flyingtigerwebdesign dot com