[SOLVED] date format question

[alemapo]

Hi,

I apologize because this is a newbie question.  I am programming with dreamweaver/php using mysql for database.  I have a file containing user entered data and for the entry date I am using a field type "timestamp" and have it set in mysql to CURRENT_TIMESTAMP.  I am now trying to format the date only to the screen for display in the format mm-dd-yyyy.  I have read book after book and online and can't get a grasp on how to format that data stored in the timestamp field to the screen in the format I want.  All I seem to find is how to use mysql commands to format the current system date.  I can't find how to take the data from mysql in the timestamp format and display on my page as mm-dd-yy. If it would be better for me to store the date in a different way I am open to suggestions.  Thanks in advance for your help!

Pamela

 

My field in mysql is cls_entry_date with type of timestamp.  It is set to default of CURRENT_TIMESTAMP.  I would like to echo with php the date in the format mm-dd-yyy.

[MatthewJ]

SELECT DATE_FORMAT(yourdatefield, '%m-%d-%Y') FROM yourtable

[DLR]

my understanding is that the time is stored as an epoch timestamp in the format 0000-00-00 00:00:00 (year-month-day hours:minutes:seconds).

 

So you need to convert this each time you want to show a date.

 

What I do is get the date in MYSQL using SELECT like:

 

$sql = "SELECT * FROM your_table";

$res = mysql_query($sql);

 

$row = mysql_fetch_array($res);

 

$show = $row['time_in_mysql_table'];

 

//convert to time format

$show = strtotime($show)

 

//format to time you want

echo strftime('%d %b %Y,$show)  // use different formatting symbols depending on your needs

 

[johnathanhebert]

You could convert it from timestamp in PHP...

 

$timestamp = {value from mysql db};

echo date("m-d-Y",$timestamp);

[MatthewJ]

Or you could let the database do it for you like I posted above without any of the needless overhead

[johnathanhebert]

  Quote

All I seem to find is how to use mysql commands to format the current system date.  I can't find how to take the data from mysql in the timestamp format and display on my page as mm-dd-yy.

 

I just showed it in PHP because it sounded like the OP was looking for something other than mysql formatting... I agree, it's better to do it with mysql or just store it as a date type in mysql anyway.

[MatthewJ]

  Quote

my understanding is that the time is stored as an epoch timestamp in the format 0000-00-00 00:00:00 (year-month-day hours:minutes:seconds).

 

So you need to convert this each time you want to show a date.

 

What I do is get the date in MYSQL using SELECT like:

 

$sql = "SELECT * FROM your_table";

$res = mysql_query($sql);

 

$row = mysql_fetch_array($res);

 

$show = $row['time_in_mysql_table'];

 

//convert to time format

$show = strtotime($show)

 

//format to time you want

echo strftime('%d %b %Y,$show)  // use different formatting symbols depending on your needs

 

You can avoid all of that by converting it during selection as well

 

SELECT UNIX_TIMESTAMP(datefield) AS ts FROM your_table

 

[alemapo]

Thank you so much MatthewJ.  That worked!