Ever since I started using PHP, if I ever needed to store a timestamp I always stored it as an INT. Only until recently have I switched over to using the DATETIME and TIMESTAMP data types in MySQL exclusively for any dates I needed. These fields are in the format of YYYY-MM-DD HH:MM:SS. When I first start using these field types, I didn’t know how to convert back and forth to a UNIX timestamp for use in PHP so I made a bunch of functions in PHP to convert between the two. That was until I found out about the MySQL date and time functions.
So if I want to get some records before a certain date, I can just do this:
SELECT * FROM table WHERE date_field <= FROM_UNIXTIME($php_timestamp)
FROM_UNIXTIME() will convert a UNIX timestamp to a MySQL date.
Now if you need your MySQL dates converted to UNIX timestamps when you retrieve your records in PHP:
SELECT UNIX_TIMESTAMP(date_field) as unixtime FROM table
UNIX_TIMESTAMP() will convert a MySQL date to a UNIX timestamp.
If your entering a new record into your table and need to enter the current date where we would normally use time() or mktime() in PHP for a UNIX timestamp, we can just use the NOW() function in MySQL:
INSERT INTO table (date_field) VALUES (NOW())
NOW() will insert the current date and time into your record.
Now a small modification from my first example. Say we need to get all records in the last 30 days. MySQL makes this very easy.
SELECT * FROM table WHERE date_field >= DATE_SUB(NOW() INTERVAL 30 DAY)
DATE_SUB() subtracts time from a given date. I gave it NOW() which is the current date and my interval is 30 days so it will give a MySQL date 30 days ago. DATE_ADD() will add to a date. The interval can be DAY, YEAR, MONTH, etc.
So switch your INT timestamps to DATETIME or TIMESTAMP fields in MySQL. It makes your queries a lot easier.
Note: If you need to represent dates beyond the range of a UNIX timestamp (1970-2038) use DATETIME instead of TIMESTAMP as it can represent any date. If you don't need that range, use TIMESTAMP as it's half the size of DATETIME.
See the MySQL manual for a full list of date and time functions.
[carousel keywords="mysql, php" category="Books" tag="damnsemi01-20" showBorder="True"]