Storing a timestamp in MySQL

Question

If I generate a timestamp with PHP's time() and insert/update a row with it, what column type should I use?

I've used varchar(20) (64-bit) in the past, and I tried TIMESTAMP just then and it has the year 2038 error it seems.

Is it okay to just use varchar(20) to store a timestamp?

Is it ok.. that's pretty opinion based.. But storing it in only varchar 20 will get you in troubles in the future.. — Naruto, Apr 18 '16 at 09:10
Timestamp is an integer, so why wouldn't you store it as an integer in the DB? — fire, Apr 18 '16 at 09:13
unsigned int would work, otherwise a bigint. Both are smaller than a `VARCHAR(20)`. — Ronald Swets, Apr 18 '16 at 09:14
Please read this SO question. http://stackoverflow.com/questions/409286/should-i-use-field-datetime-or-timestamp — scottevans93, Apr 18 '16 at 09:15

score 0 · Accepted Answer · edited May 23 '17 at 12:31

use DATETIME datatype,

MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' .

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

you may also go through this,

PHP & mySQL: Year 2038 Bug: What is it? How to solve it?

score 0 · Answer 2 · answered Apr 18 '16 at 09:26

0

Use DATETIME as datatype and use date('Y-m-d H:i:s') with the time as optional parameter to insert into the column. See http://de.php.net/manual/en/function.date.php for more info on the date function.

answered Apr 18 '16 at 09:26

Splatbang

756
1
12
29

Storing a timestamp in MySQL

2 Answers2