Posted by Michael Fesser on 11/22/07 01:15

..oO(kanwal)

>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.

>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]