storing Dates & Timestamps

As with most everything that’s out there, there are two camps, Database Date storage is no different. There are many sub topics forming a spectrum of options, but there are ultimately only two options: Store the date as an integer/decimal timestamp or as a date format. I happen to subscribe to the integer camp, and will explain why in just a short bit, but first I would like to make an argument for the later.

When you are working in a business environment, dealing with millions of records (or even hundreds), one of the most important things for a database is data integrity. Storing your date fields in a format that is human readable and valid is only one example of this in practice. It is much easier to validate a date like this, 2007-02-31 00:00:18 GMT, to false than it is to do the same to this, 1172898198 (There are quite a few reasons why this is not necessary, which I will get into later =) ). Recently I took part in a company wide conversion of applications through DST, and let me tell you – The reasons of data integrity are definitely valid. I just don’t think they are as important as many make it to be.

For examples purpose let’s set up a situation. Imagine a website, such as gneu, where you would want to keep track of a user’s last login time, and to spice it up we will talk about this over a fall DST change. Now, for those of you who don’t know, the fall change lets you get an extra hour added to your day. In essence, since It’s done at 2am you are able to experience 1am twice. Is your spider sense tingling yet? Well shit, how about this for a timeline: User logs in at 1:50am, navigates around until 1:59:59, and then clicks another link.

Depending on which method you are using to store your date, you may find some quirky situations. I said above that there are many options for either camp, but we are going to stick to what I have seen over the years. One company would basically show that our user logged in after they played on the site for a while, logged in. This is the classic chicken or the egg scenario, transposed on the web.

Another thing that came up with this change that I took part in is date formatting. The concept of date being in multiple formats doesn’t occur to anyone until they need it to be in multiple formats. For example, my blog shows the date in the format Wed, 04/18/2007 – 17:24, and I have the option to change that at will, to other formats as I see fit. One application I work on here has two different formats: UNIX date, which is similar to the UNIX date timestamp format but not quite, and a “Pretty” date, having some of the values swapped out to simplify output. The database stores these entries in the database in the format of the UNIX date, and thusly requires string manipulation in order to clean the data up for the “pretty” format. I took the route of doing some benchmarks at one point in order to check the performance at one point, in order to compare the method I have been using over these years to this and I was surprised to find that the time was considerably different, although much of it had to do with the route that the application was using to handle the manipulation. Even when I didn’t include database query time and just used regex or str_replace or explode or anything, nothing matched the performance of passing the integer timestamp into the date() function and letting it do the dirty work.

This all being said, here is the most interesting reasons why storing your dates as an integer is my choice. Although not limited to PHP, PHP has an arsenal of functions at your disposal. One that I find absolutely irreplaceable when dealing with date validation is the mktime() function, which takes integer values of the various parts of the day, month, year, hour, minute, second (and a DST flag ), and will output the integer timestamp of the input. For example:

<? php

    print mktime(0, 0, 18, 2, 31, 20076);

?>

This is how I got the integer value of the date above. From what I have seen, incorrect date input is often trivial. Some people forget that February often only has 27 days, and they forget what day it is but remember what day it was three days ago so they add three. Well, I hope you are all happy to find that Your computers are smarter than you thought. 2/31/2007 will miraculously turn into a valid day:

<?php

    print date("Y-m-d h:i:s", mktime(0, 3, 18, 2, 31, 2007));
    # 2007-03-03 12:03:18

?>

Please don’t get me wrong. I still validate dates; I just prefer to do it with JS popups to force people to select valid dates to allowing a free text field to be selected from or a series of drop downs. When DST creeps up on a server and 1:59:59 arrives I don’t get crazy or scared because my integer timestamp does not roll back an hour, it continues to progress as it did the previous second.

As usual, I don’t intend to recruit anyone to this camp, I suggest you find the applications for what your data needs. I use the date timestamp as well. I strictly use integer timestamps when I know time needs detail down to the hour or second, which it often does.

Leave a Reply