CODEWITHSUNDEEP
phpmysql
Marcus Reed
Marcus Reed

Reputation: 91

mySQL Timezones Conversion

In my database, updateTime is set to TIMESTAMP and CURRENT_TIMESTAMP. But when I display the data, the time that was recorded in the database is not my local timezone but rather 3 hours later. How can I subtract those 3 hours by converting this SQL statement? Can I use CONVERT_TZ somehow inline with this statement? I don't know how to do it.

SELECT updates.updateID, updates.windowStatus, updates.onDeck, updates.updateComments,    TIME_FORMAT(`updateTime`,'%r') AS showtime FROM updates ORDER BY updates.updateID DESC LIMIT 1

Upvotes: 0

Views: 51

Answers (1)

Sebastian Neira
Sebastian Neira

Reputation: 564

The Server Time Zone can be set by each client (per-connection) in order to receive the data in other TZ than UTC. In order to do that, you just have to use:

SET time_zone = timezone;

The value of timezone can be given as an offset of UTC ('+10:00', '-6:00', ...) or as a named time zone (such as 'Europe/Helsinki', 'US/Eastern', or 'MET'). Therefore, you can set your own TZ in order to receive your data in '+3:00', if I'm not mistaken.

Take into account that this offset done by the mysql server only affects NOW(), CURTIME() and values stored in and retrieved from TIMESTAMP columns (which is what you're looking for).

You could otherwise use

SELECT @@global.time_zone, @@session.time_zone;

to get the global and client-specific timezones.

There's more relevant info (and this is actually a sum-up of what I wrote) at: Time-zone support (mysql.com).

In your case, you could have something like this:

<?php
mysql_select_db($database_casualconnnect, $casualconnnect); 

$set_tz_query = "SET time_zone = '+1:00'";
mysql_query($set_tz_query, $casualconnnect) or die(mysql_error()); 

$query_Recordset2 = "SELECT updates.updateID, updates.windowStatus, 
    TIME_FORMAT(`updateTime`,'%r') AS showtime, updates.onDeck, updates.updateComments 
    FROM updates ORDER BY updates.updateID DESC LIMIT 1"; 

$Recordset2 = mysql_query($query_Recordset2, $casualconnnect) or die(mysql_error()); 
$row_Recordset2 = mysql_fetch_assoc($Recordset2); $totalRows_Recordset2 = mysql_num_rows($Recordset2); 

?>

Upvotes: 1

Related Questions