Working with dates and databases

At first glance, this may seem like a no brainer, but before you vote, please read this.

A few months back I had the fortune of working with a client that stored all his dates in a database as an int type, instead of a date type. All the data was a unix time (epoch time) so it actually worked quite will if you set up a few date() and mktime() calls in PHP. But I just thought it was a noob mistake, dealt with it, then moved on.

Now, I'm working on migrating an application I created using php and MS SQL server to a proper AMP environment (Apache, PHP and mySQL). Of course, my major problems lay around the DATEDIFF functions in SQL Server, and converting those queries and other queries using SQL SERVER only date functions to valid mysql date queries, then it hit me. If I had done all this with epoch time, this would have been a very simple migration.

So, what do you think? When storing dates in a database, do you use date or datetime types or int type with epoch time and handle the comparisons in your code?

I'd be interested to hear your responses.

Hmm, no responses to this. I would have thought there would be. :confused:

Usually I store the date in the database, but recently I've taken to storing the date as int, this means I can do things like SELECT * FROM table WHERE mydate < 1107827070 or something. It's really six of 1, half a dozen of another, really depends on what you want to do with the date.

I never understood why people convert dates to int, but thats probably because i'm to stupid or because these people have such extra-special requirements i never seem to encounter.

all db's store dates as integers. using values in dateformat is basically just using another (= a better) representationformat of the actually used value.

handling comparisons in your code? that's realy not efficient and not required at all. MySQL has a datediff() function so i don't see the problem. If you have an old version (pre 4.1.1), then you can just use a TO_DAYS() on the dates and subtract them.

The only real argument i find in your post is that it would have made the migration simpler. unless you are planning on constantly migrating from one db-format to another, this is not realy such a big advantage... and doesn't ms server allows you to specify the outputformat you need (yyyy-mm-dd)?