473,327 Members | 2,118 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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.
Nov 22 '07 #1
8 2047
..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
Nov 22 '07 #2
MB
>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
Nov 23 '07 #3
On Fri, 23 Nov 2007 10:07:49 +0100, MB <no@mail.comwrote:
>>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
Nov 23 '07 #4
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 <way2kan...@gmail.comwrote:
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.
Nov 26 '07 #5
Kailash Nadh wrote:
On Nov 22, 12:38 am, kanwal <way2kan...@gmail.comwrote:
>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.
js*******@attglobal.net
==================

Nov 26 '07 #6
>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.
Nov 26 '07 #7
On Nov 26, 4:05 am, Jerry Stuckle <jstuck...@attglobal.netwrote:
KailashNadhwrote:
On Nov 22, 12:38 am, kanwal <way2kan...@gmail.comwrote:
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
Nov 26 '07 #8
Kailash Nadh wrote:
On Nov 26, 4:05 am, Jerry Stuckle <jstuck...@attglobal.netwrote:
>KailashNadhwrote:
>>On Nov 22, 12:38 am, kanwal <way2kan...@gmail.comwrote:
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.
js*******@attglobal.net
==================

Nov 26 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Torfi Sackbatten | last post by:
Hi Everyone, I´m asked to "speed up" a keyword search based on MySQL. The material i´m given to work with is a quite large MySQL table with 1.5 mio rows, defined something like: CREATE TABLE...
2
by: Astra | last post by:
Hi All I know an SQL Server timestamp seems to be as useful as rocking horse for show jumping, but I'm hoping you know a 'fudge' to get me round a problem or at least confirm that it isn't...
15
by: SK | last post by:
Hey folks, I am searching for a string (say "ABC") backwards in a file. First I seek to the end. Then I try to make a check like - do { file.clear (); file.get(c); file.seekg(-2,...
3
by: Robin Tucker | last post by:
Hi, I'm in the process of implementing a multi-user system containing an adjacency list (tree structure). I'm using a TIMESTAMP field on each record in the adjacency list in order to tell when...
3
by: Andreas | last post by:
Hello list, I suspect, this is a common issue for newbies. Is there a simple way to have an auto-updating timestamp like mysql has ? create table something ( id int4, sometext...
32
by: tshad | last post by:
Can you do a search for more that one string in another string? Something like: someString.IndexOf("something1","something2","something3",0) or would you have to do something like: if...
7
by: mybappy | last post by:
Hi: I am trying to use timestamp field of SQL Server to maintain concurrency. My problem is how do I store the timestamp value in my webform. The hidden field does not work as I get some cast...
22
by: Mal Ball | last post by:
I hope I have the right forum for this question. I have an existing Windows application which uses a SQL Server database and stored procedures. I am now developing a web application to use the same...
7
by: JJ | last post by:
How do I set one field to have the updated timestamp, and another to have the created timestamp? I want to do this directly from code generated from DB Designer if possible?! JJ
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.