Extracting Year, or Year/Month, or Full Date

[storm925]

I’m new to PHP and I am hoping that some here can help me further with date formatting in PHP. I have a range of dates that sometimes are as follows: 1914-00-00, or 1952-12-00 or 1990-01-31. Because some of the dates are historical, I don’t always have a month or a day.

I want the dates to display as follows (from example above):

1914
Dec 1952
01 Jan 1990

I’m trying to work with the following example, however it’s not working at all (opened is in DATE format)…

$result = mysqli_query($link, 'SELECT store, opened FROM storez');

while ($row = mysqli_fetch_array($result))
{
$supply[] = array('store' => $row['store'], 'opened' => row['opened']);
}

<div>
<?php echo date('M',strtotime($item['opened'])); ?>
<?php echo date('d',strtotime($item['opened'])); ?>,
<?php echo date('Y',strtotime($item['opened'])); ?>
</div>

The dates that come back are as follows, which are not even close…

Nov 30, 1938
Dec 31, 1969
Nov 30, 1977

Hopefully, someone can help me with this problem. Maybe someone has run into this before? I’m sure it’s an easy fix, I just can’t think of another way. I’m trying to stay away from modifying the query, however if that is the only way, I’ll go down that road. Thank you again for reading my post and any help/suggestions would be greatly appreciated. --Ben

[deathshadow60]

1) why the multiple echo and <?php ?> for nothing?

2) what is the typical contents of $item['opened'] -- are you sure that the value stored there is in fact a string type time valid for strtotime to process?

3) Not actually seeing $item being defined, and not sure what the while/query before it is even for... especially since your making an array entry identical to $row, and with mysqli you could just use fetch_all.

Filling in the missing blanks, I suspect this is what you are TRYING to do...

$result=$link->query('SELECT store,opened FROM storez');
$supply=$result->fetch_all(MYSQLI_ASSOC);

foreach ($supply as $item) {
	echo '
		<div>',date('M d Y',strtotime($item['opened'])),'</div>';
}

Which should work just fine so long as the contents of $item['opened'] is a valid string time -- you do that with a timestamp, christmas only knows what the result would be. I'd try:

	echo '
		<div>',$item['opened'],' = ',date('M d Y',strtotime($item['opened'])),'</div>';

To verify that the contents of 'opened' is indeed a valid value. If it says a plaintext time, your fine. If it returns a 32 bit number, lose the strtotime as that's a unix timestamp, not a string based time.

-- edit -- re-reading your original post -- I suspect your incomplete dates are a likely cause as to why strtotime is getting confused... OH, WOW... yeah, *nix timestamps, which is what DATE works on, Can't handle dates prior to the UNIX epoch - 00H:00M:00S Jan 1, 1970. strtotime on a date prior to that is going to mess up badly.

[litebearer]

Also, if you use datetime as your field type you can then use dates prior to 1970

[storm925]

Deathshadow60,

Thank you for the response and I see where my code can be a little confusing, I did not include it all in order to make it shorter. I’m also still a little wet behind the ears with PHP. I see now why strtotime will not work for dates prior to the epoch you gave.

I don’t mind shortening the code, I’m just trying to get to the “desired results” and have been toying with it for a while. Just looking for a simple solution to this.

litebearer,

I see where I could change the column from DATE to DATETIME, however I believe the range for DATETIME is 1901 to 2155, and 0000… and I have some dates that are prior to 1901. Thank you for the suggestion

[storm925]

Dear Forum,

I’ve tried messing with code a little and maybe someone has an idea/suggestion on this change so that I can get to my “desired result.” The dates are outputted as follows…

1857-00-00
1914-00-00
1952-12-00
1990-01-31

The “desired result” is…

1857
1914
Dec 1952
01 Jan 1990

Here is some of the example code that I am working with. I’m getting closer to my “desired result” and maybe someone has an idea of how to get there. Again, I’m new to this stuff, so baby steps are always helpful:

