timestamps - bit of a newbie question

[Orionsbelter]

hi, there.

 

I have a timestamp in my mysql database called timestamp and it'll record the current timestamp. however it also records the time etc how do i use php to take apart these for example if i wanted to echo the date and time in a different order or if i only wanted to echo the date not the time.

 

how is this possible?

 

Thanks for reading.

 

[jcbones]

SELECT DATE_FORMAT(`timestamp`,'%W %M %Y') FROM `table`;

 

MySQL date time functions

 

[Orionsbelter]

hmmm but what if i already have it saved in the database? like 2011-05-06 22:11:57 and i want to only echo the month or date? what that code above work?

 

 

[jcbones]

You can format the date anyway you like, that is why I gave you an example, and then posted a link to the manual.  The manual has listings that will give you any result you desire.

 

[smsmarketeers]

The problem is, the other person showed you how to do it using MySQL and you said PHP. So, here is how we do this in PHP:

 

<?php

$myDate = '2011-05-06 16:10:30';

echo date('m/d/Y', strtotime($myDate)); // Output: 05/06/2011
echo date('g:iA', strtotime($myDate)); // Output: 4:10PM
echo date('M jS, Y @ g:iA', strtotime($myDate)); // Output: Jan 6th, 2011 @ 4:10PM

?>

 

Basically, you need to take your MySQL result, convert it to a UNIX timestamp using strtotime() then use the date() function to display what you want.

 

For more information: http://php.net/manual/en/function.date.php

[xyph]

jcbones' solution is leaps and bounds better than smsmarketeers from an efficiency and simplified coding standpoint.

[smsmarketeers]

jcbones' solution is leaps and bounds better than smsmarketeers from an efficiency and simplified coding standpoint.

 

Yeah well, he said he wanted to do it in PHP, not the MySQL query nor did he ask for "simplified" or "efficient". Not to mention that I would NEVER use MySQL to control dates and times because of timezone settings and other problems. PHP makes a much better effort at controlling times with timezones.

[xyph]

You use the word better without context.

 

<?php

$timezone_offset = -3;

$query = "
SELECT
	DATE_FORMAT(
		DATE_ADD(`date`,INTERVAL $timezone_offset HOUR), '%W %M %Y %H:%i:%s'
	) as `date_format`
WHERE
	`conditions` = 'foobar';
";

?>

[Orionsbelter]

thanks for the quick reply, i've read the manual but i'm a little confused

 

when using the php $date=mysql_fetch_object(mysql_query("SELECT DATE_FORMAT(`date`,'%W %M %Y') FROM `updates` WHERE id='1'"));

 

then echo $date it doesn't work

 

 

please help.

[Orionsbelter]

thanks for replying smsmarketeers example seems the easy to understand on a newbie level, i will use this code and also look at advancing to other examples upon increasing my PHP skills

 

thank you all will put the examples too the test now

[xyph]

thanks for the quick reply, i've read the manual but i'm a little confused

 

when using the php $date=mysql_fetch_object(mysql_query("SELECT DATE_FORMAT(`date`,'%W %M %Y') FROM `updates` WHERE id='1'"));

 

then echo $date it doesn't work

 

 

please help.

 

you want to use something like this:

 

$date= mysql_result(
mysql_query("SELECT DATE_FORMAT(`date`,'%W %M %Y') FROM `updates` WHERE id='1'"),
0
);

 

If you have multiple fields to grab, you can use something like this instead

 

<?php

$data= mysql_fetch_assoc(
mysql_query("SELECT DATE_FORMAT(`date`,'%W %M %Y') as `date`, `another_colum` FROM `updates` WHERE id='1'"),
0
);

print_r( $data );

?>

[xyph]

Oops, remove the ,0 from my second example