CODEWITHSUNDEEP
phpmysql
msingh
msingh

Reputation: 7

Change time zone for whole column of table

I have a table 'temp' with Temperature and Time as two column. When I feed Temperature then Time is automatically gets printed with 'timestamp' and default 'CURRENT_TIMESTAMP'. I am showing in graph the output with PHP code. Now I am getting Time in UTC zone where I require it in IST.

$query = "SELECT * FROM temp;
while($row = mysqli_fetch_array($result))
{
$chart_data .= "{ Time:'".$row["Time"]."', 
Temperature:".$row["Temperature"]."}, ";
}

Pls guide how to change timestamp of UTC to IST for complete column 'Time'

Thanks.

Upvotes: 1

Views: 333

Answers (2)

nbirla
nbirla

Reputation: 610

Try using date_timezone_set for $row["Time"] while saving in $chart_data

date_timezone_set($date, timezone_open('America/Los_Angeles'));
echo date_format($date, 'Y-m-d H:i:sP');

You can specify the time zone you want in date_timezone_set.

Or use the more popular OOP interface:

// The reference timezone
$date = new DateTime($row['Time'], new DateTimeZone('UTC'));

// Changing the timezone
$date->setTimezone(new DateTimezone('America/Los_Angeles'));

// Output
echo $date->format($date, 'Y-m-d H:i:sP');

Upvotes: 1

Evert
Evert

Reputation: 99697

Alternative... TIMESTAMP fields are already timezone aware.

You could simply tell mysql to convert the times for you:

SET time_zone = 'Asia/Kolkata';

If you run this before your query, you should also get the right time. I'm adding this as an option, but I would actually not recommend it. It's better to keep your MySQL connection in UTC and let PHP handle the conversion with DateTime and DateTimezone

Upvotes: 1

Related Questions