I like the 70's, but that's just not right (PHP Time question)

Question

So I'm storing a hidden-field time() call with each entry to my DB. The only problem is, they all come out as:

16777215

which is from 1970. I can't tell why that number, because it's not the beginning or end of the current timestamp, and it's the same for every entry. It's going into a mediumint(50) MySQL column, and the field looks as so:

    <input type="hidden" name="time" value="<?php echo time(); ?>">

Is it a casting problem? I bet it is. Sunnuva Gun. Let me go check that...

So I changed the column to "Text" and it get's entered accurately. So if I want this to be a number to do math with it, do I need to use php to convert it to an int before I save it? And for just curiosity, why did it choose that number before?

Answer 1

16777215 is the max number for a mediumint. You need to make it int or Bigint. See the MySQL Manual

Answer 2

Why not use a data type designed for storing dates?

Answer 3

If you are storing dates, I highly suggest using the built in Date types in MySQL. Why? You can do date validation (MySQL will not allow you to enter a Month 13, for example), generate manual queries much easier, and when looking at raw data you know what the date is. MySQL will also allow you to use things like CURRENT_TIMESTAMP() on columns that you need to record the time when a row changes.

Storing dates as integers does make it much easier to do simple math but I think that is about where the advantage ends in code. PHP has a strong Date/Time library you can use for dealing with dates. If you want to look at the amount of time between two dates, you can use Date::diff. You can add and subtract time from dates as well. (Yes, there are functions to correspond to the objects if you decide to use functions instead of objects.)

Manual entry for Date/Time Objects