Posted by Kailash Nadh on 11/26/07 13:56

On Nov 26, 4:05 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> KailashNadhwrote:
> > On Nov 22, 12:38 am, kanwal <way2kan...@gmail.com> wrote:
> >> Hi,
>
> >> 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.
>
> >> Now I wanna write an SQL query to fetch the records either for year
> >> (2006) or for month and year (Jan 2006)
>
> >> Currently I had implement this logic:
> >> To find records for March 2006
>
> >> start time = mktime( for Feb 2006 )
> >> end time = mktime( for April 2006 )
>
> >> I am searching the records greater than start time and lesser than end
> >> time.
>
> >> Is there any other simple way to do so.
>
> >> Thanks.
>
> > Your method would be ideal. (mktime)
> > It is BETTER to store timestamps as INTs rather than DATETIME.
> > A simple, X(int) > INT AND X(int) < INT is definitely faster than a
> > comparison involving DATETIME fields.
> >
> > Regards,
> >KailashNadh
> >http://kailashnadh.name
> >
>
> (Top posting fixed)
>
> Are you sure? What's the internal storage mechanism for a DATETIME in
> MySQL? And what about dates before 12/31/1969 or after ?/? 2038 (I
> forget the exact date) which MySQL can handle but a Unix timestamp can't?
>
> Hint: datetime comparisons in MySQL are very fast.
>
> And please don't top post.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

Depends on the type of comparisons. If they are simple > = < type (in
this case), INT timestamps are the fastest option.
(The most recent case was getting a week old records from a table of 1
million rows, and timestamps proved to be much faster.)

There IS a drawback though, Timestamp doesn't support dates before the
epoch (1970..)
And finally, DATETIME uses 8 bytes in the db, while TIMESTAMP users 4.

I remember seeing an entry about this on http://www.mysqlperformanceblog.com,
but I can't think of any sources to cite at the moment.

Regards,
Kailash Nadh | http://kailashnadh.name

[Back to original message]