CODEWITHSUNDEEP
mysqlunix-timestamp
Shackrock
Shackrock

Reputation: 4701

unix timestamp in php/mysql installation - best MySQL datatype to store this as?

I'm trying to figure out the best datatype and size in my DB to store unix timestamps...

In otherwords:

INT(32)

etc...

Thanks for any tips.

Upvotes: 3

Views: 2790

Answers (2)

Matthew Flaschen
Matthew Flaschen

Reputation: 285077

TIMESTAMP is a pretty straight-forward choice. It's implemented as a UNIX timestamp internally, but externally it appears as a date string (e.g. "1970-01-01 00:00:01").

EDIT: To migrate an INT to a timestamp, where time_test is the table and ts is the original column:

ALTER TABLE time_test ADD ts_new TIMESTAMP;
UPDATE time_test SET ts_new = FROM_UNIXTIME(ts);
ALTER TABLE time_test DROP ts;
ALTER TABLE time_test CHANGE COLUMN ts_new ts TIMESTAMP;

You may have to tweak it slightly if you care about the column order.

Upvotes: 5

mpez0
mpez0

Reputation: 2883

MySql supports Unix timestamps directly as the TIMESTAMP data type.

You have the normal limitations; dates must be from 1 Jan 1970 until 19 Jan 2038.

Upvotes: 2

Related Questions