Query - Count With Date Intervals
I have this:
select count(*) from visitors
where visitordate >= '2008-01-01'
AND visitordate >= '2008-01-08'
Now this gives me a count of how many visitors that week visited my site but I want something more automatic. I do not know which functions to use to make this logic below happen:
Start with year base (ie:2008) increment by week number (ie:upto 52) and determine how many visitors each week visited the site.
I wish this worked:
select count(*) from visitors
where visitordate = week(1), then week(2), etc.
View 4 Replies (Posted: January 25, 2008 05:04PM)
Sponsored Links:
Related Forum Messages for MySQL:
Database With 5 Minute Intervals To Show Hourly Intervals?
I have some experience with MySQL but limited. I have a database which has columns: Date, Time, Value 1, Value 2, Value 3. This database has data inserted automatically every 5 minutes. with times like.. 00:03:00 and 00:08:00 all day until 23:08:00 . I would like to group it by Date and Time and sum all the values.. so instead of having 5 minute intervals, I'd have hour intervals (24).
Posted: November 14, 2008 03:12PM
View 7 Replies!
View Related
Creating Date Intervals In Python?
I want to use a for loop to print every date between 2 dates. Actually going to merge this with a MySQL query to pass the date into the query itself and into the filename of the output. So, how can I change this: sum = 0 for i in range(1,11): print sum sum += i To this? InputDate = '2009-01-01' for i in range('2009-01-01','2009-07-01'): print InputDate InputDate += i..........
Posted: Aug 3 09 at 6:19
View 3 Replies!
View Related
Select Between An Unknown Number Of Date Intervals
I currently have two tables, one is called games, the other called rounds. Here is the structure to help out: rounds +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | schedule_id | int(11) | NO | MUL | NULL | | | open_date | datetime | NO | | NULL | | | close_date | datetime | NO | | NULL | | | start_date | datetime | NO | | NULL | | | end_date | datetime | NO | | NULL | | +-------------+----------+------+-----+---------+----------------+ games +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | schedule_id | int(11) | NO | MUL | NULL | | | team_a | int(11) | NO | MUL | NULL | | | team_b | int(11) | NO | MUL | NULL | | | winner | int(11) | YES | MUL | NULL | | | date | datetime | NO | | NULL | | +-------------+----------+------+-----+---------+----------------+ Now the rounds table is a bunch of rounds with specified date ranges, and games are set within the round date range (but not always). Anyways, What I want to do is select all games between multiple round intervals. So if I have three round intervals,.........
Posted: Nov 9 10 at 16:12
View 1 Replies!
View Related
Count Entries In A "event" Table, Grouping By Date And Row Count - For A Date Period?
I have this query to count "views" in a table, by date. It obviously gives me only the dates where there has been activity, but I wish I could have MySQL return data for a selected period of time. For example 30 rows for a 30 day period, creating null data for dates where there are no matching rows. I wish to avoid using PHP to figure this out, so I thought I'd ask here for advice on how to maybe accomplish this with a smart query. QUERY: SELECT count(ID) AS views, date AS view_date FROM logging.views WHERE date < DATE_SUB(NOW(),INTERVAL 30 DAY) GROUP BY DAY(date)"; Example below only gives me 7 rows, obviously because there are only entries matching 7 different days in the selected 30 DAY interval. I wish I could get 30 rows representing all days in the set interval. OUTPUT: Array ( [0] => Array ( [date] => 2010-09-01 22:04:19 [views] => 20 ) [Code]....
Posted: Sep 15 10 at 21:38
View 1 Replies!
View Related
Group By Date, Count Of Multiple Fields Within Date
take table a: userID INT, userLogin VARCHAR and table b: customerID INT, userID INT, customerStarted DATE, customerFinished DATE what I'm looking to produce is a report by date, showing number of starts and finishes for all customers marked with a specific userID, like User 4: Date | starts | finishes 2008-10-01 | 0 | 5 2008-10-02 | 3 | 3 2008-10-03 | 4 | 2 2008-10-04 | 0 | 0 2008-10-05 | 3 | 3 ... etc.
Posted: October 21st, 2008, 02:38 PM
View 1 Replies!
View Related
COUNT WHERE (COUNT > X OR ABOVE) And WHERE DATE -3 Days Before
Example of Date of request 1. SELECT Count(tbl_eventbooked.idEvent), Sum(tbl_eventmain.StartDateEvent) FROM tbl_eventmain Left Join tbl_eventbooked ON tbl_eventbooked.idEvent = tbl_eventmain.id_Event WHERE tbl_eventmain.StartDateEvent <= '-2' GROUP BY tbl_eventbooked.UserEmail, tbl_eventbooked.FirstName Example Count of request 2. SELECT Count(tbl_eventbooked.idEvent) FROM tbl_eventmain Left Join tbl_eventbooked ON tbl_eventbooked.idEvent = tbl_eventmain.id_Event WHERE Count(tbl_eventbooked.idEvent) >= '15' (but how do you tell it 50% of row X)
Posted: November 14, 2008 09:08AM
View 4 Replies!
View Related
Table Record Intervals
I have the following scenario table amount | letter 1 | a 5 | b 7 | c I need a query if amount=1,2,3,4,5 it displays letter a amount=6,7 it displays letter b amount=8,9,... it displays letter c
Posted: March 13th, 2006, 06:00 AM
View 1 Replies!
View Related
Time Intervals Intersection
I have no idea how to implement this so I need some help.... I'm building an application for project management and for tracking down the hours..every employee must write his activities to this application... the problem is that I have no idea how to detect if the intervals intersect.... (it would be perfect if this can be calculated with only mysql - the app is coded in PHP otherwise) In mysql I have two fields(DATETIME) time_start and time_end for each activity entered...... So what I need is to check if intervals intersect... for example if I want to enter that I've worked from 8:45 - 13:55 on Project 1 ..and if there's another record in db that starts at 12:00 - 15:00 this would throw an error as intervals intersect.....this is pretty easy to implement if you have to compare the entering date with only ONE in db but ofcourse this is not the case..... so basicly here's a second (more realistic scenario) Times in DB: 8:00 - 9:45 13:45 - 15:00.... and I want to enter 9:00 - 12:00 which should throw the warning that there's a interval intersection error... I really do hope I made this clear cause english is not my primary language.
Posted: April 13, 2005 04:28AM
View 5 Replies!
View Related
Calculate Averages For 5 Min Intervals?
I have a table log with columns id, value, category and timestamp. Suppose the table is filled like this: ID VALUE CATEGORY TIMESTAMP 1 10 1 2010-11-1 10:00:00 2 20 1 2010-11-1 10:03:00 3 15 2 2010-11-1 10:15:00 4 05 2 2010-11-1 10:19:00 5 30 1 2010-11-1 10:24:00 6 12 1 2010-11-1 10:30:00 Now I would like to generate a table with an avg() for column value in 5 minute intervals for a specified check, starting from the last check entry. The output for check = 1 should be like: ID AVERAGE 1 12 2 30 3 15 the output for check = 2 should be like: ID AVERAGE 1 10 What is the best way to approach this? I have basic knowledge of MySQL but this puzzles me. Part of the query will be like this I suppose (without the 5 min interval grouping): SELECT avg(value) FROM log WHERE check = .
Posted: Nov 1 10 at 21:34
View 2 Replies!
View Related
Php - Select A Range Of Id's At Set Intervals?
I have the following scenario, I have a table column with the name categoryid in table categories, I need to select all rows within the table where categoryid is 100 and above in intervals of 100. SELECT * FROM categories WHERE categoryid IN(100,200,300,400,500,600,700,800,900,1000,1100,1200,1300,1400,1500,1600,1700,1800,1900,2000,2100,2200,2300,2400,2500,2600,2700,2800,2900,3000... Is there a better way to do this than manually typing all intervals, it should start at hundred and go up to the last number. Keep in mind that the last number can change as client adds new categories. is this possible, hope I make sense.
Posted: Feb 17 at 7:57
View 5 Replies!
View Related
Boundaries And Intervals In Queries?
I have two tables; Table1 Boundryid(INT)|starttime(TIME)|endtime(TIME) have below values (these are timelimits for each boundary) 1|'10:00:00'|'12:00:00' 2|'10:00:00'|'13:00:00' Table 2 Boundryid(INT)| duration(TIME) and below values (these are duration for each boundaries) 1|'00:30:00' 2|'01:00:00' Now I should list down all the intervals for the specific boundaries like below Round(int)|Boudaryid(int)| starttime(time) | endtime(time) 1|1|'10:00:00'|'10:30:00' 2|1|'10:30:00'|'11:00:00' 3|1|'11:00:00'|'11:30:00' 4|1|'11:30:00'|'12:00:00' 5|2|'10:00:00'|'11:00:00' 6|2|'11:00:00'|'12:00:00' 7|2|'12:00:00'|'13:00:00'
Posted: April 11, 2011 11:27PM
View 1 Replies!
View Related
Unix Timestamp And Intervals?
I'm wondering if its possible to use unix timestamp and intervals together to get past data. I currently have something like this MySQL Code: WHERE msgDate < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY) AND msgDate > DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 2 DAY) This returns one day ago records only. How would it be possible to use the unix timestamp through sql and still use intervals, if possible? I'm trying to parse the correct date and/or time based on the user. Currently, the server is located on the west coast (of the US) and I'm on the east coast, so when I make a new row it inserts 3 hours before (using the NOW() function). So when I print out the date/time it shows the incorrect information.
Posted: Dec 22, 2009, 23:10
View 1 Replies!
View Related
Working With Intervals Of Time?
I've not been able to wrap my head around how to write a query that deals with "intervals of time", at least not without resorting to egregious hacks. To explain: Imagine my client gives me utility bills that describe electrical consumption between an irregular pair of dates -- the dates are determined by when the meter was read. (From here on, I'll use "interval" to mean a start date / end date pair.) So I have a "meter_reading" table: reading_facts ---------------------- start_date | datetime end_date | datetime kwh_consumed | float cost | float I want to be able to "re-factor" bills day by day, week by week, month by month, quarter by quarter, etc, for example to generate consumption graphs with different time scales. To simplify things, assume the existence of a series of "interval tables": daily_intervals --------------------- start_date | datetime end_date | datetime -- start_date + 1 day weekly_intervals --------------------- start_date | datetime end_date | datetime -- start_date + 7 days monthly_intervals --------------------- start_date | datetime end_date | datetime -- start_date + 1 month ... etc I want to make a query that says "for each row in the interval table, what is the average consumption over that interval"? I have already written a stored function 'COVERAGE' that computes the percentage that a row of an interval table (e.g. one week) covers one of rows of the reading_facts table. What I can't get is how to form a query that finds every row in the reading_facts table that is at least partially covered by a row in the interval table (i.e. interval_table.start_date < reading_facts.end_date AND interval_table.end_date > reading_facts.start_date), and calls my stored function with those rows. And then there's the small matter of what I do with the results of calling the stored function... If I'm going about this all wrong, let me know. (I'm also leaving out lots of details, like how I actually create an average reading, the fact that I don't actually have datetime objects in the tables, but rather foreign key pointers etc.)
Posted: March 14, 2010 12:59AM
View 4 Replies!
View Related
Group Query By 15 Min Intervals
I've got a monitoring system that is collecting data every n seconds (n ~=10 but varies). I'd like to aggregate the collected data by 15 minute intervals. Is there a way to corral the timestamp column into 15 minute chunks to allow for grouping to work?
Posted: May 8 10 at 12:13
View 2 Replies!
View Related
Select Intervals From A Table?
I have a table that defines time intervals. | id | description | start_date | |____|_____________|____________| | 1 | First | NULL | | 3 | Third | 2009-12-18 | | 5 | Second | 2009-10-02 | | 4 | Last | 2009-12-31 | |____|_____________|____________| It stores only the start date, the end date is a day before next date that follows. [Code]...
Posted: Dec 18 09 at 18:28
View 4 Replies!
View Related
Data Containing Disjoint Time Intervals
I have an Activity table: create table Activity( id integer, startTime integer, endTime integer ); I know that all Activities are non-overlapping in time, but I cannot see a way to state this in SQL. Each Activity has number of Events, in a separate table. I am trying to avioid storing the activity iD for each event. There are likely many events per activity. create table Events( type int time integer ); I then create a view so I can easily see each the Activity in which each Event falls: create view v as select E.type, A.id, E.time from Activity A, Event E where time >= startTime and time <= endTime
Posted: June 20, 2008 12:19PM
View 1 Replies!
View Related
Automate Procedures At Regular Intervals
I wonder how I create a file that checks my database at regular intervals. I want to use it to go through a date list once a day, and if it finds "todays date" email a message attached to that date. Basicly an email reminder function that will exist on the web hotel I use.
Posted: October 03, 2004 06:53PM
View 1 Replies!
View Related
Finding Difference Between Time Intervals?
i have a attendance table which has the in and out entries of employees in the same column for the particular time instance. i want to find the total hours the employee is present inside the working room. i want diff of the each entry when the employee each time punch in and punch out. and the total time. database look:- empid,empname, date, time, inout e.g. 1 sunayna 2011-02-01 08:30:00 in 1 sunayna 2011-02-01 08:45:00 out 1 sunayna 2011-02-01 09:00:00 in 1 sunayna 2011-02-01 17:30:00 out I want total in time of sunayna ie (08:45:00 -08:30:00 )+(17:30:00 -09:00:00 )
Posted: March 14, 2011 01:39AM
View 9 Replies!
View Related
How Many Values Are Present In Two Intervals Across Several Tables
I am trying to make a query where I count the number of id that are present in an unixTimestamp interval and at the same time present in another unixTimestamp interval from a series of tables I.e How many id that are present where unixTimestamp > 1266428321 in "table 1" and "table 2" that is also present where unixTimestamp < 1266428321 in any of the tables. Not how many rows, but how many unique ids? The answer is three (id: 1 & id: 2 & id 3) Code: Table 1 +-------+---------------+ | id | unixTimestamp | +-------+---------------+ | | | | 1 | 1266416813 | | 1 | 1266416921 | | 3 | 1266418721 | | 2 | 1266420471 | | 4 | 1266428321 | | 1 | 1266429921 | | 3 | 1266430821 | | | | +-------+---------------+ [code]...
Posted: Feb 20, 2010, 17:06
View 3 Replies!
View Related
Looping To Find Future 6 Month Intervals
I have been trying to find a way to set an interval of 6 months and then calculate 6 month intervals from a certain date. I know that I can do this one-time: Quote: SELECT DATE_ADD("2008-11-03", INTERVAL 6 Month ) as threshold however this will not work in a loop and using it this way only provides a proper value once. Does anyone know if this can be done in some kind of MySQL loop or some other way? BTW...I did try this using unix timestamps however It was not precise enough; by the time one year had been added the 2008-11-03 became 2008-11-01, so unfortunately it did not serve the purpose.
Posted: Aug 5, 2008, 14:12
View 4 Replies!
View Related
|
TRACKER
