[Solved]Reformatting date from a NOW() call in a database

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
dyonak
Forum Commoner
Posts: 56
Joined: Wed Jun 22, 2005 10:22 am
Location: Minneapolis, MN
Contact:

[Solved]Reformatting date from a NOW() call in a database

Post by dyonak »

Ok I'm stumped, I've searched all day, I've looked at both the PHP and MySQL documentation. I've searched general tutorials on databasing with PHP in hopes of seeing something similar to find a solution. Let me know what I'm doing wrong.

I'm trying to add two hours (7200 sec) for the timestamp on my blog, after retrieving it from the DB. I don't want to edit it before it's stored or change it in the DB.
Here's my code

Code: Select all

$query = 'SELECT * FROM blog_entries ORDER BY 

date_entered DESC';

	
if ($r = mysql_query ($query)) { // Run the query.


	// Retrieve and print every record.
	while ($row = mysql_fetch_array ($r)) {
		print"<p><h2>{$row['title']}</h2>
		{$row['entry']}<br /><br />
		<div id='advert'>This blog was 

created on: {$row['date_entered']} CST";
	$ztime = $row["date_entered"];
	$ztime = $ztime+7200;
	print"$ztime</div></p><hr />\n";
The $ztime variable works because if I don't put +7200 behind it, it will display the date in duplicate directly after "CST". However, once I try to mathematically change the $ztime it gets wierd. My output with what is shown here would be:
Testing
Is the date populated?

This blog was created on: 2005-06-29 13:12:46 CST9205
The correct date/time shows up as it should from the {$row['date_entered']} call. The 9205 is from my calcution $ztime+7200. Which is partially right because 2005+7200=9205

As you can tell I'm an absolute PHP/MySQL beginner so go easy on me please.
Last edited by dyonak on Wed Jun 29, 2005 5:23 pm, edited 1 time in total.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

I do it in the sql query

Code: Select all

SELECT id, bla, DATE_FORMAT(ADDDATE(date, INTERVAL $time_dif HOUR), '%d-%b-%Y %r') AS date, bla FROM table ..
date being the row name and $time_dif I use to set the time zone difference between where my hosting is and my country.
dyonak
Forum Commoner
Posts: 56
Joined: Wed Jun 22, 2005 10:22 am
Location: Minneapolis, MN
Contact:

Post by dyonak »

Hmm, I'm not sure this would work for me. If I use time_diff in the query it would just select different tables based on the times specified. I'm looking to just add a date/time created stamp to each of my blogs to be displayed beneath the entry.
Jim_Bo
Forum Contributor
Posts: 390
Joined: Sat Oct 02, 2004 3:04 pm

Post by Jim_Bo »

Hi,

Replace time_dif with 0.

To display it call <?php echo $row['date']; ?> or how ever you like to do it ..

It will display now() as:

30-Jun-2005 07:31:35 AM

Altho I dont really understand quite what you mean with the above post.

hth
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

another option using the php date() and strtotime() functions this is:

Code: Select all

$result = mysql_query("select datetime from myTable")
  or die(mysql_error());
while($row = mysql_fetch_assoc($result)){
  echo date("d/m/Y g:i a", strtotime($row['datetime']." + 2 hours"));
}
dyonak
Forum Commoner
Posts: 56
Joined: Wed Jun 22, 2005 10:22 am
Location: Minneapolis, MN
Contact:

Post by dyonak »

Click Me.

Here's an example of the output I'm seeing with the above code. All of the dates are 2 hours earlier than they should be, the 9205 that follows the CST is explained in my original post.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

try my suggestion did you?
dyonak
Forum Commoner
Posts: 56
Joined: Wed Jun 22, 2005 10:22 am
Location: Minneapolis, MN
Contact:

Post by dyonak »

Testing
Is the date populated?

This blog was created on: 2005-06-29 13:12:46 CST9205 1120083166
Is the result of that, here's how I inserted your code:

Code: Select all

$query = 'SELECT * FROM blog_entries ORDER BY 

date_entered DESC';

	
if ($r = mysql_query ($query)) { // Run the query.


	// Retrieve and print every record.
	while ($row = mysql_fetch_array ($r)) {
		print"<p><h2>{$row['title']}</h2>
		{$row['entry']}<br /><br />
		<div id='advert'>This blog was 

created on: {$row['date_entered']} CST";
	$ztime = $row["date_entered"];
	$adjusted = (strtotime($row['date_entered']." + 2 hours"));
	$ztime = $ztime+7200;
	print"$ztime $adjusted</div></p><hr />\n";
	}
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

missing the date() function you are:

ex:

Code: Select all

created on: {$row['date_entered']} CST";    
  $ztime = $row["date_entered"];    
  $adjusted =  date("m/d/Y g:i a", strtotime($row['date_entered']." + 2 hours"));    
  $ztime = $ztime+7200;    
  print"$ztime $adjusted</div></p><hr />\n";    
}
dyonak
Forum Commoner
Posts: 56
Joined: Wed Jun 22, 2005 10:22 am
Location: Minneapolis, MN
Contact:

Post by dyonak »

You got it, thanks much Burrito. Someday I'll know why that worked.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

today that day will be:
read date() for all the formats I suggest you do.

Code: Select all

date("m/d/Y g:i a", time());
// m = month leading zeros
// d = day leading zeros
// Y = Year YYYY format
// g = hour no leading zero
// i = minute leading zero
// a = am / pm (lowercase)

strtotime(time()."+ 3 days");
// strtotime converts strings to unix time so it can be interpreted by the date() function
// time() = unix time of now
// . = concat to add a string to the timestamp
// + 3 days = add three days to the variable value
pretty straight forward and easy stuff it is.
dyonak
Forum Commoner
Posts: 56
Joined: Wed Jun 22, 2005 10:22 am
Location: Minneapolis, MN
Contact:

Post by dyonak »

Aughhughhguhgh <--Thursdays are Chewbacca speak days.

Thanks for the explanation Burrito. Why do you concatanate the +2 hours. Why doesn't. . .

Code: Select all

$adjusted =  date("m/d/Y g:i a", strtotime($row['date_entered']+ 2 hours));
. . . work? It seems like the way that you showed me how to do it would output

20050630082812 + 2 hours

I'm assuming this is just something built into PHP that let's it know when dealing with a time() that if you concat something it needs to add it to the min,hr,sec, etc.?

Sorry if this post made no sense it's far too early in the morning to be coherent.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

dyonak wrote:Aughhughhguhgh <--Thursdays are Chewbacca speak days.
LOL :lol: Very nice.

you have to concat the + 2 hours etc because strtotime looks for a string, simply doing +2 would throw it off because you have an add operator and you can't really "add" to strings.

strtotime is a really cool function that can take it's argument in a number of different formats.

ex:

Code: Select all

echo strtotime("yesterday");
//or
echo strtotime("last tuesday");
//or
echo strtotime("10/24/2003");
//etc
check the manual for more info on it. I absolutely love strtotime, in fact if it were a living entity, I'd divorce my wife and marry it...
dyonak
Forum Commoner
Posts: 56
Joined: Wed Jun 22, 2005 10:22 am
Location: Minneapolis, MN
Contact:

Post by dyonak »

Wow, looks great, I'll definitely delve a bit more. Thanks again Burrito.
Post Reply