How do I get UTC times and unixtimes to match in MYSQL server?

I have two timestamps in different formats.

One is a UTC string that looks like this: "2014-09-19T20:55:51Z"

The other is a unix timestamp that looks like this: 1411159809

These two timestamps happen to be five minutes apart. As I verified with this Epoch Converter tool.

I want to put both of these time stamps (along with the data that they describe) into my database, but when I do they don't agree.

The table I'm using looks like this:

+--------------+--------------+------+-----+---------------------+-----------------------------+
| Field        | Type         | Null | Key | Default             | Extra                       |
+--------------+--------------+------+-----+---------------------+-----------------------------+
| id           | int(11)      | NO   | PRI | NULL                | auto_increment              |
| property_key | int(11)      | YES  |     | NULL                |                             |
| updated_at   | timestamp    | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| created_at   | timestamp    | NO   |     | 0000-00-00 00:00:00 |                             |
| value        | varchar(128) | YES  |     | NULL                |                             |
| name         | varchar(128) | YES  |     | NULL                |                             |
+--------------+--------------+------+-----+---------------------+-----------------------------+

As queries, I have:

insert into events (value,property_key,created_at) values ('datapoints/56892923',273827,'2014-09-19T20:55:51Z')

and

insert into events (value,name,created_at) values ('240','HeartRate',FROM_UNIXTIME(1411159809))

When I pull these entries back out of the database, this is what I get:

*************************** 1. row ***************************
          id: 1
property_key: 273827
  updated_at: 2014-09-19 17:42:44
  created_at: 2014-09-19 20:55:51
       value: datapoints/56892923
        name: NULL
*************************** 2. row ***************************
          id: 2
property_key: NULL
  updated_at: 2014-09-19 17:43:09
  created_at: 2014-09-19 14:50:09
       value: 240
        name: HeartRate
2 rows in set (0.00 sec)

updated_at becomes the timestamp for the creation of the row, as expected. What bothers me is that created_at is six hours different. I'm guessing MYSQL is interpreting the UTC as local time, but that's not what I want. How do I get these timestamps to behave the way I want?

Solution

Reading the manual for FROM_UNIXTIME():

Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

Also for the definition of the TIMESTAMP data type:

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.

So, if you're using TIMESTAMP columns you should be OK, all you have to do is use TZ_CONVERT to ensure the timestamp gets formatted as UTC.