Posted by MB on 11/23/07 09:07

>> I have millions of records in my xxxxx table in mysql. And I have a
>> column of time in which I have stored the timestamp using php time()
>> function.
>
> If this is your own database, you should convert that column to a
> DATETIME type. This would allow to use MySQL's own date and time
> functions for all different kinds of date calculations.
>

Why? Is there any performance issues (database) involved making DATETIME
a better choice? I am using the same methos as the original poster and I
have been thinking about possible performance issues using different
methods.

Let's say I use an Unsigned Int for the field "timestamp" in my
database. Then my query would look something like this:

"SELECT Stuff FROM SomeTable WHERE `timestamp` BETWEEN $timestamp1 AND
$timestamp2"

Wouldn't this query be faster than if I had used DATETIME? To me, an
Unsigned Int sound easier to process than a DATETIME.

I don't see that using mktime to create the needed timestamps when
building the database querys should be a problem. At least, I don't have
a problem with it. But which method is the easiest for the database to
handle? That's what matters most to me.

>> Now I wanna write an SQL query to fetch the records either for year
>> (2006) or for month and year (Jan 2006)
>
> Pretty easy with a correct MySQL date ...
>
>> Currently I had implement this logic:
>> To find records for March 2006
>>
>> start time = mktime( for Feb 2006 )
>> end time = mktime( for April 2006 )
>
> SELECT ...
> FROM ...
> WHERE MONTH(yourDateColumn) = 3
> AND YEAR(yourDateColumn) = 2006
>
> Of course you can do the same with your current Unix timestamps and the
> FROM_UNIXTIME() function, but this is rather ugly.
>
> Micha

[Back to original message]