MYSQL :: Counting Days

Counting Duration Between Time/days
I have store the current time when user logout.
So when user login back, I want to show them how long or the duration of the idle time in mins.

Eg:
If user logout at 8:00PM(logout_time) and login back at 8:30PM, this means there is 30mins.

Also if user logout for few days, the duration can be counted either.
Eg:2880mins (for 2 days)

Do I need another field to hold the duration table?

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

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...

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

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?

Get Last Posts In 5 Days
is there any idea how to get last posts in 5 days? I took current date minus to 5, but having trouble when it's moving to a new month.

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.....

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.

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.

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?

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?

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?

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

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.

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.

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

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.

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?

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)

Get 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.

Compute Days To Birthday
Table Users(login VARCHAR(50),birthday DATETIME)

I would like to compute in how many days user has a birthday. I read topic about select user with birthday in next 7 days . I used this query:

SELECT login,birthday,
DAYOFYEAR(birthday + INTERVAL YEAR(NOW()) - YEAR(birthday) YEAR) -
DAYOFYEAR(NOW()) as days
FROM users
The problem is with users who have already had birthday in this year. It returns negative number. Do you know how to write correct query?

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" ');

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?

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;

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 .

Selecting Records Only X Days Old
Using PERL and MySQL.

I have the select statement working for all records, is it possible to select only records (in this case it will only return one) that is as old as say 30 days?

Multiple Tables And Last 30 Days
What i'm trying to do is query two tables for the last 30 days and i'm not quite sure how to do that. Here's what i've got but it don't work (i get an error),

SELECT a.count,b.count as Rcount FROM $Table_links a, $Table_referer b WHERE
a.TO_DAYS(NOW()) - TO_DAYS(date) <= 30 && b.TO_DAYS(NOW()) - TO_DAYS(date) <= 30

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?

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
{

} ?>

Date Functions, How To Calculate Last 7 Days...
Hope you guys don't mind me posting questions every other day MySQL is not my strong suit..

Q: If my table uses the DATE field, how would I go about selecting all the rows where the date is within the last 7 days?

Query For Deleting Older Than 10 Days !
I have a table with some coulomns one of them is datetime that save the insert time

I need a query for deleting the fields that inserted older than 10 days

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:

All Days In Current Week Query
I need all Entrys created in This Week.

The Week starts with monday till friday.

Can you give me an SQL Query command ?

(SELECT * FROM MAIN WHERE DATE = {THIS WEEK}}

Automatically Deleting Entries From > 7 Days Ago
Is there a way to do this automatically in the database settings or w/e?

If not, what query would I have to use if I use the code below to store the date/time something happened.

<?php
echo date("d-m-Y H:i:s", time());
?>

Send An Email 60 Days After Registering
I need some advice on a feature that a client wants to incorpate into their DB. I have a MySQL DB that uses PHP to insert the current date when they register thier info. The client wants to email that person, 60 days after they register.

Does anyone have any ideas on how to go about this?

32763 Opened Tables In 3 Days...
32763 Opened tables in 3 days...

The MySQL Manual says "If the value is quite big" you should increase your table_cache. What is quite big for 7 sites running PHP MySQL?

What should my table_cache be set to.

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?

Getting Birthdays From Today Untill 21 Days In The Future
Got a table of birthdays.

Need to retrieve all birthdays from today to 21 days in the future.

Using this query:

SELECT * from birthdays
where(DAYOFYEAR(curdate()) <= dayofyear(birthday)
AND DAYOFYEAR(curdate()) +21 >= dayofyear(birthday) OR DAYOFYEAR(curdate())
<= dayofyear(birthday)+365 AND DAYOFYEAR(curdate()) +21 >= dayofyear(birthday)+365);

However it returns birthdays from yersday as well.
If I change the second line from <= to just = it works as expected with my test data but it their a better way to do it?

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)

Retrieving Records Entered In Last Couple Days
How can i retrieve records that have been entered in the last couple days or even last week?

I know i will first need a field called "entry date".

How do i match the date to the entry date and count backwards for the past couple days?

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.

Auto Calculate Total DAYS For Leave, Help Please!
I want to auto calculate DAYS for leave taken based on date key-in by user:

For example:

Start date: 15-04-2004 -- (Friday)
End date: 18-04-2004 -- (Monday)
Days: 3 -- (calculate automatically)

From the date given above, system will automatically:-
1) Count days as 3 -- assuming working day is 15, 16 and 18 only.
2) Sunday will not be count as working day.

Is this possible with MySQL(3.32) + PHP?

Auto-backup Database - Delete Backups Older Than X Days
I am not planning on doing this yet but I need to know from a feasibility point of view.

I know it is possible to set up cron jobs to automatically backup databases on whatever time cycle you want but I have no experience writing them. Does anyone know how you tell it to backup your database with a name and a timestamp, while also deleting backups older than x (7 for example) days?

Also does anyone know how to keep backups secure online? Do you encrypt them? Do you download them to a local server? To your own computer (unless its a very large database)? How do people practically go about making sure backups work, and secure them effectively. And if you do download them to your own local computer, how do you go about doing that? Is it an automatic thing?

I just have all these questions but I have no idea about going about performing backups the most efficient and secure way!

Sub Counting For Each Row
For each row in my table, I'm getting stuck doing a count. An extract of the table is as follows:

user_id | name | favourite_user
1 | "person 1" | 2
2 | "person 2" | 1
3 | "person 3" | 1
4 | "person 4" | 2
5 | "person 5" | 2

I'm trying to select all rows from the table, and at the same time, a count of how popular each person is. In two queries, this can be achieved by:

SELECT * FROM TABLE;
SELECT favourite_user, COUNT(*) FROM TABLE GROUP BY favourite_user

How can I combine the queries so that for each person I return their data and a count of their popularity?

Counting Records
Got a question for you that is really stumping me. I have 3 tables

job_jobs
------------
job_id
job_name
.
.
.

job_cats
-------------
cat_id
cat_name
.
.
.

job_jobsToCats
-------------
jtc_id
job_id
cat_id

***************

ok i have those 3 tables to categorise the jobs on the site and im trying to build a query that will essentially get all categry names and display the number of jobs in that category so when i output it i will get

accounting (12)
admin(2)
etc
etc

Subquery And Counting
I have a table called people in my database.

it looks like this

+----+------+
| id | name |
+----+------+
| 2 | bob |
| 1 | Jill |
| 3 | Jill |
| 4 | John |
| 5 | Jill |
+----+------+
I want to display the distinct name and how many times they are in the table
So like this

Jill - 3
bob - 1
John - 1

How would I go about doing this. I thought maybe something like this but not to sure

[code]
SELECT COUNT(name)
FROM people
WHERE name =
(SELECT DISTINCT name
FROM people);
[code]

Counting Like A Human
I have a query that does an ORDER BY area ASC. When it does the order by it is ordering by how a computer counts rather than how people count.

As an example here is some of my data ....

Counting Query
Is there a MySQL function that i can use to just count the number of rows in a table instead of listing all the IDs and running a command on the result?

Counting Rows
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home2/itsyouma/public_html/staff/memberlist.php on line 6

<?php
$SQL_statement = "SELECT * FROM users";
$resultset = mysql_query($SQL_statement);
$count_rows = mysql_num_rows($resultset);
?>