Page 1 of 1
format timestamp
Posted: Thu Oct 23, 2003 9:48 am
by yaron
Hello all,
I have a timestamp field in my db.
When I display it I want to format it into yyyy-mm-dd H:I:S
But I get exatcly like its stored on the db yyyymmddhhiiss
just numbers with no spaces.
any ideas?
Posted: Thu Oct 23, 2003 10:04 am
by markl999
SELECT DATE_FORMAT(thedatecol, '%Y-%m-%d %H:%i:%s') FROM thetable;
Posted: Thu Oct 23, 2003 10:04 am
by DuFF
Heres an example:
Code: Select all
<?php
include("header.php");
@mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query = "SELECT * FROM table_name WHERE option=$option";
$result=mysql_query($query) or die("Error: " . mysql_error());
$num = mysql_num_rows($result);
mysql_close();
$i=0;
while ($i < $num) {
//this is the part that creates the date format
$date=mysql_result($result,$i,"date");
$timestamp = strtotime($date);
$date_format = 'Y-mm-dd H:I:S';
$newdate = date($date_format, $timestamp);
echo $newdate; //outputs something like "2003-10-23 11:01:32"
}
?>
EDIT: Damn, he beat me to it. Well that ways a bit easier heh

Posted: Thu Oct 23, 2003 10:20 am
by yaron
Sorry guys,
Both ways don't work.
the date is not the only field i need from this query and if I try to use the query way I get null on the date field.
The second way with the date function and strtotime gives an error that it can't handle dates prior to 0000:00:00 or something...
am I doing something wrong?
Thanks
Posted: Thu Oct 23, 2003 10:22 am
by markl999
am I doing something wrong?
Yep

But without seeing a snippet of your code it's hard to say what it is

Posted: Thu Oct 23, 2003 10:26 am
by yaron
"SELECT id,name,desc,DATE_FORMAT(date, '%Y-%m-%d %H:%i:%s') FROM table
All fields are coming out fine except the date
Posted: Thu Oct 23, 2003 10:29 am
by markl999
I'm guessing it's the way you're fetching the results. Try
"SELECT id,name,desc,DATE_FORMAT(date, '%Y-%m-%d %H:%i:%s') AS nicedate FROM table" then use 'nicedate' the same way you use id, name and desc.
Posted: Thu Oct 23, 2003 10:39 am
by yaron
Thanks
Working!!