strtotime date ranges 1

[d0nny]

I am trying to store dates in my database as a UNIX timestamp but there appears to be a problem saving (or converting) older dates.

For example, trying to save a date of 01/01/1854 (1St Jan 1854) doesn't work, I think. I say I think as the resulting conversion produces nothing.

Here's my code:
My input box accepts the date '01/01/1854'
I then

$dob = $_POST['dob'];
$dob_date_parts = explode("/",$dob);
$newdob = $dob_date_parts[2] . "-" . $dob_date_parts[1] . "-" . $dob_date_parts[0];
$dobDate = strtotime($newdob);

If I echo the $dobDate, it produces nothing.
If I put the date of 01/01/2011 in the input field the UNIX timestamp is successful.

So, I would deduce from this that the older dates are out of range??
The PHP manunal states:

The valid range of a timestamp is typically from Fri, 13 Dec 1901 20:45:54 UTC to Tue, 19 Jan 2038 03:14:07 UTC. (These are the dates that correspond to the minimum and maximum values for a 32-bit signed integer.) Additionally, not all platforms support negative timestamps, therefore your date range may be limited to no earlier than the Unix epoch. This means that e.g. dates prior to Jan 1, 1970 will not work on Windows, some Linux distributions, and a few other operating systems. PHP 5.1.0 and newer versions overcome this limitation though.

The last line of this I presume means that if I have PHP 5.1.0 or newer, I can any date in this variable???
I'm using PHP 5.2.14

If there is no way of converting this date (or even older dates) into UNIX timestamps, how is it best to store these dates?

 

[jpadie]

How you store dates in a db is linked to what you want to do with them. Caluculations within php are most easily performed on unix timestamps. In mysql it is best to use the native column types.

Strtotime is limited by your operating system. A 64 bit architecture may allow you greater flexibility but I do not know for sure

For storage of vey early dates it is possible that the time no longer matters. In which case you might use Julian dates.

Otherwise you could use the new datetime class instead of Strtotime.

 

[d0nny]

Hmmm... now your last line is intriguing.
I was looking at the DateTime class last night and couldn't work out out to use (which probably highlights my novice status in PHP!).

I was looking at this function:

http://goo.gl/dikFH

If I stick the Object oriented style in my page like so:

$date = DateTime::createFromFormat('Y-m-d', $people->dob);
echo $date->format('js M Y');

I get this error:
Fatal error: Call to undefined method DateTime::createFromFormat()

Not quite sure how I use this?
If I use the Procedural style, I get this error:
Fatal error: Call to undefined function date_create_from_format()

I think I need some help...

BTW, I've changed my database data type to be Date as I'm only interested in the date. I simply store the date in US-style format - 'YYYY-MM-DD'. I'm now trying to format that into something readable like '15th March 1854'

 

[d0nny]

Doh!

I need to write the class!

 

[jpadie]

for example

$dob = '1-1-1854'; //note m-d-Y NOT uk style
$date = new datetime($dob, 'UTC');
echo $date->format('js M Y');

or just use mysql

select date_format(cast(datefield as date),'%D %M %Y') as FormattedDateOfBirth from sometable

if you are certain of the datatype you can avoid the cast of course.

 

[jpadie]

ps the createfromformat method was only added to the class in php 5.3.0 . this may be why you cannot access it.

 

[d0nny]

Ah, good point!
I'm on 5.2.14

 

[d0nny]

Hmmm... now I'm struggling.
I want to subtract dates to get an age. The problem I have is the result I'm getting seems to only subtract the years as the age seems to be 1 year more (for those dates where there is no end date - there is no death date.

I think I might have to start at the beginning with all of this. Here are my requirements:

- I want to store dates ONLY
- there should be two date fields (Birth and Death)
- Death can obviously be NULL
- the dates could be any date in the past (not really interested in dates in the future)
- when I display the dates, I want to display a birthdate like 13th January, and then a year like 1954 (in separate columns)
- I also want to display the age of the person using the two dates above
- if Death date is null then display current age of person

Help!

 

[jpadie]

list($day, $year) = getFullDate($dob);
function getFullDate($date){
	$date = new datetime($date, 'UTC');
	$day = $date->format('jS M');
	$year = $date->format('Y');
	return array($day, $year);
}

