Local Time vs GMT in mysql

[confused and dazed]

Hello internet. I am trying to place the local time in a php table using the code below - it executes but is not displaying the local time EST that I would like - it is still displaying GMT. What am I doing wrong here? Table is ViewColumn for date in mysql is TStampAuto Increment column is M_idgru references with a 3 which is the gru number where I send the persons comments to the Cmnts column //code$result = mysql_query("SELECT * FROM View WHERE gru='3' AND DATE_ADD(NOW(), INTERVAL 2 HOUR) > TStamp ORDER BY M_id DESC");echo "<table border='0'><tr><th>Name</th><th>Comments</th><th>Date & Time</th></tr>";while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>" . $row['NameC'] . "</td>"; echo "<td>" . $row['Cmnts'] . "</td>"; echo "<td>" . $row['TStamp'] . "</td>"; echo "</tr>"; }

[justsomeguy]

You can use the convert_tz function to have MySQL convert the timezone: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz You can also set MySQL to use a certain timezone if you have access to the config files for it, or you can tell it to use a particular time zone for your session. http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html

[confused and dazed]

I would prefer method one. What’s interesting is that I made my way to the page you referenced prior to posting and I could not make the code work...How do I integrate the conver_tz into my code? BTW can you make a comment on why my code did not work?

[justsomeguy]

You're not doing anything to change the value that it returns, it still returns the value stored in the row. You're comparing a different value in the WHERE clause, but that's not the value it returns. e.g.: SELECT CONVERT_TZ(Tstamp, 'GMT', 'EST') AS Tstamp FROM ...

[confused and dazed]

AS? I have not seen the AS verbiage in any code or tutorial as of yet. Wow I sure have a long way to go... So I played around with the code and used this code (below) and it worked - only problem is that now there is the military time issue. I have played around with some code but I cannot get it to work out. Suggestions? $result = mysql_query("SELECT CONVERT_TZ(TStamp, 'MST', 'EST') AS TStamp, NameC, Cmnts FROM View WHERE gru='3' ORDER BY M_id DESC");

[justsomeguy]

You can use date_format to format the date, check the examples. It's similar to using PHP's date function. https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

[confused and dazed]

I played around with the code for a while but I have not been able to integrate the following piece of code DATE_FORMAT('TStamp', '%b %d %Y %r') into my $result argument below... $result = mysql_query("SELECT CONVERT_TZ(TStamp, 'MST', 'EST') AS TStamp, NameC, Cmnts FROM View WHERE gru='3' ORDER BY M_id DESC");

[justsomeguy]

$result = mysql_query("SELECT DATE_FORMAT(CONVERT_TZ(TStamp, 'MST', 'EST'), '%b %d %Y %r') AS TStamp, NameC, Cmnts FROM View WHERE gru='3' ORDER BY M_id DESC");

[confused and dazed]

I tried that way and it did not work... Maybe there was some syntax problem. I will try it again.

[confused and dazed]

O.K. so originally I had apostrophes where should not be any DATE_FORMAT('CONVERT_TZ(TStamp, 'MST', 'EST')', '%b %d %Y %r') changed to==> DATE_FORMAT(CONVERT_TZ(TStamp, 'MST', 'EST'), '%b %d %Y %r') so once I got rid of those it executed but all three entries that displayed had the same time stamp (I think it was supposed to be current time) but they all were wrong to the current time... All three comments in my database had this time stamp displayed in the webpage. They should not be all the same and the time is about 30 minutes slow... Mar 05 2013 12:04:10 PM

[confused and dazed]

Strange once again - I wiped out all the entries in gru=3 (sort of like starting over) in the database now it is acting right and displaying EST on a 12 hour rotation not 24. Why did I need to wipe out the entries for it to work right? Why would it not fix the ones already there? All is well that ends well!!! Thanks for the help on this one!!!

[justsomeguy]

It sounds like the dates were all the same to start with, for whatever reason.

[confused and dazed]

There were all differnet - spread between three different days. The first time I ran this new code DATE_FORMAT(CONVERT_TZ(TStamp, 'MST', 'EST'), '%b %d %Y %r') it made all the entries in my database the same time and date - All of them - regardless of gru=3 or not. Strange.