HowTo: Manage Timestamps in a custom PHP/MySQL blog system

After my rant the other day about the state of Taiwanese politics, I thought I would lighten the mood with a technical article on how this website now does timestamp management, something which may be useful should you be looking to build a blogging system of your own, or any other sort of time indexed system.

What follows is an incrementally built system for timestamp management with the rationale behind each increment explained, and some coding hints for implementing it. I am aware that in the strictest sense, what follows may not be considered good database design however I have chosen to build it in this way since it allows me to reduce the complexity of the code and the number of queries needed to render a page.

1 – Indexing posts by time
This is the first step when implementing a time indexed system, and is implemented using a single database field which stores details of when the content was inserted. PHP/MySQL provides a very useful function called the Unix Timestamp which is the number of seconds which have elapsed since January 1st 1970 at midnight. This is stored in the database as an integer and can then be formatted into a massive number of possibilities using the PHP date() function.

To implement this, in your blog post table, which we will call blog_post, create an integer column called post_timestamp. When posting content through the MySQL management console, you can use the UNIX_TIMESTAMP built in function to populate the post_timestamp field with the current time on submission. When inserting a row using PHP, you can generate the current timestamp using the date() function. The implementation of this is date(“U”) where the U specifies you want a Unix Timestamp and the lack of a second parameter means that you want to use the current server time to generate it. To use it, you merely need to add a SORT BY post_timestamp statement to the end of the SQL command, and can add DESC if you want it sorted by newest to oldest.

2 – Adding in month and year groupings
This is the function that allows a user to see all posts made in say November 2011. While this could be accomplished through the use of the PHP date() function, this would not be the best way to accomplish it, since it would require retrieving all the timestamps from the database and then performing a fairly complex set of operations to build the list of month/year/post count tuples. Instead two additional fields are inserted into the database, one stores a numeric month value from 1 to 12 and the other stores a four digit year value. These can be queried through SQL using the count and group by functions to achieve the desired result easily.

To implement this, add to the blog_post table two additional integer fields called post_month and post_year. When inserting data, populate them with the values as described above. To retrieve them, the SQL command would be SELECT post_month, post_year, COUNT(post_id) FROM blog_post GROUP BY post_month, post_year ORDER BY post_month, post_year. This would return the aforementioned tuples of the form {[1,2011,4],[2,2011,6]…..} which can then be formatted to produce the listing to the right of this post.

3 – Edit Tracking
Edits in CMv6 are only tracked to a level that is visible to the user. That is if the post has been updated since it was first posted, then the date of the last update is shown to the user. This is done through the addition of two other fields to the blog_post table. The first is a boolean called post_modified, the second is an integer called post_mod_date. When a post is modified, the post_modified flag is set as 1, and the modified date is set as the timestamp of the modification. When retrieving the post, if the flag is set, then the modified date is formatted and displayed to the user.

4 – Sticky posts and bug fixes
One issue that arises from the current setup is that when a post is modified, it continues to be ranked by the original post timestamp as opposed to the modified timestamp. To avoid the need for extensive pre-processing of the result set produced by the SQL query, an additional field is added which allows for the results to be ordered at SQL query time.

To implement this, an integer field called post_effective_date is added to blog_post. This timestamp can be set arbitrarily since it is only used to order the results. In normal usage it is therefore set equal to the posting timestamp. Once the post is modified, it is set to the timestamp of the last modification. This gives rise to another feature I wanted to implement. By setting the effective timestamp in the future, a post can be set to remain at the top of the listing regardless of the number of posts made before the effective timestamp of the sticky post. After this point, it will be listed as a normal post.


Leave a Comment

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