changing the hour interval on my sql query

Question

So my date format on my page is showing the time 6 hours ahead. How can i change it so it shows the correct time this is my query

   $query = "SELECT firstName,lastName, post,date_format(postDate,'%M,%d,%Y at %H:%i') as     mydatefield
      FROM users INNER JOIN posts ON userId = empl_Id
      ORDER BY postDate";

This inserts values into table

   $query = "INSERT INTO posts(postId,empl_Id,post,postDate)
        VALUES('','$empId','$idea',NOW())";
    $result = mysqli_query($db, $query);

how can i do that? I just edited my post which shows the insert query — user2189151, Mar 26 '13 at 22:06

score 1 · Accepted Answer · answered Mar 26 '13 at 21:57

Presumably you're storing times in UTC and you're located 6 hours behind UTC.

Do not change your query. Timezone presentation is a concern of your application's View logic, not the controller.

Every time you display a date/time in your page just add the TZ offset to the UTC value. You can see examples here: http://www.php.net/manual/en/book.datetime.php

score 0 · Answer 2 · edited May 23 '17 at 12:04

As noted in this question, you can either do it as described here.

Or you might choose, as the answer below that states, do it this way :

you can use DateTime::setTimezone(). If your UTC date is an UNIX timestamp, you can use some code like this :

$date = new DateTime();
$date->setTimezone(new DateTimeZone('UTC'));
$date->setTimestamp(1297869844); // this should be the 'current' then 
$date->setTimezone(new DateTimeZone('Europe/Paris'));

echo $date->format('Y-m-d H:i:s');
// Will print 2011-02-16 16:24:04

And then use that to insert the values into your database. Changing the representation (after all its the unix timestamp, you might even want store that, which just gets translated to a human form as a way of speaking)

changing the hour interval on my sql query

2 Answers2