CODEWITHSUNDEEP
phpmysqldatabasetime
stacker
stacker

Reputation: 109

Proper way to save a 1970 type date

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)

Upvotes: 3

Views: 253

Answers (5)

Martin Bean
Martin Bean

Reputation: 39409

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.

Upvotes: 2

Daniel Vassallo
Daniel Vassallo

Reputation: 344451

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)

Upvotes: 5

Patrick
Patrick

Reputation: 170

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...";

Upvotes: 3

dockeryZ
dockeryZ

Reputation: 3981

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.

Upvotes: 4

Jim
Jim

Reputation: 18863

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.

Upvotes: 9

Related Questions