CODEWITHSUNDEEP
phpmysqldatedatetimeunix
Jethro Hazelhurst
Jethro Hazelhurst

Reputation: 3285

How to store UNIX timestamps with MySQL

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?

Upvotes: 31

Views: 43797

Answers (3)

Luka Govedič
Luka Govedič

Reputation: 524

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()

Upvotes: 4

Barmar
Barmar

Reputation: 780673

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.

Upvotes: 22

George Kagan
George Kagan

Reputation: 6124

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?

Upvotes: 3

Related Questions