how to get my local time from mysql table

Question

Column date is timestamp - default - current_timestamp

example value: 2017-04-07 09:45:53

$stmt = $db->query("select * from cinema order by date desc");
    while($row = $stmt->fetch()){
        $date = strtotime($row['date']);
        $datea = date("d-m-y", $date);
        $time = date("H:i", $date);

I'm getting -2 hours difference comparing to my timezone - 09:45 instead of 11:45 - so I tried:

$stmt = $db->query("SELECT *, CONVERT_TZ(date, '+00:00', '+01:00') FROM cinema order by date desc"); - without success

I also tried to change column type from timestamp (current_timestamp) to datetime (current_timestamp).

And still getting -2 hours difference.

Any help?

Take a look at: http://stackoverflow.com/questions/2934258/how-do-i-get-the-current-time-zone-of-mysql — Chin Leung, Apr 07 '17 at 13:14

score 1 · Accepted Answer · edited May 23 '17 at 11:46

1

You can try something like:

$stmt = $db->query("SELECT *, CONVERT_TZ(date, @@session.time_zone, '+02:00') AS mydate FROM cinema order by mydate desc");

Here are some reading that might be helpful when working with date and time: https://stackoverflow.com/a/19075291/1363190

edited May 23 '17 at 11:46

Community

1
1

answered Apr 07 '17 at 13:20

Michael Krikorev

2,126
1
18
25

how to get my local time from mysql table

1 Answers1