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

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

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
.

Relevant Pages

  • Re: Please advise on approach
    ... has direct bearing on the issues and the context is difficult to communicate ... > of the term timestamp - it does not refer to the sql data type in this ... rather to the datetime at which the source produced the data - ... I'm only interested in it differentiating between records ...
    (microsoft.public.sqlserver.server)
  • Re: migration from mysql to oracle - problems with time data type
    ... The various tables include a mysql data types datetime, ... I can change the existing time fields to timestamp and ... mysql (or have become timestamps in Oracle). ...
    (comp.databases.oracle.misc)
  • Re: [PHP] checking user input of MM-DD-YYYY
    ... Because I'm inserting it into MySQL as a date conversion from American ... All the more reason I would turn it into a timestamp or DateTime ... If you enter it in that format MySQL will get it right without regard ...
    (php.general)
  • migration from mysql to oracle - problems with time data type
    ... The various tables include a mysql data types datetime, ... I can change the existing time fields to timestamp and ... mysql (or have become timestamps in Oracle). ...
    (comp.databases.oracle.misc)
  • Re: Want search on timestamp ! Any other alternative ??
    ... > It is BETTER to store timestamps as INTs rather than DATETIME. ... forget the exact date) which MySQL can handle but a Unix timestamp can't? ... Kailash Nadh | http://kailashnadh.name ...
    (comp.lang.php)