Hi, folks! I'm new to MySQL and PHP and working on my 1st project and have spent hours reading the PHP manual, MySQL manual, and some posts on what I thought was a simple subject and is not... dates. I know everyone's different, but I'd like to know how folks store/retrieve/display their dates and what they would recommend for my situation...

MySQL table (kinda like an address book):
creationdate, datetime, will initialize manually when record/row created
modifieddate, timestamp, auto-updated everytime record/row modified
firstname, text
lastname, text
... 12 other fields

WEB site (I'll simplify)...
webpage1 = list of people (link to detail page... pass ID thru URL)
webpage2 = detail (select * tablename where ID=$id)

When I retrieve and display modifieddate, it is an unformatted string... 20040528142620

So, I do not how to correct this to show in a more meaningful format. Should I....

(1) select modifieddate a different way
(2) create & store a new field/column that is equivalent to modifieddate but formatted as I want it so it is already ready-to-show and displayed the same as the other fields are selected and displayed
(3) use PHP to convert string to timestamp and use PHP date()
(4) use MySQL to convert to Unix_Time and use PHP date()
(5) manually update modifieddate in a correctly formatted form whenever record/row modified

Any tips/suggestions and explanations would be appreciated.
Thanxalot!
--ST

are you sure that data type is datetime? it looks like timestamp. I'd say do number 3, but leave out the step of converting it to timestamp cos it looks like it already is in that format.

see
http://dev.mysql.com/doc/mysql/en/Da...ime_types.html

Thanx, skinny! yeah, I think you're right... I had thought it was unix/epoch but I see it is timestamp. When I use date() on it, though, I get some weird date that I cannot identify from the string, even if I parse it differently.

Anyone else? What do most folks do?
--ST

$whenmodified=20040528142620;
<? print ( date ("M d, y", $whenmodified) . " raw:" . $whenmodified); ?>

outputs...
Jan 18, 38 raw:20040528142620

It seems I am using the date() function incorrectly since I should be getting something like
May 28, 04

Can anyone point out my obvious but not-so-obvious-to-me mistake? Thanxalot!
--ST

Hi, folks! Hmmm... I guess I was looking for a pre-defined function. A Google search led me to http://www.awtrey.com/support/dbeweb/php.php where I saw an example of something similar. I have to manually parse the string myself. Here's a clipping from the website cited...

----------------------------------

Using the MySQL timestamp datatype in PHP requires a little coding. This next bit takes apart the MySQL data and generates a formated date. The substr function just grabs a substring from a given string. The mktime function generates a timestamp that the date function uses to generate the date and time in the format specified.

<?

////////////////////////////////
// The database has a timestamp record type that we can use to show the
// date the guestbook was filled out.
////////////////////////////////
$datefromdb = $guest['guest_time'];
$year = substr($datefromdb,0,4);
$mon = substr($datefromdb,4,2);
$day = substr($datefromdb,6,2);
$hour = substr($datefromdb,8,2);
$min = substr($datefromdb,10,2);
$sec = substr($datefromdb,12,2);
$orgdate = date("l F dS, Y h:i A",mktime($hour,$min,$sec,$mon,$day,$year));
?>
Date: <? echo $orgdate; ?>

Here is my finished code from my include file. If you are a newbie and trying to use/display/output mysql timestamp dates/times, I hope this helps...

<?

/* ************************************************** **********************
whenmodifiedconversion.inc
INCLUDE: TIMESTAMP DATE CONVERTER

This file parses a mysql timestamp, $whenmodified, into usable substrings.
The substrings are then used w/the php function mktime() to convert the
date to a Unix epoch time integer. Lastly, the php date() function is used
to assign appropriate values to $whenmodified_date and $whenmodified_time.

I should probably have made this some kind of re-usable function/method/class
but since this is my 1st php/mysql project, I do not know how to do that yet.
--ST
************************************************** ************************* */

$year = substr ($whenmodified, 0, 4);
$month = substr ($whenmodified, 4, 2);
$day = substr ($whenmodified, 6, 2);
$hour = substr ($whenmodified, 8, 2);
$min = substr ($whenmodified, 10, 2);
$sec = substr ($whenmodified, 12, 2);

$whenmodified_epochtime = mktime($hour, $min, $sec, $month, $day, $year);
$whenmodified_date = date ("m/d/Y", $whenmodified_epochtime);
$whenmodified_time = date ("g:i:s a", $whenmodified_epochtime);

?>

P.S. I guess I should add that a large part of my confusion had to do w/ the term "timestamp". In MySQL terminology, timestamp seems to mean something like yyyymmddhhmmss, whereas in PHP, timestamp seems to refer to a Unix Epoch Time integer.

Shheezz! And I thought it'd be simple.
--ST