Using MySQL date functions to save time in PHP

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"]

Continue Reading

  • Jon

    Great post. Just saved me a ton of time.

  • Stefan

    Me too!

  • You have to express more your opinion to attract more readers, because just a video or plain text without any personal approach is not that valuable. But it is just form my point of view

  • fortnox007

    I disagree with Zell corp, This A nice to the point approach! Awesome website btw, really like the look and feel

  • Skye

    Thanks fortnox007. I agree in both cases but still like the straight forward approach as coding for the most part is cut and dry. Other sites that I read, I want to see the code, see how they use and go off and use it myself. But I’m still new to blogging so I’m still trying to figure out my posting style. This post was really straight forward so I don’t feel the need for it to be personable. If you want my opinion though, use this method over UNIX timestamps as MySQL is optimized for TIMESTAMP and DATETIME =)