list($years, $months, $days) = getAge($dob, $dod);
echo "as of today or the date of death, the individual was $years, $months and $days old";
function getAge($birth, $death){

	$death = empty($death) ? date('Y-m-d') : $death;
	$b = new datetime($birth, 'UTC');
	$d = new datetime($death, 'UTC');
	$diff = $b->diff($d);
	return $diff('%y,%m,%d');
}

 

[d0nny]

Thanks jpadie

I'm getting an error which I think relates to 'new datetime' line.

Can you perhaps explain how this works and why I get an error?

 

[jpadie]

What is the error? Bit difficult to help without info

 

[d0nny]

Fatal error: Uncaught exception 'Exception' with message 'DateTime::__construct() expects parameter 2 to be DateTimeZone, string given' in /home/sites/domain.com/public_html/php/functions.php:4 Stack trace: #0 /home/sites/domain.com/public_html/php/functions.php(4): DateTime->__construct('0828-02-13', 'UTC') #1 /home/sites/domain.com/public_html/templates/FS_AllList.html(31): getFullDate() #2 /home/sites/domain.com/public_html/php/despatch.php(93): require('/home/sites/domain...') #3 /home/sites/domain.com/public_html/index.php(33): require_once('/home/sites/domain...') #4 {main} thrown in /home/sites/domain.com/public_html/php/functions.php on line 4

 

[d0nny]

What I am also struggling with is sorting these dates.

If I simply sort by the birth date, the records are sorted by year (which may be correct??).

If I then add this to my SQL:
date_format(dob, '%D %M') as DOB

and then sort by DOB, the sort seems to sort by day??

(Damn, I never knew dates were as complex as this!!)

 

[jpadie]

list($day, $year) = getFullDate($dob);
function getFullDate($date){
    $date = new datetime($date, [red]new datetimezone('UTC')[/red]);
    $day = $date->format('jS M');
    $year = $date->format('Y');
    return array($day, $year);
}

list($years, $months, $days) = getAge($dob, $dod);
echo "as of today or the date of death, the individual was $years, $months and $days old";
function getAge($birth, $death){

    $death = empty($death) ? date('Y-m-d') : $death;
    $b = new datetime($birth, [red]new datetimezone('UTC')[/red]);
    $d = new datetime($death, [red]new datetimezone('UTC')[/red];
    $diff = $b->diff($d);
    return $diff('%y,%m,%d');
}

 

[jpadie]

to sort by date in mysql you can do this

SELECT *
FROM table
ORDER BY datefield ASC

this will work if the field type is date or numeric (for storage of unix timestamps)

if the field is varchar or something else then try this

SELECT *
FROM table
ORDER BY CAST(datefield as date) ASC

similarly if the column is not date or numeric then you should also cast it for use in where conditions that use date specific comparisons

[code]
SELECT *
FROM table
WHERE CAST(datefield as date) BETWEEN '2010-01-01' and '2010-02-28'
ORDER BY CAST(datefield as date) ASC

 

[d0nny]

Thanks for your efforts jpadie.

After much to-ing and fro-ing I decided that I would ditch the DATE storage and instead split the date in DAY, MONTH and then YEAR and stored each item separately. Bit long-winded but it solved all of my problems. And although it made my AGE calculation slightly more complex, at least it is correct.

I haven't given up just yet though so I will try your solution as it will be way more efficient!

 

[d0nny]

Another problem in my getAge function:

Fatal error: Call to undefined method DateTime::diff() in function

I tried this code in that function:

function getAge_j($birth, $death){
    $death = empty($death) ? date('Y-m-d') : $death;
    $b = new datetime($birth, new datetimezone('UTC'));
    $d = new datetime($death, new datetimezone('UTC'));
    $diff = $b->diff($d);
    return $diff[red]->format[/red]('%y,%m,%d');
}

but I still the diff error.

 

[jpadie]

my apologies , you need php 5.3 for the diff method too.

if upgrade is not an option then let us know. i am sure that there are alternative ways to skin the cat.

 

[d0nny]

Oh. I'm on 5.2.14 on a reseller account so upgrade is not an option.

 

[jpadie]

the top user contributed note on this page seems to do the trick

http://www.php.net/manual/en/function.date-diff.php