format timestamp

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

format timestamp

Post 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?
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

SELECT DATE_FORMAT(thedatecol, '%Y-%m-%d %H:%i:%s') FROM thetable;
User avatar
DuFF
Forum Contributor
Posts: 495
Joined: Tue Jun 24, 2003 7:49 pm
Location: USA

Post 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 :P
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post 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
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

am I doing something wrong?
Yep ;) But without seeing a snippet of your code it's hard to say what it is :o
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post 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
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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.
Last edited by markl999 on Thu Oct 23, 2003 10:40 am, edited 1 time in total.
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post by yaron »

Thanks
Working!!
Post Reply