PHP - How to change datetime from UTC when pulling data from MYSQL into JSON?

Question

I am pulling some data from a MYSQL database in which I have a date-time that is stored in UTC, then converting everything into JSON. What I need to accomplish is that I want to show this datetime in UTC+3, not UTC.

This is how I am doing everything:

function getValues() {

    $sth = mysql_query($query);
    $rows = array();
    while($r = mysql_fetch_assoc($sth)) {
        $rows['announcements'][] = $r;
    }
    print json_encode($rows);

}

This is the output I get (example):

{
    "announcements":[
        {"id":"1","Title":"Title One","Details":"Detail of One","Submitter":"Alaa Alrufaie","DateSubmitted":"2016-03-06 17:03:48"},
        {"id":"7","Title":"Title Seven","Details":"Detail of Seven","Submitter":"Alaa Alrufaie","DateSubmitted":"2016-03-06 17:23:44"}
    ]
}

How can I output the time in UTC+3 while maintaining this same JSON structure?

Obviously by converting the datetime to a different time zone. Take a look at the date formatting functions php offers. — arkascha, Mar 06 '16 at 17:58
The answer for this question might be helpful: [PHP & MySQL: Converting Stored TIMESTAMP into User's Local Timezone](http://stackoverflow.com/q/10184192/4577762) — FirstOne, Mar 06 '16 at 17:58
If you know for certain that the data is in UTC you could just do `date( "Y-M-d H:i:s", strtotime( $r[DateSubmitted'] ) + 3 * 3600 );` — Devon Bernard, Mar 06 '16 at 17:59

score 0 · Accepted Answer · answered Mar 06 '16 at 18:22

You should make the change while reading the database result set:

while($r = mysql_fetch_assoc($sth)) {
    $r['DateSubmitted'] =
        date("Y-m-d H:i:s", strtotime($r['DateSubmitted'].'+3 hours'));
    $rows['announcements'][] = $r;
}

I would however add the time zone information to the date as well, so the consumer of the JSON data knows it is UTC+3. For this you should use the ISO 8601 format, which has a T between date and time, and the additional time zone shift appended as +hh:mm (the colon is optional):

    $r['DateSubmitted'] = 
        date("Y-m-d\TH:i:s+03:00", strtotime($r['DateSubmitted'].'+3 hours'));

Thanks a lot, this is what I was looking for. – Alaa Salah Mar 19 '16 at 15:11 — Alaa Salah, Mar 19 '16 at 15:11

PHP - How to change datetime from UTC when pulling data from MYSQL into JSON?

1 Answers1