How to store UNIX timestamps using MySQL

Question

A bit of background:

My site is highly dependent on user coordination across all different time zones.

I use Carbon to process my time zones and server-side and moment.js calculations on the client.

As a result, a design choice was made so that all time dates (for example, the time the event started) were saved as unix timestamps.

Problem

I am a little confused by the definition of "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 the default field of the current timestamp, you get the current date in GMT.

This complicates the conversion back to the user's timezone compared to the integer timestamp unix ...

Question:

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

Answer 1

The Unix timestamp is a large integer (the number of seconds from 1970-01-01 00:00:00 UTC), so INT(11) is the correct data type.

Unfortunately, I don't think there is a way to specify a default value that will insert the current timestamp. You will need to call UNIX_TIMESTAMP() explicitly on insertion and use this. Function calls are not allowed in the DEFAULT specifications.

Answer 2

You can continue to use unsigned INT, but you will have to manually set the timestamp for the insert ( UNIX_TIMESTAMP() ).

Or you can use the TIMESTAMP type with CURRENT_TIMESTAMP by default (which is stored as int behind the scenes) and convert it to int when selected:
SELECT UNIX_TIMESTAMP(foo_field) FROM foo_table

Reference - Is it possible to create a column with UNIX_TIMESTAMP by default in MySQL?

Answer 3

Actually, you should use either bigint or varchar , because the maximum for int(11) is 2'147'483'647 (more details here ).

Then, as the previous answers say, you need to manually insert UNIX_TIMESTAMP()