[RESOLVED] formatting MySQL dates

[saraadmin]

Hello PHPers,

I have the following function which accepts a MySQL date as a parameter and prints a nicely formatted textual version of it. It is intended to allow any portion of the date not to be present. e.g. "1999-11-00 00:00:00" will be printed as "November, 1999" and "2003-00-00 00:00:00" will be printed as "2003". However, in the cases where only the year or only the year and the month are present, it prints the previous year or the previous month.

e.g. "1999-11-00 00:00:00" prints "October, 1999" and "2003-00-00 00:00:00" prints "2002". Weird, eh?

function long_date($date)
{
	$year = intval(substr($date, 0, 4));
	$month = intval(substr($date, 5, 2));
	$day = intval(substr($date, 8, 2));
	// added some debugging code to check these values
        // they're correct.

if (strlen($date) > 10)
{
	$hour = intval(substr($date, 11, 2));
	$minute = intval(substr($date, 14, 2));
	$second = intval(substr($date, 17, 2));
} else { $hour = 0; $minute = 0; $second = 0; }

$timestamp = mktime($hour,$minute,$second,$month,$day,$year);

if (($day ==0 ) && ($month == 0) && ($year == 0)) { $format = "NO DATE"; }
elseif (($day == 0) && ($month == 0)) { $format = "%Y"; }
elseif ($day == 0) { $format = "%B, %Y"; }
elseif (($hour == 0) && ($minute == 0) && ($second == 0)) { $format = "%A %e %B, %Y"; }
else { $format = "%H:%M %A %e %B, %Y"; }

return strftime($format, $timestamp);
}

PS. I wrote this function ages ago and it used to work. I think its gone wrong since I upgraded my system to PHP5. (Debian testing).

[saraadmin]

OK, this function is quite old fashioned and the strlen($date) > 10 is a bit shakey.

So I thought I'd try using strptime() instead. But it doesn't work:

<?php
$date = "1999-11-00 00:00:00";
$format = "%Y-%m-%d %H:%M:%S";
$date_rec = strptime($date, $format);
var_dump($date_rec);
?>

just returns bool(false). Can anyone see whats wrong with it?

PHP vs. 5.1.2-1+b1

[TF_TheMoon]

If you have a date with a days of 00 then it will convert the date to the last day of the previous month. What you're best of doing is to check the string for "00 " and replace it with 01, then you will get the right month. If you wanst those invaid dates to only print the part that was known then you'll need to keep a check of wha ttests have been performed and what has been replaced so you know what date format to use. But why do you have mysql dates like that? It suggest your data is rather dodgy

[saraadmin]

OK, I see

function long_date($date)
{
	$year = intval(substr($date, 0, 4));
	$month = intval(substr($date, 5, 2));
	$day = intval(substr($date, 8, 2));

if (strlen($date) > 10)
{
	$hour = intval(substr($date, 11, 2));
	$minute = intval(substr($date, 14, 2));
	$second = intval(substr($date, 17, 2));
} else { $hour = 0; $minute = 0; $second = 1; }

if (($day ==0 ) && ($month == 0) && ($year == 0)) { $format = "NO DATE"; }
elseif (($day == 0) && ($month == 0)) { $format = "%Y"; }
elseif ($day == 0) { $format = "%B, %Y"; }
elseif (($hour == 0) && ($minute == 0) && ($second == 0)) { $format = "%A %e %B, %Y"; }
else { $format = "%H:%M %A %e %B, %Y"; }

// these need to be valid for mktime
if ($day == 0) $day = 1;
if ($month == 0) $month = 1;

$timestamp = mktime($hour,$minute,$second,$month,$day,$year);

return strftime($format, $timestamp);
}

works. Thanks!

Because our data is archival, quite often we only know what year or what month something happened in.

Cheers,
http://www.sara.uea.ac.uk/