Dates and Times and mySQL

I have come across applications where date/time fields from within the PHP are stored in INT fields in the database. Just why anyone would do this I don't know as it doesn't make the PHP any simpler and it makes it far more difficult to perform certain queries on the data. It also makes it much harder when examining the data to determine just exactly what the data represents.

It isn't even as if storing the date in an INT field makes the PHP any simpler. The PHP date can be easily converted into a string suitable to store in the database in a DATETIME field using a single statement and another single statement can easily turn it back into a PHP date after reading it from the database. Presumably an INT field is being used because the author doesn't know just how easy it is to use a DATETIME field instead (and so make it much easier to be able to run queries on that field).

So with simple statements allowing us to use DATETIME fields in our own code the only time we should see dates stored in INT fields is in code written by someone else.

Suppose that we are updating or replacing code written by someone else where INT fields have been used for dates and we want to write our replacement code to use DATETIME instead. The question then becomes - how do we convert the existing data so as to convert the dates held in INT fields?

Well we can't update in place as the definition of the field in the table needs to change. We will either need to add a new field to the table and convert the data from the old field into the new field, delete the old field and then rename the new one in order to keep the data in the same table or we will need to create a new table and load it using the data from the old table. Whichever of these you choose to do the changes can be done entirely using SQL calls.

The least obvious part of this process is how we go about converting the values from the INT field into values that can be loaded into the DATETIME field. We do not need any PHP code to do this. We don't need to set up PHP with a loop that runs a SELECT on the old table, converts the integer into a date and then into a string so that it can then be INSERTed into the new table. We can run a single INSERT on the new table that collects the data to be inserted directly from the old table using a SELECT. The general format of such a command is:

INSERT INTO newtable (field1, field2...) SELECT field1, field2... FROM oldtable

With regard to converting our date we need to do a little more than just retrieving the value from the field as that is not in a suitable format to load into the new field. We need to extract the value as a valid date string instead of as an integer. While it is not exactly obvious, the built in DATE_ADD() function built into SQL can do this for us. These integers represent the number of seconds from a specific point in time - usually midnight on 1st January 1970. If we add the intDate as a number of seconds to that start date using DATE_ADD then the result returned should be an appropriate date/time string suitable for inserting into the new table.

DATE_ADD('1970-01-01', INTERVAL intDate SECOND)

Note that if it turns out that the values you have are not based on 1970 but on some other starting point then it should be a simple matter of substituting that alternative start point into the first parameter of the DATE_ADD in order to perform the conversion.


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow