how to format the timestamp??

Question

samsons17 -4 Posting Whiz in Training

16 Years Ago

Hi..
I got a problem with the date and time in php..
This is the story.

1)I want to get the date and time automatically whenever a person write onto the field that i have made(eg. the blogpost field).

2)Then,after i stored it into the database, i want to output it into my desired format.That is like this :

September 8 2010 at 11:46am

And what i have done was created a field of dateandtime and made its stored data as the timestamp type in my phpmyadmin.

BELOW IS THE WORKS :

This is the part where someone entering something into the field :

..some codes here...
<textarea rows="18" id="postcontent" name="blogpost" cols="100"></textarea>
...some codes here...

and after clicked submit,this page will be redirected to the other page:

This is the page and my query to insert the date and time :

<?php
$blogpost=$_POST['blogpost'];

$query = "INSERT INTO blogpost,dateandtime (blogpost,dateandtime) VALUES ( '{blogpost}',NOW() ) ";
?>

This is how I echoing the date on the mainpage :

$result = mysql_query("SELECT * FROM dateandtime");

while($row=mysql_fetch_array($result))
{
echo "$row["blogpost"]".$row["dateandtime"];
}

Thus,by using the timestamp,how could i do that format that i want?Or there is the other way that i could use to get the date automatically and format it into that way?

Please help.Thank You :)

mysql php

Edited 16 Years Ago by samsons17 because: n/a

3 Contributors
8 Replies
120 Views
1 Day Discussion Span
Latest Post 16 Years Ago Latest Post by samsons17

Dani AI

Generated 9 Months Ago

Quick recap: the formatting can be done in two places — in the database (MySQL) or in PHP. ’s suggestion to format in the SELECT is valid, and the runtime error you hit was because the SQL string never ran (you assigned the SQL to a variable but didn’t call mysql_query() to execute it). That’s why the fix looked “magical” when pointed it out.

If you prefer formatting in PHP (more flexible, timezone-aware), use PHP’s DateTime to parse the stored value and output the exact string you want. Example:

$dt = new DateTime($row['dp_datetime']);
echo $dt->format('F j Y \a\t g:ia'); // "September 8 2010 at 11:46am"

This keeps display logic in the app and makes conversions (UTC ↔ local) easier.

If you want the DB to return the formatted string directly (useful when many clients consume the same query), use MySQL’s formatting functions in the SELECT to return the date as a formatted text column. See the MySQL docs for the available format specifiers.

A few practical tips learned from this thread:

Don’t pass the raw SQL string into fetch functions — execute the query (e.g., mysql_query() or, better, mysqli/PDO) and fetch the result resource.
Prefer PDO or MySQLi with prepared statements instead of the old mysql_* extension (deprecated).
Consider timezone behavior: TIMESTAMP columns can be converted by MySQL based on server/session timezone; DATETIME stores literal values. Decide whether to store UTC and convert on display.
Further reading: PHP DateTime docs (https://www.php.net/manual/en/class.datetime.php) and MySQL DATE_FORMAT / date-time type docs (https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format).

All 8 Replies

MooGeek 16 Posting Whiz

16 Years Ago

http://php.net/manual/en/function.date.php

MooGeek 16 Posting Whiz

16 Years Ago

http://lmgtfy.com/?q=timestamp+php

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

hielo 65 Veteran Poster · Answer 1 · 2010-09-13T21:31:07+00:00

instead of

SELECT * ...

use:

SELECT blogpost, DATE_FORMAT(dateandtime,'%M %e %Y at %h:%i%p') as dateandtime ...

samsons17 -4 Posting Whiz in Training · Answer 2 · 2010-09-14T10:49:59+00:00

http://lmgtfy.com/?q=timestamp+php

dont you read my post first?i am using now() function to get the current date and time from the user meaning it'll get them automatically..

the question again,is how do i format my date and time so that it will appear as this:

September 8 2010 at 11:46am

i already look around the php.net again and again,but i am not sure how could i do this,thats why i am asking here.So instead of giving the link,could you show me some example please..

tq.

hielo 65 Veteran Poster · Answer 3 · 2010-09-14T10:55:14+00:00

try my post.

SELECT blogpost, DATE_FORMAT(dateandtime,'%M %e %Y at %h:%i%p') as dateandtime FROM TABLENAME

just replace TABLENAME with the actual name of your table.

samsons17 -4 Posting Whiz in Training · Answer 4 · 2010-09-14T13:32:58+00:00

try my post.
SELECT blogpost, DATE_FORMAT(dateandtime,'%M %e %Y at %h:%i%p') as dateandtime FROM TABLENAME
just replace TABLENAME with the actual name of your table.

Sory for a late reply..
I have done like what you said,but i dont know why,i am getting an error...

here is my actual code :

<?php
$blogpost=("SELECT id,title,blogpost, DATE_FORMAT(dp_datetime,'%M %e %Y at %h:%i%p') as dp_datetime FROM blogpost");
 
		while($row = mysql_fetch_array($blogpost))
			{
		$id = $row["id"];
		$result2 = mysql_query ("SELECT id FROM comments WHERE entry='$id'");
        $total_comments = mysql_num_rows($result2);

				echo "<div id='blogcontainer'>"."<div id='blogtitle'>".$row["title"]."</div>".
			"<br/>"."<div id='blogpost'>"."<div id='mainpost'>".$row["blogpost"]."</div>"."<br />"."<div id='date'>".$row["dp_datetime"]."</div>"."<br/>".
			"<a href='#lightbox' class='commenttoggle' rel='facebox'>".$total_comments." "."comment</a>".
			
				"</div>";	
	}
				
		?>

hielo 65 Veteran Poster · Answer 5 · 2010-09-14T20:22:26+00:00

on line 2 you forgot to call mysql_query() so that it ACTUALLY executes the query. You are simply assigning a string (the sql statement) to $blogpost.

samsons17 -4 Posting Whiz in Training · Answer 6 · 2010-09-14T21:00:46+00:00

on line 2 you forgot to call mysql_query() so that it ACTUALLY executes the query. You are simply assigning a string (the sql statement) to $blogpost.

WOW!...you are great...
its actually works like magic!

Thank you so much for helping :)

how to format the timestamp??

Dani AI

Recommended Answers Collapse Answers

All 8 Replies

Recommended Answers