MySQL, PHP and Unix time

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.

PHP-side Manipulation

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 strtotime($mysqlts) and 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.

MySQL-side Manipulation

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 UNIX_TIMESTAMP() and FROM_UNIXTIME(). Here’s two example queries that make use of them

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.

Leave a comment

Your email address will not be published. Required fields are marked *