When working in PHP, the most convenient way of dealing with timestamps is through the
time() function, which returns an integer representing the number of seconds from the UNIX epoch (1 Jan 1970 at 00:00 GMT). That’s perfect for passing around your code modules, calculating time differences and easy conversion to string with the
date() function. MySQL though handles the same data type differently – if storing or retrieving timestamps in a MySQL table, there are a couple of options available.
First of all, date, time and date time values in MySQL are represented as strings with a specific format, namely (in its fullest form)
'YYYY-MM-DD HH:MM:SS'. A wide range of functions allow you to manipulate such special string values in SQL. Option number one, therefore, is to store timestamp data in the database using the native type and then convert it to/from a unix time value in your PHP code with
date('Y-m-d H:i:s', $phpts) respectively. If you’re inheriting a third-party database and cannot write your own SQL queries against it, you’ll probably have to go down this route.
A second option is to use two MySQL functions that convert between native date/time values and Unix timestamps at the database level. They are
FROM_UNIXTIME(). Here’s two example queries that make use of them
$sqlSelect = "SELECT recid, UNIX_TIMESTAMP(ts) AS ts, newstext FROM site_news";
$sqlInsert = "INSERT INTO site_news (ts, newstext) VALUES(FROM_UNIXTIME($phpts), $text)";
The advantage of this technique is that you maintain the structure of existing tables intact, you keep dates and times stored in the internal format used by MySQL (so you can manipulate them more easily in stored procedures and SQL functions), but at the same time don’t have to clutter your PHP code with clumsy conversion statements. This is the way to go if you’re given an unmodifiable database structure, but can run your queries on it.
Dates as Integers
Last but not least, when you design your tables from scratch, you can opt to create your timestamp fields as
INTEGERs. This way you will be able to insert and retrieve the exact values returned by
time() and expected by
date(), keeping your PHP code as simple and straightforward as possible. If there is a downside to this third option, it’s that the fields become more awkward to manipulate with date/time functions in SQL, but if your database is merely used for storage and contains no data munching logic, this shouldn’t be an issue.