Not able to get current Timestamp

Question

Need help getting pulling timestamp from database. timestamp = CURRENT_TIMESTAMP I need it to show our current timezone EST time. Need help doing this.

current query

    mysql_select_db($database_localhost, $localhost);
$query_leadsscott = "SELECT * FROM leads WHERE marketer = 'scott' ORDER BY `timestamp` DESC";
$leadsscott = mysql_query($query_leadsscott, $localhost) or die(mysql_error());
$row_leadsscott = mysql_fetch_assoc($leadsscott);
$totalRows_leadsscott = mysql_num_rows($leadsscott);` 



' <h6>Marketing Reports for Emily</h6>
    <table border="1">
      <tr>
        <td>Timestamp</td>
        <td>Marketer</td>
        <td>Facility</td>
        <td>Name</td>
        <td>Type</td>
        <td>Description</td>
        <td>Cocality</td>
        <td>State</td>
        <td>Zip</td>
        <td>Latlng</td>
      </tr>
      <?php do { ?>
        <tr>
          <td><?php echo $row_leadsemily['timestamp']; ?></td>
          <td><?php echo $row_leadsemily['marketer']; ?></td>
          <td><?php echo $row_leadsemily['facility']; ?></td>
          <td><?php echo $row_leadsemily['name']; ?></td>
          <td><?php echo $row_leadsemily['type']; ?></td>
          <td><?php echo $row_leadsemily['description']; ?></td>
          <td><?php echo $row_leadsemily['locality']; ?></td>
          <td><?php echo $row_leadsemily['state']; ?></td>
          <td><?php echo $row_leadsemily['zip']; ?></td>
          <td><a href="https://www.google.com/maps/place/<?php echo $row_leadsemily['latlng']; ?>"> Link </a></td>
        </tr>
        <?php } while ($row_leadsemily = mysql_fetch_assoc($leadsemily)); ?>
    </table>'

Timestamp that shows back on report time as follows

Timestamp
2016-05-09 13:16:57

I need this to show EST it is currently showing UTC time

score 0 · Answer 1 · answered May 10 '16 at 07:40

0

remove this

 ORDER BY timestamp DESC

then check the query. If this works fine means then sort the timestamp as follows

 order by year  DESC, month DESC, day DESC"

answered May 10 '16 at 07:40

ARUN G

94
1
9

Timestamp that shows back on report time as follows Timestamp 2016-05-09 13:16:57 I need this to show EST it is currently showing UTC time which is three hours behind – Steven Rostorfer May 10 '16 at 07:49
date_default_timezone_set('EST'); echo $row_leadsemily['timestamp']; refer the timezone set here http://stackoverflow.com/a/33163392/3548072 – ARUN G May 10 '16 at 08:17
this didnt change the timezone still receiving UTC time from database @ARUN.G – Steven Rostorfer May 10 '16 at 08:39
You need to check the datetime field in mysql after inserting a row (echo now();) if this is your timezone? then all fine else convert to ur timezone then manually insert the time stamp refer http://stackoverflow.com/a/10184834/3548072 and post your data insertion code – ARUN G May 10 '16 at 09:07

score 0 · Accepted Answer · answered May 10 '16 at 09:10

0

If the offset is always the same you can just modify your output and leave the timestamp in the db as UTC:

 DATE_ADD(timestamp, INTERVAL 2 HOUR)

answered May 10 '16 at 09:10

Michael

663
1
13
28

How did I do this? In my table data? – Steven Rostorfer May 10 '16 at 09:25
Just use this for your query: `SELECT DATE_ADD(timestamp, INTERVAL 2 HOUR) FROM leads WHERE marketer = 'scott' ORDER BY `timestamp` DESC` – Michael May 10 '16 at 09:27
You have to add all fileds you might want to get so it might look like this: `SELECT DATE_ADD(timestamp, INTERVAL 2 HOUR), marketer, facility, name, type, description, locality, state, zip, FROM leads WHERE marketer = 'scott' ORDER BY` – Michael May 10 '16 at 09:31
`$query = "SELECT * FROM leads WHERE DATE_ADD(timestamp, INTERVAL 2 HOUR) AND marketer <> 'Tommy' AND DATE(`timestamp`) = CURDATE() ORDER BY 'timestamp' ASC";` Is not changing time interval – Steven Rostorfer May 10 '16 at 09:32
you must use it instead of the `*` after the `SELECT` – Michael May 10 '16 at 09:39
Using the following in MYSQL `SELECT DATE_ADD(timestamp, INTERVAL 2 HOUR), marketer, facility, name, type, description, locality, state, zip, FROM leads WHERE marketer = 'scott';` I get the error! `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM leads LIMIT 0, 30' at line 1` – Steven Rostorfer May 10 '16 at 09:49
Is there a way i can send my script to be looked at? This just has me puzzled and have looked everywhere, google, MySQL forums and documents. called my hosting and am not able to have the time zone changed unless hosting a dedicated server. – Steven Rostorfer May 10 '16 at 09:53
You can update the code in your question above. The error you got is not from the posted query. Do you have more querys in your code? Make sure that each query ends with `;` otherwhise mysql will mix up two or more querys to one which will lead to an error. – Michael May 10 '16 at 10:52

Not able to get current Timestamp

2 Answers2