Constructing query to display item count based on increments of time

Hi!

How would one go about to construct a query that counts items within an
increment or span of time, let's say increments of 10 minutes?
Imagine a simple table where each row has a timestamp, and the query
should return the count of items occurring within the timespan of a
defined period.

Say,

09:00: 14
09:10: 31
09:20: 25
09:30: 0
09:40: 12

etc.

I have been able to get collections of item occurrence based on month
and day by using GROUP BY together with a DATE_FORMAT( start_time, "%d
%m %Y" ) eg.
I can however not seem to be able to find the solution to grouping based
on the minute increments in my example above.

Any suggestions?

--
Kind regards


Pascual Strømsnæs

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Constructing query to display item count based on increments of time

--0022152d6c65aff9a20491f04220
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

convert to unixtime, convert your interval to unixtime, creatively combine
with integer division to get a base number for each period, group by that
and count().

2010/10/6 Pascual Str=F8msn=E6s

> Hi!
>
> How would one go about to construct a query that counts items within an
> increment or span of time, let's say increments of 10 minutes?
> Imagine a simple table where each row has a timestamp, and the query shou=
ld
> return the count of items occurring within the timespan of a defined peri=
od.
>
> Say,
>
> 09:00: 14
> 09:10: 31
> 09:20: 25
> 09:30: 0
> 09:40: 12
>
> etc.
>
> I have been able to get collections of item occurrence based on month and
> day by using GROUP BY together with a DATE_FORMAT( start_time, "%d %m %Y"=
)
> eg.
> I can however not seem to be able to find the solution to grouping based =
on
> the minute increments in my example above.
>
> Any suggestions?
>
> --
> Kind regards
>
>
> Pascual Str=F8msn=E6s
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dvegivamp@tuxera.b=
e
>
>


--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0022152d6c65aff9a20491f04220--

Re: Constructing query to display item count based on increments of time

Maybe you could use something like the following to truncate your times to
10 minute increments before doing your GROUP BY and COUNT():

select concat(date_format(timestamp_col, '%Y-%m-%d %H:'),
truncate(minute(timestamp_col) / 10, 0), '0') from your_table;

-Travis

--------------------------------------------------
From: "Pascual Strømsnæs"
Sent: Wednesday, October 06, 2010 4:20 AM
To: "[MySQL]"
Subject: Constructing query to display item count based on increments of
time

> Hi!
>
> How would one go about to construct a query that counts items within an
> increment or span of time, let's say increments of 10 minutes?
> Imagine a simple table where each row has a timestamp, and the query
> should return the count of items occurring within the timespan of a
> defined period.
>
> Say,
>
> 09:00: 14
> 09:10: 31
> 09:20: 25
> 09:30: 0
> 09:40: 12
>
> etc.
>
> I have been able to get collections of item occurrence based on month and
> day by using GROUP BY together with a DATE_FORMAT( start_time, "%d %m
> %Y" ) eg.
> I can however not seem to be able to find the solution to grouping based
> on the minute increments in my example above.
>
> Any suggestions?
>
> --
> Kind regards
>
>
> Pascual Strømsnæs
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=travis_ard@hotmail.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Constructing query to display item count based on increments of time

Here's what I came up with:

select concat(left(DATE_FORMAT(start_time, "%Y-%m-%d %h:%i"
),15),"0") as time, count(*) from table group by time

-Hank

>>
>> How would one go about to construct a query that counts items within an
>> increment or span of time, let's say increments of 10 minutes?
>> Imagine a simple table where each row has a timestamp, and the query
>> should return the count of items occurring within the timespan of a defi=
ned
>> period.
>>
>> Say,
>>
>> 09:00: 14
>> 09:10: 31
>> 09:20: 25
>> 09:30: =A00
>> 09:40: 12
>>
>> etc.
>>
>> I have been able to get collections of item occurrence based on month an=
d
>> day by using GROUP BY together with a DATE_FORMAT( start_time, "%d %m %Y=
" )
>> eg.
>> I can however not seem to be able to find the solution to grouping based
>> on the minute increments in my example above.
>>
>> Any suggestions?
>>
>> --
>> Kind regards
>>
>>
>> Pascual Str=F8msn=E6s
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dtravis_ard@hotmail.=
com
>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dheskin@gmail.=
com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Constructing query to display item count based on increments of time

--005045015703cc03780491f88afe
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Two people already who suggested a text-based approach vs. my numeric
approach.

Analysing, my method takes a single function call per record (to_unixtime);
Travis' takes 4 (concat, date_format, truncate, minute) and Hank's 3
(concate, left, date_format).

Someone feel like benchmarking ? :-D



On Wed, Oct 6, 2010 at 5:44 PM, Hank wrote:

> Here's what I came up with:
>
> select concat(left(DATE_FORMAT(start_time, "%Y-%m-%d %h:%i"
> ),15),"0") as time, count(*) from table group by time
>
> -Hank
>
> >>
> >> How would one go about to construct a query that counts items within a=
n
> >> increment or span of time, let's say increments of 10 minutes?
> >> Imagine a simple table where each row has a timestamp, and the query
> >> should return the count of items occurring within the timespan of a
> defined
> >> period.
> >>
> >> Say,
> >>
> >> 09:00: 14
> >> 09:10: 31
> >> 09:20: 25
> >> 09:30: 0
> >> 09:40: 12
> >>
> >> etc.
> >>
> >> I have been able to get collections of item occurrence based on month
> and
> >> day by using GROUP BY together with a DATE_FORMAT( start_time, "%d %m
> %Y" )
> >> eg.
> >> I can however not seem to be able to find the solution to grouping bas=
ed
> >> on the minute increments in my example above.
> >>
> >> Any suggestions?
> >>
> >> --
> >> Kind regards
> >>
> >>
> >> Pascual Str=F8msn=E6s
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dtravis_ard@hotmail.com
> >>
> >>
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dheskin@gmail.co=
m
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dvegivamp@tuxera.b=
e
>
>


--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--005045015703cc03780491f88afe--

Re: Constructing query to display item count based on increments of time

Ok, I can see that. Here's a different approach that gets it down to
two function calls and some math.. and the DATE_FORMAT call might not
even be needed depending on the actual application.

select
DATE_FORMAT(start_time, "%Y-%m-%d %h:" ) as dhour,
10*(minute(start_time)%6) as dtime ,count(*)
from table
group by dhour,dtime;

-Hank



On Wed, Oct 6, 2010 at 4:22 PM, Johan De Meersman wrot=
e:
> Two people already who suggested a text-based approach vs. my numeric
> approach.
>
> Analysing, my method takes a single function call per record (to_unixtime=
);
> Travis' takes 4 (concat, date_format, truncate, minute) and Hank's 3
> (concate, left, date_format).
>
> Someone feel like benchmarking ? :-D
>
>
>
> On Wed, Oct 6, 2010 at 5:44 PM, Hank wrote:
>>
>> Here's what I came up with:
>>
>> =A0select concat(left(DATE_FORMAT(start_time, "%Y-%m-%d %h:%i"
>> ),15),"0") as time, count(*) =A0from table group by time
>>
>> -Hank
>>
>> >>
>> >> How would one go about to construct a query that counts items within =
an
>> >> increment or span of time, let's say increments of 10 minutes?
>> >> Imagine a simple table where each row has a timestamp, and the query
>> >> should return the count of items occurring within the timespan of a
>> >> defined
>> >> period.
>> >>
>> >> Say,
>> >>
>> >> 09:00: 14
>> >> 09:10: 31
>> >> 09:20: 25
>> >> 09:30: =A00
>> >> 09:40: 12
>> >>
>> >> etc.
>> >>
>> >> I have been able to get collections of item occurrence based on month
>> >> and
>> >> day by using GROUP BY together with a DATE_FORMAT( start_time, "%d %m
>> >> %Y" )
>> >> eg.
>> >> I can however not seem to be able to find the solution to grouping
>> >> based
>> >> on the minute increments in my example above.
>> >>
>> >> Any suggestions?
>> >>
>> >> --
>> >> Kind regards
>> >>
>> >>
>> >> Pascual Str=F8msn=E6s
>> >>
>> >> --
>> >> MySQL General Mailing List
>> >> For list archives: http://lists.mysql.com/mysql
>> >> To unsubscribe:
>> >> http://lists.mysql.com/mysql?unsub=3Dtravis_ard@hotmail.com
>> >>
>> >>
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dheskin@gma=
il.com
>> >
>> >
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dvegivamp@tux=
era.be
>>
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

my.ini

I recently upgraded mysql from 5.1.9-beta to 5.1.50

Since I used the custom data dir path, my upgrade is basically a replacement
of the bin folder and I used the old my.ini file that pointing the data dir
to the right path.

Then I found, in the event viewer, two warnings:

(1)
--myisam_max_extra_sort_file_size is deprecated and does nothing in this
version. It will be removed in a future release.

(2)
'--default-character-set' is deprecated and will be removed in a future
release. Please use '--character-set-server' instead.


I mainly used MyISAM tables but have a few of innodb tables.

What should I do to correct the my.ini?


Thanks for your help.

EL


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Constructing query to display item count based on increments of time

--0003255746ce35ed0c0492027085
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Wed, Oct 6, 2010 at 11:15 PM, Hank wrote:

> Ok, I can see that.
>

Oh, I wasn't implying that mine was necessarily better - I didn't even thin=
k
of doing it that way. I suggested a benchmark because different fuctions
might have different execution speeds. Now actually thinking about this,
I've come up with three different formulations of mine, even :-)

Given that I'm curious about this, I've bothered to do the benchmarks mysel=
f
:-) All running on the same virtual machine, a VirtualBox on my laptop,
which has 1 cpu and 384 megs of memory, running Debian 5.0.5 and MySQL
5.1.47 and using mysqlslap to perform 200.000 calls in the same thread,
iterated five times. My chosen datestamp is '2010-10-07 08:38:07'.



- select sql_no_cache unix_timestamp('2010-10-07 08:38:07') -
(unix_timestamp('2010-10-07 08:38:07') % 600);
- Average number of seconds to run all queries: 30.592 seconds
Minimum number of seconds to run all queries: 30.084 seconds
Maximum number of seconds to run all queries: 31.374 seconds


- select sql_no_cache (unix_timestamp('2010-10-07 08:38:07') div 600) *
600;
- Average number of seconds to run all queries: 29.606 seconds
Minimum number of seconds to run all queries: 29.454 seconds
Maximum number of seconds to run all queries: 29.813 seconds


- select sql_no_cache floor(unix_timestamp('2010-10-07 08:38:07') / 600)
* 600;
- Average number of seconds to run all queries: 30.857 seconds
Minimum number of seconds to run all queries: 30.274 seconds
Maximum number of seconds to run all queries: 31.378 seconds


- select sql_no_cache concat(date_format('2010-10-07 08:38:07', '%Y-%m-%=
d
%H:'), truncate(minute('2010-10-07 08:38:07') / 10, 0), '0');
- Average number of seconds to run all queries: 32.905 seconds
Minimum number of seconds to run all queries: 32.099 seconds
Maximum number of seconds to run all queries: 33.429 seconds


- select sql_no_cache concat(left(DATE_FORMAT('2010-10-07 08:38:07',
'%Y-%m-%d %h:%i'),15),'0');
- Average number of seconds to run all queries: 31.042 seconds
Minimum number of seconds to run all queries: 30.369 seconds
Maximum number of seconds to run all queries: 31.727 seconds


- select sql_no_cache DATE_FORMAT('2010-10-07 08:38:07', '%Y-%m-%d %h:'
), 10*(minute('2010-10-07 08:38:07')%6);
- Average number of seconds to run all queries: 32.012 seconds
Minimum number of seconds to run all queries: 31.335 seconds
Maximum number of seconds to run all queries: 32.894 seconds


So, it turns out that the method used doesn't make a major difference.

My guess would be that function execution and arithmetic is actually pretty
much instant, and that the major cost here was simply initializing the
parser and other structures. There is in any case no obvious relation
between number of function calls and execution time here.




> Here's a different approach that gets it down to
> two function calls and some math.. and the DATE_FORMAT call might not
> even be needed depending on the actual application.
>
> select
> DATE_FORMAT(start_time, "%Y-%m-%d %h:" ) as dhour,
> 10*(minute(start_time)%6) as dtime ,count(*)
> from table
> group by dhour,dtime;
>
> -Hank
>
>
>
> On Wed, Oct 6, 2010 at 4:22 PM, Johan De Meersman
> wrote:
> > Two people already who suggested a text-based approach vs. my numeric
> > approach.
> >
> > Analysing, my method takes a single function call per record
> (to_unixtime);
> > Travis' takes 4 (concat, date_format, truncate, minute) and Hank's 3
> > (concate, left, date_format).
> >
> > Someone feel like benchmarking ? :-D
> >
> >
> >
> > On Wed, Oct 6, 2010 at 5:44 PM, Hank wrote:
> >>
> >> Here's what I came up with:
> >>
> >> select concat(left(DATE_FORMAT(start_time, "%Y-%m-%d %h:%i"
> >> ),15),"0") as time, count(*) from table group by time
> >>
> >> -Hank
> >>
> >> >>
> >> >> How would one go about to construct a query that counts items withi=
n
> an
> >> >> increment or span of time, let's say increments of 10 minutes?
> >> >> Imagine a simple table where each row has a timestamp, and the quer=
y
> >> >> should return the count of items occurring within the timespan of a
> >> >> defined
> >> >> period.
> >> >>
> >> >> Say,
> >> >>
> >> >> 09:00: 14
> >> >> 09:10: 31
> >> >> 09:20: 25
> >> >> 09:30: 0
> >> >> 09:40: 12
> >> >>
> >> >> etc.
> >> >>
> >> >> I have been able to get collections of item occurrence based on mon=
th
> >> >> and
> >> >> day by using GROUP BY together with a DATE_FORMAT( start_time, "%d =
%m
> >> >> %Y" )
> >> >> eg.
> >> >> I can however not seem to be able to find the solution to grouping
> >> >> based
> >> >> on the minute increments in my example above.
> >> >>
> >> >> Any suggestions?
> >> >>
> >> >> --
> >> >> Kind regards
> >> >>
> >> >>
> >> >> Pascual Str=F8msn=E6s
> >> >>
> >> >> --
> >> >> MySQL General Mailing List
> >> >> For list archives: http://lists.mysql.com/mysql
> >> >> To unsubscribe:
> >> >> http://lists.mysql.com/mysql?unsub=3Dtravis_ard@hotmail.com
> >> >>
> >> >>
> >> >
> >> > --
> >> > MySQL General Mailing List
> >> > For list archives: http://lists.mysql.com/mysql
> >> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dheskin@gmail.com
> >> >
> >> >
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dvegivamp@tuxera.be
> >>
> >
> >
> >
> > --
> > Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dvegivamp@tuxera.b=
e
>
>


--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0003255746ce35ed0c0492027085--

RE: my.ini

Open you're my.ini and correct the values as stated in the event viewer=20

Check the online documentation for the keys and values to set.
http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables. html=20

Regards


-----Original Message-----
From: ext Elim PDT [mailto:elim@pdtnetworks.net]=20
Sent: 07 October 2010 04:51
To: mysql@lists.mysql.com
Subject: my.ini

I recently upgraded mysql from 5.1.9-beta to 5.1.50

Since I used the custom data dir path, my upgrade is basically a replacemen=
t=20
of the bin folder and I used the old my.ini file that pointing the data dir=
=20
to the right path.

Then I found, in the event viewer, two warnings:

(1)
--myisam_max_extra_sort_file_size is deprecated and does nothing in this=20
version. It will be removed in a future release.

(2)
'--default-character-set' is deprecated and will be removed in a future=20
release. Please use '--character-set-server' instead.


I mainly used MyISAM tables but have a few of innodb tables.

What should I do to correct the my.ini?


Thanks for your help.

EL=20


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dandrew.2.moore@noki=
a.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: my.ini

Thanks a lot Andrew.

----- Original Message -----
From:
To: ;
Sent: Thursday, October 07, 2010 3:07 AM
Subject: RE: my.ini


Open you're my.ini and correct the values as stated in the event viewer

Check the online documentation for the keys and values to set.
http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables. html

Regards


-----Original Message-----
From: ext Elim PDT [mailto:elim@pdtnetworks.net]
Sent: 07 October 2010 04:51
To: mysql@lists.mysql.com
Subject: my.ini

I recently upgraded mysql from 5.1.9-beta to 5.1.50

Since I used the custom data dir path, my upgrade is basically a replacement
of the bin folder and I used the old my.ini file that pointing the data dir
to the right path.

Then I found, in the event viewer, two warnings:

(1)
--myisam_max_extra_sort_file_size is deprecated and does nothing in this
version. It will be removed in a future release.

(2)
'--default-character-set' is deprecated and will be removed in a future
release. Please use '--character-set-server' instead.


I mainly used MyISAM tables but have a few of innodb tables.

What should I do to correct the my.ini?


Thanks for your help.

EL


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=andrew.2.moore@nokia.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=elim@pdtnetworks.net



------------------------------------------------------------ --------------------



No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.862 / Virus Database: 271.1.1/3182 - Release Date: 10/07/10
00:34:00


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

where clause

I have a table with a column pv (int)
I want to fetch all the rows r such that (r.pv)+1 is the pv-value of another
row.

what the sql looks like? thanks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org