$result = mysqli_query($link, 'SELECT store, DATE_FORMAT(opened,”%d”) AS open_day, DATE_FORMAT(opened,”%b”) AS open_month, DATE_FORMAT(opened,”%Y”) AS open_year FROM storez');

<?php echo $item['open_day'], $item['open_month'], $item['open_year']; ?>

The dates that come back are as follows, which are a little closer to the “desired result”, but not the whole enchilada…

00 1857
00 1914
00 Dec 1952
01 Jan 1990

Again, any ideas would be awesome. I know the SQL looks a little messy, If there is a better way to get to my “desired result”, I’m all open for it. Thank you again for reading my post. --Ben

[deathshadow60]

One thing computers do NOT do well is non-uniform storage of information or storage of partial information. Like "just a year" is meaningless since the date storage formats all pretty much DEMAND a day and month (and hour and second and even in some cases hundreths). When you store "1857" it's going to automatically plug in the rest of the values, and those values could in fact be gibberish it doesn't know what to do with. You want to use a date field, you're going to have to fill in that missing data or live with it being broken.

If your data isn't consistent/complete, using date or datetime or any other date storage method is probably inappropriate... you won't be happy with the results because DATE, DATETIME, TIMESTAMP are not meant for tracking just a year, or just a year and month, it's meant for tracking an exact date and time down to the second. In fact, zero's are invalid values in dates for months and days, and are probably being treated as -1 month and -1 day.

So... why not just store it as a normal string... VARCHAR, TEXT, etc... Don't even TRY to operate on it as a date since, well... you don't have a complete date as the variable types for dates in programming languages expect it. Unless you plan on performing operations on it, even TRYING to store incomplete information in a variable that expects a complete time is... well.... just not going to work.

You don't have a complete date, so it doesn't go in any of the date/time formats. Just treat it like normal plaintext. Don't even try to store it in the database as a date, don't try to use date or strtotime on it -- because that's NOT what you have for data.

Sometimes the simplest answer is best.

It's either that, or write your own parser.... are they ALWAYS in that format of YYYY-MM-DD? If so, you could just explode them, then test for 00 (which is invalid) as to whether to show the rest.

One moment.

[deathshadow60]

Ok, if all your stored dates are in this EXACT format:

YYYY-MM-DD

and zero's indicate missing values, then THIS should format that for you.

<?php

$months=array(
	'invalid',
	'Jan','Feb','Mar','Apr','May','Jun',
	'Jul','Aug','Sep','Oct','Nov','Dec'
);

function formatDate($date) {
global $months;
	$split=explode('-',$date);
	return (
		$split[2]=='00' ? '' : $split[2].' '
	).(
		$split[1]=='00' ? '' : $months[(int)$split[1]].' '
	).$split[0];
}

$testDates=array(
	'1857-00-00',
	'1914-00-00',
	'1952-12-00',
	'1990-01-31'
);

foreach ($testDates as $date) {
	echo formatDate($date),'<br />';
}

?>

Due to the incomplete data, the built in date/time/storage functions just aren't going to cut it for you... but if you have that exact date format, this should get the job done... the above code outputs:

1857
1914
Dec 1952
31 Jan 1990

Hope this helps. Does NOT remove leading zero's from the day though.... you could probably get that to work just by changing this line:

$split[2]=='00' ? '' : $split[2].' '

to read:

$split[2]=='00' ? '' : ((int)$split[2]).' '

typecasting to integer strips out the zero.

[litebearer]

DATETIME

A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but permits assignment of values to DATETIME columns using either strings or numbers.

http://dev.mysql.com/doc/refman/5.0/...-overview.html

[deathshadow60]

1000-01-01

... and therein lies his real problem -- he's got 00's for month and date as values... which aren't valid values for date, datetime, or any other date handler in SQL or PHP.

So pointing at DATETIME really isn't going to help storm925. He puts in 1952-00-00, any of those are going to wig out. Minimum value for a month or a day is one, not zero. It's why he's going to have to have them stored as strings, not date, datetime, timestamp or any other format, and then do the formatting of them the 'hard way'...

Which my code above shows in action.

[r937]

Like "just a year" is meaningless since the date storage formats all pretty much DEMAND a day and month ...

except for mysql

mysql specifically ~does~ allow 00 in the month and day portion of a DATE or DATETIME column

If you are not using the NO_ZERO_IN_DATE SQL mode, the day or month part can be zero. This is convenient if you want to store a birthdate in a DATE column and you know only part of the date.
...
Under this mode, MySQL verifies only that the month is in the range from 0 to 12 and that the day is in the range from 0 to 31. These ranges are defined to include zero because MySQL allows you to store dates where the day or month and day are zero in a DATE or DATETIME column. This is extremely useful for applications that need to store a birthdate for which you do not know the exact date. In this case, you simply store the date as '1999-00-00' or '1999-01-00'. If you store dates such as these, you should not expect to get correct results for functions such as DATE_SUB() or DATE_ADD() that require complete dates. (If you do not want to allow zero in dates, you can use the NO_ZERO_IN_DATE SQL mode).

in storm925's case, the requirement to store partial date information exactly fits this scenario, so using a DATE column is fine

the only issue then is the display

[deathshadow60]

Huh, never noticed that one, where's it hidden? (Is it just me, or are the mysql docs one of the worst out there?)

Where does one disable the 'NO_ZERO_IN_DATE" flag, since it appears neither my server nor my XAMPP install are set up for that... Yeah, 00 dates are bouncing an error.

Not that being able to store it that way in SQL does him any good since he needs to get it into PHP in a meaningful manner.

[r937]

Where does one disable the 'NO_ZERO_IN_DATE" flag.

http://dev.mysql.com/doc/refman/5.1/...-sql-mode.html

[Salathe]

On the PHP side, you could use DateTime::createFromFormat() to try and parse the three different formats (year, year and month, and year, month and day).

function formatDate($date) {
    $formats = array('Y-00-00' => 'Y', 'Y-m-00' => 'M Y', 'Y-m-d' => 'd M Y');
    foreach ($formats as $input_format => $output_format) {
        if ($datetime = DateTime::createFromFormat($input_format, $date)) {
            return $datetime->format($output_format);
        }
    }
    // Bad date format, trigger an error or throw exception or whatever you want to do
    return 'unknown';
}

echo formatDate('1952-12-00'); // Dec 1952 

[storm925]

I’d like to say thank you to everyone who has contributed to this post, especially Salathe for seeing what I was trying to accomplish. I’m excited that I can keep the flexibility of my ‘opened’ column in a DATE format in mysql -- important when entering in historical dates where month and year are not always available. I thought that I would have to loose the ability to enter partial dates, or even have to create more columns (e.g. year, month, day) in mysql. My intent was not to change mysql, but to use the power of PHP to manipulate the output -- which Salathe shown can be done simply with a function. As a reference to others, I changed my query back to the following…

$result = mysqli_query($link, 'SELECT store, opened FROM storez');

I left the opened column in a DATE format, allowing me to enter partial dates like 1857-00-00, 1952-12-00, etc. And, I used Salathe’s function with an addition to allow me to loop…

<?php
if(!function_exists('formatDate')){
function formatDate($date) {
$formats = array('Y-00-00' => 'Y', 'Y-m-00' => 'M Y', 'Y-m-d' => 'd M Y');
foreach ($formats as $input_format => $output_format) {
if ($datetime = DateTime::createFromFormat($input_format, $date)) {
return $datetime->format($output_format);
}
}
return 'unknown';
}
}
echo formatDate($item['opened']);
?>

I’m now getting my desired results. YES! I can’t say thank you enough for taking the time to read my post and responding to it. --Ben

* I've tried indenting code with the space bar, not working... Sorry.