A Query To Find Something From Last 30 Days
So basically I am trying to do a query to pull up a "request" that is over thirty days old. How do I setup the other half of the inequality to automatically adjust for what 30 days old is each day. Date is in this format: 2006-07-18 11:59:59
$result = mysql_query('SELECT * FROM requests WHERE reqtime < "2006-07-18 11:59:59" ');
View Complete Forum Thread with Replies
Related Forum Messages:
Find Average Amt Of Days Dates
I've been working on this problem for quite some time now. I've searched google and all over these forums, but didn't quite find what I'm looking for. Here's what I'm doing: mysql> SELECT idno, sro, pname, adate, cdate , TO_DAYS(cdate)-To_DAYS(adate) as days FROM turnaround group by idno,sro order by adate; +------+----------+--------------+------------+------------+------+ | idno | sro | pname | adate | cdate | days | +------+----------+--------------+------------+------------+------+ | 4 | S1111115 | Tupperware | 2003-05-03 | 2003-05-05 | 2 | | 5 | S1111116 | Tupperware | 2003-06-03 | 2003-06-05 | 2 | | 3 | S1111114 | Blab | 2004-05-03 | 2004-05-05 | 2 | | 2 | S1111112 | Product Test | 2005-03-11 | 2005-03-11 | 0 | | 1 | S1111113 | Big Tester | 2005-04-06 | 2005-04-08 | 2 | | 6 | S1111111 | blah blah | 2005-11-18 | 2005-11-22 | 4 | +------+----------+--------------+------------+------------+------+ 6 rows in set (0.00 sec) What I need to do now is find the average number of days. I've tried several combos of AVG() without sucess. Could someone point me in the right direction?
View Replies !
Find Days, Hours And Minutes Between 2 Dates
I have a datetime field in a mysql database and i want to output how many days, hours and minutes are left between the current date and the future date. So far i just convert the datettime to a timestamp like so: $start = strtotime($began); Where $began is the current time, basically i just need to know how to convert a timestamp to days, hours ect If i was to minus the current timestamp from the future timestamp.
View Replies !
To Find Days Per Month In A Date Range
How to find days per month in a date range using MySQL query? For example I have a start date - 04/28/2007 (mm/dd/Y) and end date 05/04/2007 , I have to find the days in each month that comes in the above date range In the above example the days in April are 3 and in may the days are 5 (I am using MySQL database,Please find a query that outputs the above result)
View Replies !
Last 7 Days Query
I'm in the middle of moving a MS Access DB to a MySql backend. I have figured out about 90% of the problems I have faced, execpt for this one. I have 3 Queries, which pull records depending on a date range. (Today,Last 7 Days,Next 7 Days) The one I'm having problems with is the "Last 7 Days" Query. Here's the Criteria I'm Using: Between Date() And DateAdd("d",-7,Date()) When the tables are in access things work Fine, but when the tables are in MySql the Query doesn't work. Changing things a little and playing around with the criteria, it looks like MySql/MyODBC or something doesn't like the "-7" part, as when I make the # a non negitive value the query works.
View Replies !
Query For Last 7 Days
i have a mysql table where a random number of entries can be inserted on a given day. for example during a week monday could have 3 entries, tuesday 0, wednesday 5, thrusday 4, and so on. think of it as like storing blog entries. i want to have a query select all the entries from the last seven days of entries but not including days when nothing was entered. so during a week of 7 days, if nothing was entered on say tuesday and friday, i would like to keep going back until i have a total of seven days with results. to sum it up quick: i want to return all entries for the last 7 days of entries but days without any entries don't count.
View Replies !
Query On Last 7 Days
I have a query below and I was wondering how could I query only the last 7 days from the time I ran the query? If its easier not to put in my query can you just show how it would be done.....
View Replies !
Date To Days Query
I need to update one of our mysql tables, which has about 60,000 entires and correct the amount of days remain on each data record. An example of one of the data entires is: memid days regdate expdate -------------------------------------- 625290 | 5 | 2003-07-15 | 2003-08-16 now the days should be 14 and not 5 days. I have been trying to update the table with a single SQL query but haven't come up with a way to do this.. I'm sure it's something simple but I can't seem to come up with it. What is the best way to accomplish this in a single query to update the entire table so all the "days" are accurate according to the "expdate", and change the "days" so they are correct?
View Replies !
Totals Query Based On Days
If I have a table with a ProductID, Quantity, & DateTime field, & would like to have the sum of the Quantity calculated per product per day with blank days being accounted for even if zeroed out, how would I go about accomplishing this in one query? Example result for ProductX:
View Replies !
Find And Replace Query
Recently i completed a forum move to a new URL, however there are many posts on my board that still use the old URL for reference. Is there a query that i could run on my database to find all the instances of the first URL and replace it with the second?
View Replies !
Query To Find Friends Of A Friend
I'm experimenting with writing my own social network script but I'm struggling a bit with one of the queries. I have a users table, and a friendships table containing user_id and friend_id columns which are both foreign keys linking to users.id (sql dump at end of this post). if x is a friend of y, and the friendship is approved, then y is also a friend of x. I'm using this query to retreive all the friends of x (in this example, x has an id of 99):
View Replies !
How To Find Out How Many Times A Query Has Been Executed
How can you count the number of times your query has been executed? So I'm trying to set it up, so that I get a variable on every time someone views a review. I have two querys right now, but honestly don't really know what I'm doing. And I'm assuming I have to increment viewcount everytime that query gets executed. This query looks up the review:
View Replies !
Need A Query To Find All Spam Entries In My DB
I need to tweak the following query to help weed out spammers who have created accounts. I want to try and: 1. Show accounts that have alpha characters in the zip field 2. Show accounts that use more than 5 numbers in the zip field select zip, country from users where country = 'United States'
View Replies !
Find Hightest Number After Finding First Query
i have this code but it does not display the highest number or anything. it should be searching the field 'name' for the string $name and only display the one that has the highest number in the 'number' field. what am i doing wrong? $res = mysql_query("SELECT MAX( number) FROM data WHERE name='".$name."'"); $high = mysql_result($res, 0, 'number') or die(mysql_error()); echo($high);
View Replies !
Mysql Query Search And Find In String
I have following values in a field of mysql table. These values are stored as string [varchar] in the field. Values : 3,4,10,21,20,8,100,2,6 How can I check through MYSQL query that 100 or 21 or 4 exists in the string?
View Replies !
Date Query (how Do I Get Everything With A Date In 7 Days?)
the goal is a reminder email to people who have a workshop coming up in 7 days. So, workshop date is 2006-05-25. The reminder email should go out on 2006-05-18 I guess mysql query returns all records where (Date column - 7 days) = current date. Right? But what does teh QUERY look like? And how does it accomodate if today is 2006-05-28 and the upcoming workshop is 2006-06-04 (so month changes over the 7 day period).
View Replies !
Find All Games A User Has A Top 3 Score In (was "Need Help With A Query")
I am trying to create a query that will go through a scores table for me and find all the games that a user has a top 3 score in. Here are the table that are relevant I think... CREATE TABLE files ( fileid int(11) NOT NULL auto_increment, file text NOT NULL, icon text NOT NULL, filelocation int(1) NOT NULL default Ƈ', iconlocation int(1) NOT NULL default Ƈ', customcode text NOT NULL, title text NOT NULL, description text NOT NULL, keywords text NOT NULL, width int(11) NOT NULL default Ɔ', height int(11) NOT NULL default Ɔ', category int(11) NOT NULL default Ɔ', timesplayed int(11) NOT NULL default Ɔ', status int(1) NOT NULL default Ɔ', filetype int(2) NOT NULL default Ɔ', dateadded date NOT NULL default ��-00-00', rating int(11) NOT NULL default Ɔ', totalvotes int(11) NOT NULL default Ɔ', totalvotepoints int(11) NOT NULL default Ɔ', scores enum(Ɔ',Ƈ') NOT NULL default Ɔ', PRIMARY KEY (fileid) ) TYPE=MyISAM AUTO_INCREMENT=2285 ; CREATE TABLE scores ( scoreid int(11) NOT NULL auto_increment, fileid int(11) NOT NULL default Ɔ', score int(11) NOT NULL default Ɔ', username text NOT NULL, userid int(11) NOT NULL default Ɔ', usercomment text NOT NULL, dateadded date NOT NULL default ��-00-00', PRIMARY KEY (scoreid) ) TYPE=MyISAM AUTO_INCREMENT=16 ; CREATE TABLE users ( userid int(11) NOT NULL auto_increment, username text NOT NULL, password text NOT NULL, email text NOT NULL, status enum(Ƈ',Ɔ') NOT NULL default Ɔ', usergroup int(11) NOT NULL default Ƈ', joined date NOT NULL default ��-00-00', played int(11) NOT NULL default Ɔ', comments int(11) NOT NULL default Ɔ', avatar text NOT NULL, avatar_uploaded enum(Ƈ',Ɔ') NOT NULL default Ɔ', location text NOT NULL, website text NOT NULL, gender int(1) NOT NULL default Ɔ', favourite int(11) NOT NULL default Ɔ', receiveemails enum(Ƈ',Ɔ') NOT NULL default Ƈ', newpm enum(Ƈ',Ɔ') NOT NULL default Ɔ', confirmation text NOT NULL, PRIMARY KEY (userid) ) TYPE=MyISAM AUTO_INCREMENT=8 ; ( Basically the query would need to search through the scores table to find if the specified user $userid has the top 3 (by top 3 i mean 1st, 2nd, or 3rd highest score) scores of any game in the scores table. The end resukt would be a list that contained the fileid of the game the user has a top 3 score in, the actual score, and the position (1, 2, or 3) Im not sure if I am even explaining this right cause im kind of tired. I am a novice MYSQL user but this just has me stumped
View Replies !
Query To Find Friends Of Friends
I'm experimenting with writing my own social network script but I'm struggling a bit with one of the queries. I have the following table structure: users ------ id - integer (pk) username - string friendships ---------- id - integer (pk) user_id - integer (fk to users.id) friend_id - integer (fk to users.id) approved - boolean if x is a friend of y, and the friendship is approved, then y is also a friend of x. I'm using this query to retreive all the friends of x (in this example, x has an id of 99): SELECT CASE WHEN user_id=99 THEN friend_id ELSE user_id END AS fid FROM friendships WHERE (user_id=99 OR friend_id=99) AND approved IS true; so far so good, but what I'd like to do now is extend the search to find all friends of friends of x, but I'm not making any progress.
View Replies !
30 Days
I was wondering is it possible to update a value in a feild by executing a query for date values? Basically, I need store the CURRENT_DATE with 30 days added to it into my db. How would I do that? My table for test purpose is only one feild called date where the date is stored in the following format YEAR-MM-DD
View Replies !
Add 45 Days
i have a simple problems in query, i want to add current date(2006-08-17 00:00:00) + 45 days. = (2006-09-14 00:00:00) but it will give result Equivalents = 2051 that
View Replies !
Last 7 Days
I have a table with a date column (yyyy-mm-dd). I need to find all rows which are listed dates from Sunday to Saturday of the current week. I tried DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= datecol but that doesn't work...
View Replies !
Counting Days
How can I make a query such that in the "WHERE" part of my clause, I want to put something to the effect that, say, X days have elapsed since a given date. For example, suppose in my table, I have a boolean field and a date field. I want to create a query asking for those rows where X days have elapsed from the date field in a row and the boolean, say, is false. Do I need to create a third field, called, say "X" for how many days have elapsed between the date field and today, and update every row in the table every day?
View Replies !
Adding Days
How can i add 7 days to a date type? Ive tryed simple now() + 7 to get seven days from now but when i checked the database it was still just today.
View Replies !
Date > Now() - 360 Days
Im trying to select all records where a date field is greater than now minus one year, have tried a variety of different approaches none have worked. Im sure it should be simple but im struggling...
View Replies !
Days Between Start And End
this is the SQL i have so far: SELECT c.ID AS 'Course ID', date_format(c.start_date,'%d/%m/%Y') AS 'Start Date', date_format(c.end_date,'%d/%m/%Y') AS 'End Date' , t.description AS 'Course Description', FROM media_courses AS c INNER JOIN media_courses_type AS t ON t.ID = c.media_courses_type_ID GROUP BY c.ID ORDER BY c.start_date ASC
View Replies !
Birthdays In The Next 7 Days
Has any one got any suggestions on how to do this. I have a column date_of_birth that is a date field and I want to select all users who have a birthday in the next 7 days.
View Replies !
Records From Last 7 Days
I am building a site that will show racing results and want to be able to show the results from the last 7 days. The table is called 'results' structure of the table is: Date Course Time Horse Odds Result How can I just return the restuls in the last 7 days?
View Replies !
Data For Next 7 Days
I want to select records from database with a field holding a date in a date type field where the date record is between today and a weeks time i.e SELECT * From 'dbase' WHERE 'date' "is between today's date and today plus 7 days" ORDER by 'date' ASC I've tried many variations and all I have achieved is to output todays date!!! Can anyone help me with a simple query
View Replies !
Data From 'x' Days
I am having some problems writing a query. What I want to do is to pull data from 2 tables (a user table, and an awards table) and only have it show awards that were issued from the last 'x' Number of days. Lets say 7. Here is what I have so far: SELECT A1.user_name, A2.awarded_date FROM e107_user A1, e107_ranks_awarded A2 WHERE A1.user_id = A2.awarded_user_id This pulls all the information I need, but it pulls ALL dates. I am going to use this in a PHP module. What I need help with is to make it so it only lists awards from the past 7 days.
View Replies !
Select The Last X # Of Days
I have a MySQL database, and one of the fields is called date, and is of the date type. I would like to access * fields of every entry in the database that has a date within the last 31 days of today. I'm using "SELECT * WHERE (DAY(date)-DAY(NOW))<31" as my query but this doesnt seem to be working. Does anyone know why this is wrong, or can someone point me in the right direction, or give me an example of how to do it?
View Replies !
Days Between Dates
I need to know how to get the number of days between two dates using MySQL 3.2. I went over the manual before posting and found (DATEDIFF) but that can only be used with version 4.1 witch is still in alpha. Can anyone solve my problem?
View Replies !
Days And Dates
i have a table the contains news data for users. what i want to be able to do is give the users an option to view news by - today, yesterday, 3days ago, 4 days ago. i have a date column in the table for for when the news item was created. the problem is i don't know how to subtract days from the date. simply grabbing the day and subtracting will work unless it is the 1st of the month and then you are going back to the previous month and have to subtract 1 from that too. can SQL do these sorts of calculations?
View Replies !
Get Number Of Days
I have a date field called ExpireDate. I would like to query and get the number of days from today until an account will expire to display to the user. I am not sure how to accomplish this in a query. I thought about just querying for the date and doing the math with php functions but I know this can be done in a query. ExpireDate ('Y-m-d" format)
View Replies !
Get How Many Days From Date?
I am trying to order some products by popularity, I have a product_date_added column and a product_stat column (how many hits each product has). I know I need to divide the hits by the days to get the hits per day to see how popular the product is. like this: SELECT (hits / days) AS hitrate, cart_product.* FROM cart_product ORDER BY hitrate DESC what I need to figure out is how to determine how many days it has been on there? how can I subtrat the product_date_added from the current date to get the amount of days it has been alive?
View Replies !
Days Since Last Contact
In a ERP aplication, Im trying to know the days since last contact of a customer. In this case, each 'tarea' has a date, and tarea's table is conected to 'ventas' with it's 'venta' field. How should the function be? Code: SELECT ventas.id , ( latest date from tareas's table - CURDATE() ) AS days_since_contact FROM ventas LEFT OUTER JOIN tareas AS tareas_alias ON tareas_alias.venta = ventas.id GROUP BY papeles.id ORDER BY max_gramaje_diferencia ASC
View Replies !
Working Out 7 Days
in this query i am trying to find the number of products (added by date) in the last 7 days! so if i upload 23 new products in the last 7 days the query selects all date/time stamps from the last 7 days! PHP Code: <?php                    $q_p = "SELECT `date_added` FROM `fcp_products` WHERE `date_added` >= CURDATE();";                    $r_p = mysql_query($q_p);                    $n_p = mysql_num_rows($r_p); ?>
View Replies !
Date_sub (last Two Days)
I am trying to select entries from a Mysql database which were added within the last two days...I am trying to use something like the following, PHP Code: datefield >= DATE_SUB(CURDATE(),INTERVAL 2 DAY)
View Replies !
Timestamp :: Add 7 Days Time
How do i display a date as a new column 7 days after the initial timestamp eg. current timestamp = 20050310000000 if i add 7 days to this won't it result in 20050317000000 eg SELECT *, DATE_FORMAT('%d/%m/%Y' , (dateAuthorised+7)) as expiryDate I know this is the wrong approach as the timestamp 20050330000000 would cause an error
View Replies !
Determining Number Of Days
I'm trying to determine the number of days between today and the value in a datetime field in a table. It appears my version of MySQL doesn't have DATEDIFF available, so I'm wondering if there's another method to achieve the same result as: SELECT DATEDIFF(CURDATE(),`mydate`) AS numdays FROM tblArticles WHERE artid = 10 .
View Replies !
Date Functions For Days
This time i looked on the mysql site first, found the answer but it doesnt work I am trying to get the days between two dates with the following: SELECT DATEDIFF(now(), date_joined) AS 'joined', DATEDIFF(now(), trial_end_date) AS 'trial' FROM JSPCustomers WHERE username like 'qwerty' And i get the folllowing error: Syntax error or access violation: You have an error in your SQL syntax near '(now(), date_joined) AS 'joined', DATEDIFF(now(), trial_end_date) AS 'trial' FRO' at line 1
View Replies !
Erron In Date + Days
i want to retrive data from mysql database. it will be simply revrive date date from database. Eg :- 2006-06-27 which that code below of date retrive code. <? echo $rsProdDetail["product_added_date"];?> but i am also retrive data date + display day. which i will that code. eg:- 2006-06-27 + 3( 3 is prod_dispdays) but it will give me result that 2009 only which i will this code. <? echo ($rsProdDetail["product_added_date"]+$rsProdDetail["prod_dispdays"]);?></font> <? } else { } ?>
View Replies !
Fetch Data 2 Days
I have 1 column called expired_date in tbl_registration. How do I fetch all data that will expire in 2 days time based on current date?
View Replies !
Calculate Number Of Days
I'm trying to write an online user subscriptions system. The admin can manually add subscriptions to user accounts. This is all working fine however I would like to add in a checking feature that errors back to the admin if he tries to add an expiry date of April 31st or February 30th, as both of these dates never occur. My current insert script (into a standard DATE field 'YYYY-MM-DD') does allow these values to be inserted, so I would naturally like to prevent this from happening. Is there an effective way of counting the number of days for any given month in any given year ? Once I have this number I can then perform my error checking routines. I'm just a little stumped at how to get this in the first place.
View Replies !
Select All Records From X Days Ago
You have DATE, DATETIME and TIMESTAMP. I want to be able to retrieve records by saying, "Select all records from 4 days ago." I dont care so much for the time, but the date is very important.
View Replies !
Calculating Business Days
I need to calculate the number of business days between a pair of arbitrary dates.Some scrounging around online delivered me this query,which basically does the trick: Quote:SELECT d1, d2, @dow1 := DAYOFWEEK(d1) AS dow1, @dow2 := DAYOFWEEK(d2) AS dow2, @days := DATEDIFF(d2,d1) AS Days, @wknddays := 2 * FLOOR( @days / 7 ) + if( @dow1 = 1 AND @dow2 > 1, 1, if( @dow1 = 7 AND @dow2 = 1, 1, if( @dow1 > 1 AND @dow1 > @dow2, 2, if( @dow1 < 7 AND @dow2 = 7, 1, 0 ) ) ) ) AS WkndDays, @days - @wkndDays AS BizDays FROM dates ORDER BY d1,d2;
View Replies !
|
TRACKER
