Posted by Rik Wasmus on 11/23/07 17:12

On Fri, 23 Nov 2007 10:07:49 +0100, MB <no@mail.com> 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

[Back to original message]