Want search on timestamp ! Any other alternative ??

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.

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

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

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

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

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

On Fri, 23 Nov 2007 10:07:49 +0100, MB wrote:

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

Yes: you can make better use of native (fast) database functions to
alter/compare/fetch output the way you like, instead of having to jump
through hoops to get it OK.

Also, the range is higher, DST is easier to handle, input/output control
is easier etc.

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

You can do exactly the same query using datetime fields, and it is a
native database format, so I suspect difference in performace here is
negligable (haven't tested it though, it's more a question for the
database experts, not PHP).

For this particular query, datatime would probably not be any faster
either. The datetime pays of in grouping/selecting/comparing different
time periods like hours/days/months/years.
>
> 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.

It's usually more effective to let the database handle it itself.

> But which method is the easiest for the database to handle? That's what
> matters most to me.

If you're doing more then just fetching a range of from..to.. timestamps,
datetime is a lot easier on the database. Even if it's all you do now,
think ahead: will there be a time when you want more details from that
database (statistical analyses, grouping of records, reports etc.)? And it
will happily convert it's output back to a timestamp for you if you have
to use that in your code.
--
Rik Wasmus

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

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,
Kailash Nadh
http://kailashnadh.name

On Nov 22, 12:38 am, kanwal 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.

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

Kailash Nadh wrote:
> On Nov 22, 12:38 am, kanwal 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,
> Kailash Nadh
> 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.
jstucklex@attglobal.net
==================

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

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

That depends entirely on how you use it. If you have to convert a
timestamp to display it often, you may lose that speed advantage.

Also, many uses for timestamp information can't use a Unix timestamp.
Even my birthdate is too old, so forget using it for genealogy.
And the due date for a new 30-year mortgage is going to go out of range
pretty soon.

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

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

On Nov 26, 4:05 am, Jerry Stuckle wrote:
> KailashNadhwrote:
> > On Nov 22, 12:38 am, kanwal 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

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

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