Ninedays Blog

AKA Why I Hate UNIX Timestamps Disguised as Integers

For a long time now I've been converting old code that programmers before me at work have brutalized. I'm not saying I am the cleanest/most efficient coder ever but at least I employ some amount of updated logic in my code.

Timestamps are great and all, as long as you have them stored in the MySQL database as a TIMESTAMP data type. The trouble I was running into is that the fields I was handling had the timestamp stored as INT data types. I'd much rather store dates as a DATE or DATETIME data type rather than TIMESTAMP data types because TIMESTAMP data types cannot store dates before 1970 or after 2038. Though it is nice that you can have a TIMESTAMP data type be set to always update when the record on the table is changed, I don't mind adding that to my code and being able to override that data easily if necessary.
By using any of the three date data types (DATE, DATETIME and TIMESTAMP) I can quickly and easily make updates directly in the database without using PHP or a calculator as an interface to calculate the date to a Unix time stamp.

Original Table Structure

Here's my original table structure, as a point of reference.

MySQL

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `fname` varchar(100) NOT NULL default '',
  `lname` varchar(100) NOT NULL default '',
  `dob` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

When I needed to pull users by their birthday, kind-of a "Look Whose Birthday" module, there is no easy query. The only thing I could do was add a new field to the table that used the DATETIME time stamp:

MySQL

ALTER TABLE `users` ADD `birthdate` DATETIME NOT NULL ;

Unfortunately there is no query to convert and update all 1000 users birthday (datetime data type) with the data from dob (Unix time stamp stored as INT) since the date is originally stored as an integer that the developers must understand and know is actually a Unix time stamp.

Here's the quick script I used to make the conversion.

PHP

<?php
    // Make MySQL connection

    $query1 = 'SELECT * FROM `users`';

    $result1 = mysql_query($query1,$sql);
    if(mysql_num_rows($result1) > 0){
        while ($row = mysql_fetch_assoc($result1)) {
            $birthdate = date('Y-m-d H:i:s',$row['dob']);
            $id = $row['id'];

            $query2 = "UPDATE `users` SET `birthdate` = '$birthdate' WHERE `id` = $id";
            mysql_query($query2,$sql);
        }
    }
?>

Grab those Birthday Girls and Boys

Now that the table has been updated I can grab people by their birthdays. I have two options for queries, which both accomplish the same thing.

MySQL

SELECT * FROM `users` WHERE MONTH(`birthdate`)=MONTH(NOW()) AND DAYOFMONTH(`birthdate`)=DAYOFMONTH(NOW());
# OR
SELECT * FROM `users` WHERE DATE_FORMAT(`birthdate`, '%m%d') = DATE_FORMAT(NOW(), '%m%d');

Ran a simple benchmark and the first query seems to have a faster success rate in a table of 1000 records, though I wouldn't mark it as monumentally faster. Run some tests on your table selecting the data you need to determine which method would work best for you.

Related Links

• Re: Select name, email where birthday = ?? - MySQL Lists - Post to MySQL lists, a response to a question about how to get the birthday girls and boys in a simple query.
• The DATETIME, DATE, and TIMESTAMP Types - MySQL Manual - References about the DATETIME, DATE and TIMESTAMP data types in the official MySQL manual.

Popularity: 39% [?]