Format Date Pre 1900

[nikster]

I have a database of guitarists birthdays. The birthday is stored in a MySQL DB in DATE format as YYYY-MM-DD. Virtuoso Spanish classical guitarist Andrés Segovia was born on Feb 21, 1893. I would like the PHP output to be "February 21, 1893". When I use the common date output. this date come out as January 1, 1970.

 

I understand there is a problem with either the DB or PHP understanding dates pre 1900. (I'm a bit of a nube)  Through a Google search I am able to get close with this code;

 

while($row = mysql_fetch_array($result)) {

$bday = explode('-',$row['bday']);

$bdayf = $bday[1] .'/' . $bday[2] .'/' . $bday[0] ;

echo "$bdayf";

}

 

This outputs 02/21/1893. But I can't get it the final mile and get it to output as February 21, 1893. I have to beilive this a common problem but I cannot find a clear answer.

 

I thank anyone in advance for your help.

 

Nick Paonessa

 

 

 

[requinix]

You might be on a system that doesn't support dates that far in the past. You're doing the SQL queries yourself? Do the date formatting in the query.

[nikster]

I'm running;

PHP Version 5.3.10
MySQL Version 5.5.20

 

Again. I'm  abit of a nube. I looked at that link. It's not completely clear on how to formulate the Query. Here's what my query currently looks like. (My goal is that if today is a guitarist's birthday it will show up in a  side bar.)

===================

$TodayM = (date ('m'));
$TodayD = (date ('d'));

 

$result = mysql_query("
SELECT * FROM guitaristbday
WHERE MONTH(bday) = $TodayM AND DAY(bday) = $TodayD
ORDER BY bday");

====================

[Jessica]

Format the date in the select part. Instead of select *, specify the fields and you can format it there.

[Q695]

do it with a +/-BIGINT then have it output the date based on that.

[xenLiam]

Your existing code, as you said, is already there. The problem is converting the month.

 

You can do it manually, but it's a long code.

$months = array(
"01" => "January",
"02" => "February",
"03" => "March",
"04" => "April",
"05" => "May",
"06" => "June",
"07" => "July",
"08" => "August",
"09" => "September",
"10" => "October",
"11" => "November",
"12" => "December"
);

echo $months[$bday[1]] . " " . $bday[2] . ", " . $bday[0];

[Barand]

SELECT thedate,
DATE_FORMAT(thedate, '%M %e, %Y')
FROM test.dates
ORDER BY thedate
LIMIT 2;

+------------+-----------------------------------+
| thedate    | DATE_FORMAT(thedate, '%M %e, %Y') |
+------------+-----------------------------------+
| 1893-02-21 | February 21, 1893                 |
| 2012-01-01 | January 1, 2012                   |
+------------+-----------------------------------+

Alternatively,

$d = new DateTime('1893-02-21');
echo $d->format('F j, Y');     //-> February 21, 1893

Edited May 24, 2013 by Barand

[nikster]

There is clearly many ways to skin this cat. (I hope none of you have cats :-) They all work!

 

Thank you all very much.

[nikster]

The date for Andres Segovia now looks right (February 21, 1893) but as I've progressed to the next phase of the project, I've come across a new but related issue. In the last issue, I was sorting by name. The next page groups all the Guitarists' birthdays by Month so now I'm sorting by birthday. Because, the (I think) PHP is interpreting 1983-02-21 as Jan 1 1970, even though it looks right (February 21, 1893 due to the formatting solution), the entry is showing up in the January group. Not the February group.
 
Here's my query;
    $sql = mysql_query("SELECT *
    FROM guitaristbday
    ORDER BY MONTH(bday), bday ASC
    ");
 
The date are in the MySQL database as 1893-02-21 in a field called bday and it's fromatted as type DATE. Again, I'm a nube so I don't manage the DB via a command line. I use PHPMyAdmin. WHen I click on the field name, it sorts it correctly. In ASC order, it puts that date first. When you do that, it puts the query of what you just did at the top of the page.
 
SELECT *
FROM `guitaristbday`
ORDER BY `guitaristbday`.`bday` DESC
LIMIT 0 , 30
 
Because I'm not exactly sure where the limitation is (MySQL or PHP) I'm not sure what side to tackle this from. Here is my PHP Code;

=========================

<?php
echo "<div align='left'>";

// Get format for formatted BDay month
include "/includes/months.php";

// login to db
include ("includes/db.php");

//Perform Query for Header info
    $sql = mysql_query("SELECT *
    FROM guitaristbday
    ORDER BY MONTH(bday), bday ASC
    ");
    
//Show # of Records found
echo "Total records found - " .mysql_num_rows ($sql)."";

// initialize header and set it to an empty string
$header = '';

// mysql_fetch_assoc will return each row from the result set
while ($row=mysql_fetch_assoc($sql)) {

$id = $row['id'];
$name = $row['name'];
$bday = $row['bday'];
$bday2 = explode('-',$row['bday']);
$bdayf = $months [$bday2[1]] .' ' . $bday2[2] .', ' . $bday2[0] ;
$month_no = date('m', strtotime ($bdayf));
$month = date('F', strtotime ($bday));

// we only want to print the header if it hasn't been seen in the result yet
if ($header!=$month) {
echo "<div class='Titles'></br>";
echo "$month";
echo "</div></b>";

// means the current header display is for the nonth name
$header = $month;
}

// include formatted City/Firm

echo "$name - $bday<br/>";
}

//close align div
echo "<div>";
?>

=============================

Nick

[Barand]

The mysql ORDER BY works fine for me

mysql> SELECT thedate,
    -> DATE_FORMAT(thedate, '%M %e, %Y') as formatted
    -> FROM test.dates
    -> WHERE thedate < '2012-01-05'
    -> ORDER BY MONTH(thedate), DAY(thedate) DESC;
+------------+-------------------+
| thedate    | formatted         |
+------------+-------------------+
| 2012-01-04 | January 4, 2012   |
| 2012-01-03 | January 3, 2012   |
| 2012-01-02 | January 2, 2012   |
| 2012-01-01 | January 1, 2012   |
| 1893-02-21 | February 21, 1893 |
+------------+-------------------+

[Q695]

The date tag interprets the date every second previously, or after 1/1/1970.

 

it is also saying they're 10 years younger than they really are.  If only real life was that easy.

[nikster]

Thanks Barand. Like I said, In MySQL, it's sorting fine. it's when I try and look at it via PHP.

 

Nick

[Jessica]

Well, stop doing that then.

 

date will NOT work with pre 1070 dates.

Edited May 24, 2013 by Jessica

[Q695]

the easiest way to do it would be to have the guitarist have date columns with the month, day, and year in them, possibly hour, minute, second, and day of the week fields in them.

 

Well, stop doing that then.

date will NOT work with pre 1070 dates.

the date function will work with dates pre1070, only IF you put them into two column data spots with the overflow in the 1st column, unless you tested that idea already, and it gave you an error.

[Jessica]

What??

 

This problem is already solved the easiest way. See Barands post

Edited May 24, 2013 by Jessica

[Q695]

Why don't people mark solved then?

[Barand]

Thanks Barand. Like I said, In MySQL, it's sorting fine. it's when I try and look at it via PHP.

 

Nick

my earlier reply #7 showed you how to handle it in mysql and PHP

[Q695]

Then mark it solved.

[Jessica]

Q, you need to read the posts, read who posts what, and calm down. You seem to have an issue with following the threads. Let the moderators handle it if there's an issue.