Proper way to save a 1970 type date

Question

I have a date that looks like 1003029303, which I guess is what's known as a linux UNIX time stamp.

What format should I save it as in a mysql database? I don't suppose that an int(10) is the right way.

`gottime` int(10)

Answer 1

I take it you are trying to save Birthdays or something similar. DATETIME format is the proper way to store past dates. A unix timestamp should only be used for current day items that do not exceed 30-40 +/- years.

The MySQL DATETIME format is YYYY-MM-DD HH:MM:SS.

Answer 2

On MySQL, I store such timestamps in INT(11) fields and they work fine.

Answer 3

You shouldn't store UNIX timestamps in your database. Mainly because MySQL (amongst other brands) databases includes DATE functions to calculate most any type of DATE math you can think of. Whereas if you store it as a timestamp, you would have to either, convert the timestamp within mysql first and then format it OR use PHP's date() function.

Answer 4

Timestamps are simply integers. You could store it like that.

You could also use MySQL's TIMESTAMP data type with the FROM_UNIXTIME() and UNIX_TIMESTAMP() functions to convert to and from MySQL's format.

$query = "UPDATE table SET
    datetimefield = FROM_UNIXTIME($phpdate)
    WHERE...";

$query = "SELECT UNIX_TIMESTAMP(datetimefield)
    FROM table WHERE...";

Answer 5

I suggest using the DATETIME data type, and then use the FROM_UNIXTIME() and UNIX_TIMESTAMP() functions to convert as required:

SELECT FROM_UNIXTIME(1003029303);
+---------------------------+
| FROM_UNIXTIME(1003029303) |
+---------------------------+
| 2001-10-14 05:15:03       |
+---------------------------+
1 row in set (0.08 sec)


SELECT UNIX_TIMESTAMP('2001-10-14 05:15:03');
+---------------------------------------+
| UNIX_TIMESTAMP('2001-10-14 05:15:03') |
+---------------------------------------+
|                            1003029303 |
+---------------------------------------+
1 row in set (0.00 sec)

Answer 6

Personally, I store dates in MySQL databases using the DATETIME field type. For example, 2010-07-27 09:30:09.

It's far easier to read than a time stamp, and if you need to convert it to a UNIX timestamp you can do so with PHP's strtotime() function.