I've been storing dates in MySQL database tables in fields formatted as
Date (e.g. 2008-08-02). I now want to learn how to manipulate the data
(e.g. displaying only years or months or sorting by days).

I found several references that are confusing and also got some mixed
advice on another forum.

First, I plan on using PHP to manipulate and store dates. I was given
the following code to display my dates in a text format (e.g. May 8, 2008):

date("F d, Y",strtotime($Date))

However, it doesn't appear to work for dates earlier than 1900.

Also, someone told me that I should change my Date fields to Timestamp.
However, I haven't yet figured out how to format my data for Timestamp
or even assign a character length to Timestamp. I'm getting all sorts of
weird results. In the meantime, a MySQL quru told me to forget about
Timestamp.

So, I'd like to ask what methods you use to manipulate and display
dates. My dates consist mostly of birth dates and the dates various
people were elected to public office. I would like to be able to display
them in various formats (e.g. 2008-04-08, 2008, April 8, 2008).
Eventually, I'd like to be able to sort my table columns by such
variabls as year, month or date.

It might also be nice to be able to calculate things like...

May 7, 1777 = ? years ago
1777 and 1848 = a difference of how many years?

Can I do those sorts of things with PHP if my dates are stored in Date
fields rather than Timestamp? If I have to use Timestamp, how do I set
it up? I read that it doesn't accept dates that "include" the number 0,
but that doesn't make sense. I assume it means Timestamp dates can't
BEGIN with 0, so I changed 1884-04-12 to 1884-4-12. That nixed the
zeroes - but it displays the current date.

I'm working with phpMyAdmin, but I'm not certain it can handle Timestamp.

Thanks for any tips.

..oO(David Blomstrom)

>I've been storing dates in MySQL database tables in fields formatted as
>Date (e.g. 2008-08-02). I now want to learn how to manipulate the data
>(e.g. displaying only years or months or sorting by days).

MySQL knows functions like DAY(), MONTH(), YEAR() and DATE_FORMAT().
The ORDER BY clause should be obvious.

>I found several references that are confusing and also got some mixed
>advice on another forum.
>
>First, I plan on using PHP to manipulate and store dates. I was given
>the following code to display my dates in a text format (e.g. May 8, 2008):
>
>date("F d, Y",strtotime($Date))
>
>However, it doesn't appear to work for dates earlier than 1900.

Correct. UNIX timestamps are signed 32 bit integers. With negative
timestamps the most you can get is something around 1901-2038.

You should let the DB do the formatting with DATE_FORMAT(). See the
manual for details and format parameters.

>Also, someone told me that I should change my Date fields to Timestamp.

TIMESTAMP fields are only needed if you want to let the DB update them
automatically on a record's update. For most other things the DATE or
DATETIME types are enough.

>So, I'd like to ask what methods you use to manipulate and display
>dates. My dates consist mostly of birth dates and the dates various
>people were elected to public office. I would like to be able to display
>them in various formats (e.g. 2008-04-08, 2008, April 8, 2008).
>Eventually, I'd like to be able to sort my table columns by such
>variabls as year, month or date.

Use DATE or DATETIME for the column types, DATE_FORMAT() for the
formatting and ORDER BY for sorting. It's that simple.

>It might also be nice to be able to calculate things like...
>
>May 7, 1777 = ? years ago
>1777 and 1848 = a difference of how many years?

DATE_ADD()/DATE_SUB()/DATEDIFF()/...

There are a lot more useful date and time functions. Check out the
manual.

11.6. Date and Time Functions
http://dev.mysql.com/doc/refman/5.0/...functions.html

>Can I do those sorts of things with PHP if my dates are stored in Date
>fields rather than Timestamp?

Whenever a DB can do it - let the DB do it. It's much more efficient
than in PHP.

Micha

Below is the script I'm currently using to display dates in text
formats. It works for dates later than 1900. It replaces earlier dates
(as well as 0000-00-00) with "December 31, 1969." (I have no idea where
that date comes from; it's not in my database table.)

How would I replace date("F d, Y",strtotime($TermE)) with format_date?

It would appear that I need to do something like this...

function format_date($TermB)
{
$formatted_date = date('d/m/Y', $TermB);

return $formatted_date;

// We have returned before this
$this = 'will not be set';
}

But I don't know where to insert the code or what comes next.

Also, when I change ORDER BY PRES.Key to ORDER BY MONTH(), it doesn't
work. Do I need to write it a special way?

Thanks.

* * * * *

$result = mysql_query('select count(*) from pox_ppl_gov_presidents');
if (($result) && (mysql_result ($result , 0) > 0)) {
} else {
die('Invalid query: ' . mysql_error());
}

{
$Prez = mysql_query ("SELECT President, Term_Began, day(Term_Began),
monthname(Term_Began), year(Term_Began),
Term_Ended, Party, Vice_President
FROM pox_ppl_gov_presidents PRES
ORDER BY PRES.Key");
}

echo '<table>';
while ($row = mysql_fetch_array($Prez, MYSQL_ASSOC))
{
$Pres = $row['President'];
$TermB = $row['Term_Began'];
$TermE = $row['Term_Ended'];
$Party = $row['Party'];
$VP = $row['Vice_President'];

$DateTermB = date("F d, Y",strtotime($TermB));
$DateTermE = date("F d, Y",strtotime($TermE));

$TD1 = '<td>'.$Pres.'</td>';
$TD2 = '<td>'.$DateTermB.'</td>';
$TD3 = '<td><a href="" title="'.$DateTermE.'">'.$TermE.'</a></td>';
$TD4 = '<td>'.$Party.'</td>';
$TD5 = '<td>'.$VP.'</td>';

echo <<<EOD
<tr style="background: #$BG;">
$TD1
$TD2
$TD3
$TD4
$TD5
</tr>
EOD;
}
echo '</table>';

Please ignore this thread. I'm going to use an entirely different
strategy. Thanks.

..oO(David Blomstrom)

>Please ignore this thread. I'm going to use an entirely different
>strategy. Thanks.

OK. ;)

Micha