How to store UNIX timestamps with MySQL

Question

Some background:

My website depends heavily on coordinating users across all kinds of different time zones.

I am using Carbon to handle my time zone conversions and calculations on the server side and moment.js on the client.

As a result the design choice was made that all date times (e.g. the start time of an event) would be stored as unix timestamps.

Problem

I am a bit confused by the definition of a "timestamp". In PHPMyAdmin the timestamp is not a unix timestamp but rather a date format:

2016-10-06 20:50:46 

So if you want to have a default field of the current timestamp then you get the current date in GMT.

This makes things more complicated to convert back into a users timezone compared to a unix timestamp integer...

Question:

What field type should I store my unix timestamps as, currently I am using int(11) with a default of none. By extension... is there a way to store the current unix timestamp (e.g. 1475971200) by default in MySQL?

Answer 1

A Unix timestamp is a large integer (the number of seconds since 1970-01-01 00:00:00 UTC), so INT(11) is the correct datatype.

Unfortunately, I don't think there's any way to specify a default that will insert the current timestamp. You'll need to call UNIX_TIMESTAMP() explicitly when inserting, and use that. Function calls aren't allowed in DEFAULT specifications.