28

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?

Jethro Hazelhurst
  • 3,230
  • 7
  • 38
  • 80
  • 3
    You can circumvent the problem by converting the datetime from and to unix timestamp. So to insert a unix timestamp, you would create a DATETIME field in your table, and convert the unix timestamp with `FROM_UNIXTIME(1514789942)`. Then when you want to retrieve the value back in unix timstamp you do `SELECT UNIX_TIMESTAMP(my_datetime_field)` – Jacques Jan 01 '18 at 07:01

3 Answers3

22

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Why is it 11 bytes? – User Oct 19 '18 at 16:19
  • 4
    It isn't 11 bytes, it's 11 decimal digits, which is how many digits a 32-bit number can show. – Barmar Oct 19 '18 at 20:29
  • 3
    INT(11) won't do the trick, just tried it, stores 2147483647 for 1623925731158. BIGINT does the trick. – DBencz Jun 17 '21 at 10:45
  • @DBencz `UNIX_TIMESTAMP()` is measured in seconds, and the MySQL timestamp functions only support values up to `2**31-1`, which fits in `INT(11)`. You seem to have added 3 digits (milliseconds). – Barmar Jun 17 '21 at 14:07
3

You can continue using an unsigned INT, but you'll have to manually set the timestamp on insert (UNIX_TIMESTAMP()).

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

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

Community
  • 1
  • 1
George Kagan
  • 5,913
  • 8
  • 46
  • 50
2

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

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

Luka Govedič
  • 399
  • 4
  • 14
  • 4
    Int(11) is enough, no need for bigint. unix timestamp can't go further than 19. january 2038 anyway because its usually a signed 32bit integer. see https://en.wikipedia.org/wiki/Year_2038_problem – Jack O'Neill Mar 20 '18 at 17:06
  • 1
    Unix timestamp is seconds not miliseconds. You wont need bigger than int(11). People get confused because javascript Date.now() returns miliseconds instead. you need to divide it by 1000 – T S Feb 12 '22 at 13:37