Re: Want search on timestamp ! Any other alternative ??

Kailash Nadh wrote:
On Nov 26, 4:05 am, Jerry Stuckle <jstuck...@xxxxxxxxxxxxx> wrote:
KailashNadhwrote:
On Nov 22, 12:38 am, kanwal <way2kan...@xxxxxxxxx> 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...@xxxxxxxxxxxxx
==================

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@xxxxxxxxxxxxx
==================

.

Relevant Pages