List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:October 6 2010 8:22pm
Subject:Re: Constructing query to display item count based on increments of time
View as plain text  
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 <heskin@stripped> 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 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=1
> >>
> >>
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


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

Thread
Constructing query to display item count based on increments of timePascual Strømsnæs6 Oct
  • Re: Constructing query to display item count based on increments of timeJohan De Meersman6 Oct
  • Re: Constructing query to display item count based on increments of timeTravis Ard6 Oct
    • Re: Constructing query to display item count based on increments of timeHank6 Oct
      • Re: Constructing query to display item count based on increments of timeJohan De Meersman6 Oct
        • Re: Constructing query to display item count based on increments of timeHank6 Oct
          • Re: Constructing query to display item count based on increments of timeJohan De Meersman7 Oct
  • my.iniElim PDT7 Oct
    • RE: my.iniandrew.2.moore7 Oct
      • Re: my.iniElim PDT7 Oct
      • where clauseElim PDT3 Jan