mysqltimetimestamputcconvert-tz

Get unix timestamp from mysql database with offset applied

I have the offset in seconds to UTC, all timestamps stored on the database are in UTC, how to apply the offset during selection queries,

mysql_query("SELECT * FROM table WHERE unix_timestamp=unix_timestamp with offset applied");

mysql_query("INSERT INTO table (unix_timestamp) VALUES(UNIX_TIMESTAMP())");

I'd want to insert in UTC time to the database and retrieve in local time.

On PHP:

date_default_timezone_set('UTC');

$user_timezone_offset=-7200; // -2

Could I substract on select's runtime of mysql to the unix_timestamp field $user_timezone_offset and that would be it?

I am inserting in UTC which is what I need apparently, but for selection in user's local time I'd need to apply the offset to the timestamp stored in the database somehow.

Edit: I just came across this:

$user_timezone_offset="-2:00";

FROM_UNIXTIME(CONVERT_TZ(unix_timestamp,'+00:00','$user_timezone_offset'))

Would need to know how to apply it to a select query making the selection of unix_timestamp converted for comparison inside the select and the result also becoming modified for php's fetch_array

This seems to be the solution:

 SELECT *, Unix_Timestamp(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp), '+00:00', '$user_timezone_offset')) as unix_timestamp

Actually this has a problem - although on

 mysql_fetch_array($result){ $row['unix_timestamp']; }

comes converted while checking on

  WHERE unix_timestamp=value //assuming unix_timestamp

to be converted on the select - in there it is not converted and another 

  Unix_Timestamp(CONVERT_TZ(FROM_UNIXTIME(unix_timestamp), '+00:00', '$user_timezone_offset'))

has to be called for unix_timestamp to be "converted" each time a check is to be made against the converted value.

Solution

  • I think you may be overcomplicating the issue.

    Timestamps are always UTC. When you retrieve a timestamp in PHP, it is in UTC.

    When you convert a timezone using the date() function for example, it displays in whatever timezone the php.ini setting date.timezone is set to.

    Therefore, if you have users with specific timezones, just call date_default_timezone_set() with the appropriate timezone prior to displaying dates in local time.

    If you use the DateTime class, then you can specify the desired timezone when you construct the object.

    Unless you have a very specific reason, there should be no need to perform timezone conversions when you fetch timestamps from the database. PHP handles this all for you.