Page 1 of 1
[Solved]Reformatting date from a NOW() call in a database
Posted: Wed Jun 29, 2005 3:47 pm
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.
Posted: Wed Jun 29, 2005 3:55 pm
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.
Posted: Wed Jun 29, 2005 4:00 pm
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.
Posted: Wed Jun 29, 2005 4:28 pm
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
Posted: Wed Jun 29, 2005 4:48 pm
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"));
}
Posted: Wed Jun 29, 2005 4:50 pm
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.
Posted: Wed Jun 29, 2005 5:00 pm
by Burrito
try my suggestion did you?
Posted: Wed Jun 29, 2005 5:06 pm
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";
}
Posted: Wed Jun 29, 2005 5:11 pm
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";
}
Posted: Wed Jun 29, 2005 5:20 pm
by dyonak
You got it, thanks much Burrito. Someday I'll know why that worked.
Posted: Wed Jun 29, 2005 5:28 pm
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.
Posted: Thu Jun 30, 2005 8:28 am
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.
Posted: Thu Jun 30, 2005 9:48 am
by Burrito
dyonak wrote:Aughhughhguhgh <--Thursdays are Chewbacca speak days.
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...
Posted: Thu Jun 30, 2005 9:58 am
by dyonak
Wow, looks great, I'll definitely delve a bit more. Thanks again Burrito.