October 6, 2009Why Is 86400 A Magic Number?
Anyone who has worked with database date/time fields probably recognizes the number from the title of this blog post. If not, it’s simple: there are 86400 seconds in a day. Why do I care about this? Because there are all sorts of fun things that I can do with that number. 
What Happened Yesterday?
One of the frequent requests that I used to see on phpbb.com was something like this:
How many visitors came to my board yesterday?
The problem I have with questions like this is that your “yesterday” is not the same as mine, unless you happen to live in the central time zone in the United States. When I wrote a MOD to do this for a client, I convinced them that rather than showing what happened “yesterday” it would be better to show what happened in the last 24 hours.
The user_lastvisit field shows the date/time that a user last logged in. This field is used to track new topics during a user session. It’s also used to drive the difference between “new” and “unread” personal messages. (A “new” message arrived since the last session. An “unread” message is one that hasn’t been read yet but arrived before the current session started.) I have altered my memberlist.php code to show when the user last visited as well.
Like most date fields in phpbb, this field is stored as int(11) rather than as a date/time field. (Other examples are the user’s registration date, the post time, new topic time. … the list goes on from there.) The content of the field is a very large integer value and is officially known as a unix timestamp.
Unix time, or POSIX time, is a system for describing points in time, defined as the number of seconds elapsed since midnight proleptic Coordinated Universal Time (UTC) of January 1, 1970, not counting leap seconds.
The standard for storing date/time fields in unix timestamp is to use a signed integer rather than unsigned. This allows a developer to store negative numbers to reflect dates prior to 1970. It also has its own Y2K issue as the int(11) field will overflow in 2038. But let me get back on track for this blog post.
SQL Code for Last 24 Hours
Because of the way the user last visit time is stored, I can easily get a list of people that have visited my board in the last 24 hours with this SQL code:
select user_id , username from phpbb_users where user_lastvisit >= (unix_timestamp() - 86400) order by user_lastvisit desc
The MySQL function unix_timestamp() returns the current date and time in a unix timestamp format so I don’t have to convert anything. Since the unix timestamp is a number of seconds, and since one day has 86400 seconds, by subtracting 86400 from the current time I get the matching time from 24 hours ago. Easy stuff.
If I wanted to truly get a list of people that signed in “yesterday” then the first thing I have to do is define what yesterday means. Time zones could get involved. It could get messy. I much prefer the “last 24 hours” definition because it’s the same for everybody everywhere.
What About More Than One Day?
Sometimes I want to calculate more than one day. Instead of memorizing multiples of 86400 I simply multiply by the number of days. So if I want to count how many people have logged in for the past 7 days (as defined by 24-hour periods rather than “days”) I would do this:
select count(user_id) from phpbb_users where user_lastvisit >= (unix_timestamp() - ( 86400 * 7 ) )
This is easy enough to do, and the code becomes “self-documenting” in a manner of speaking. I know that there are 86400 seconds in a day, and if I multiply by 7 I get a week. This is much easier to read and understand than using the number 604800.
Measuring Board Activity
About two years ago I told folks I was eagerly looking forward to the first week where my board averaged 86400 page views daily. Now that I have explained what the number is, that statement makes more sense. I was looking for the first week that I averaged a page view every second for an entire week. That happened over a year ago, and in fact my board averages over 100K page views daily at this point.
Now I am looking forward to the first week that I average 172800 page views a day. Hmm, I wonder why that is? 
Related Links
- Wiki on Unix Timestamps
- Wiki on UTC
- Unix Millenium Bug
Comments (1)
If you’re using MySQL, then it’s better to use MySQL native time and date fields. You can do more calculations than you can with just storing the Unix timestamp.
When I first learned PHP and MySQL, I did it by using phpBB, which uses integers for cross-db compatibility. Now that I also use other open-source programs, I am beginning to find that that is rare. Most of these other packages are designed to support MySQL, so they use MySQL’s data type for date/time.
Comment by Dog Cow — October 6, 2009 @ 5:38 pm