[SOLVED] How to format - Mysql 5 timestamp

[Alexhoward]

Good morning PHP people!

 

I am using an auto timestamp in mysql 5.

 

This gets added automatically when the data is inserted

 

It's saved as:

 

2008-06-16 09:09:53

 

and i would like to change it to:

 

Mon 16 June

 

problem is that when i use the normal php date formatting method it comes out with some bizare results...

 

has anyone come across this before...?

 

Thanks

[AndyB]

Leave it alone in the database - that format is the best.

 

Change how it is displayed by your scripts after being retrieved from the database.

 

The date() function allows all kinds of personal preference date formats.

[zenag]

select date_format( date fieldname , '%a %b %Y' ) as date from....

[Alexhoward]

Hi Guys,

 

Thanks for the replies!

 

here's my code: (just a test and an extract of the full thing)

<?php 


include("config.php");

//connect to the mysql server
$link = mysql_connect($host, $db, $pass)
or die ("Could not connect to mysql because ".mysql_error());

//select the database	
mysql_select_db($db)
or die ("Could not select database because ".mysql_error());

$query2=" SELECT * FROM mail ";
$result2=mysql_query($query2);
echo mysql_error();

while($row = mysql_fetch_array($result2)) { 
    $from = $row['sender'];
    $title = $row['title'];
    $body = $row['body'];
    $date = $row['date'];
    $ref = $row['ref'];
}

echo "$date</br>";

echo date("D-d-M-Y", $date);


?>

 

problem being that my output is:

 

2008-06-16 09:26:23

Thu-01-Jan-1970

 

any ideas why this is happening...?

 

Thanks

[zenag]

echo date("D-M-Y", $date);

[Alexhoward]

Hi zenag

 

That just gives me

 

2008-06-16 09:26:23

Thu-Jan-1970

 

or have i missed something...?

[zenag]

sorry ...i ve misunderstood....

try this ...it satisfies ur expectations...

while($row = mysql_fetch_array($result2)) { 
    
    $date = $row['created_at'];
    $ref = $row['ref'];$date=date('D-M-Y');

[zenag]

echo $date ..

[Alexhoward]

Excellent!

 

That seems to work a treat!

 

Thanks

 

Just a quick question thou, as the two variables have the same name

 

i.e.

 

$date = $row['date'];

$date=date('D d/M/Y');

 

and i'm echo-ing  $date

 

is it just combining them...?

[kenrbnsn]

The date() function expects the second parameter to be a UNIX timestamp (integer), you are passing it a string, so it gives you back the zero date (1/1/1970). You need to use the function strtotime() to convert the string date stamp to a UNIX timestamp before you use it with the date() function.

 

<?php
echo "$date</br>";

echo date("D-d-M-Y", strtotime($date));

?>

 

or use zenag's method.

 

Ken

[Alexhoward]

Cheers kenrbnsn.

 

both methods work beautifully

 

Just out of interest i take it it is best practice to use:

 

echo date("D-d-M-Y", strtotime($date));