|
|
Posted by Jerry Stuckle on 11/26/07 14:08
Kailash Nadh wrote:
> 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.)
>
Are you sure? Do you know the internal representation of a timestamp?
Hint - date/time comparisons in MySQL are very fast.
> 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
>
Try reputable sites, like mysql.com. Or the mysql mailing lists, where
the mysql developers hang out. The blogs have more misinformation than not.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
[Back to original message]
|