Calculating Dates in MySQL
Moderator: General Moderators
Calculating Dates in MySQL
Morning guys.
I've hit a brick wall here with my knowledge (which admittedly is limited). I want to calculate the difference netween two dates, both following the Date/Time format of: YYYY-MM-DD HH:MM:SS but only want to know if there is more than 7 days between the two and can't seem to find any good tutorials or source code out on the web.
Can anyone help with this...?
I've hit a brick wall here with my knowledge (which admittedly is limited). I want to calculate the difference netween two dates, both following the Date/Time format of: YYYY-MM-DD HH:MM:SS but only want to know if there is more than 7 days between the two and can't seem to find any good tutorials or source code out on the web.
Can anyone help with this...?
You can explode the date into an array and then use the mktime funktion to create a unix timestamp
Then simply substract 60*60*24*7 and see if it is smaller than the comparison date (timestamp)
Then simply substract 60*60*24*7 and see if it is smaller than the comparison date (timestamp)
Last edited by AGISB on Fri Oct 01, 2004 4:31 am, edited 1 time in total.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
what version of mysql are you using?
if 4.1.1 or later, you have access to [mysql_man]DATEDIFF[/mysql_man]().
there's also [mysql_man]TO_DAYS[/mysql_man], and of course [mysql_man]UNIX_TIMESTAMP[/mysql_man]()
this may help:
viewtopic.php?t=25232&highlight=week
if 4.1.1 or later, you have access to [mysql_man]DATEDIFF[/mysql_man]().
there's also [mysql_man]TO_DAYS[/mysql_man], and of course [mysql_man]UNIX_TIMESTAMP[/mysql_man]()
this may help:
viewtopic.php?t=25232&highlight=week
mysql.com
i'm not sure if you want the db to do the work for you or not but mysql had date and time functions built in that could help you.
[mysql_man]Date+and+Time+Functions&charset=iso-8859-1[/mysql_man]
and look for dateDiff on the first link.
[mysql_man]Date+and+Time+Functions&charset=iso-8859-1[/mysql_man]
and look for dateDiff on the first link.
and see if that helpsDATEDIFF(expr,expr2)
DATEDIFF() returns the number of days between the start date expr and the end date expr2. expr and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.
mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
-> 1
mysql> SELECT DATEDIFF('1997-11-30 23:59:59','1997-12-31');
-> -31
DATEDIFF() was added in MySQL 4.1.1.
Feyd
I'm using V 4.0.20.
I'm thinking (after reading the manual) that the "TO_DAYS" might be what I am looking for. For example:
A user registers on $DateX and I know that "now ()" is $DateY. All i need to do now is calculate $DateY-$DateX and if the difference is greater than 7 days do something.
It's the syntax that's getting me. I've managed to do loads of stuff on calculations just using the manual up to now but I'm at a real loss here.
I'm using V 4.0.20.
I'm thinking (after reading the manual) that the "TO_DAYS" might be what I am looking for. For example:
A user registers on $DateX and I know that "now ()" is $DateY. All i need to do now is calculate $DateY-$DateX and if the difference is greater than 7 days do something.
It's the syntax that's getting me. I've managed to do loads of stuff on calculations just using the manual up to now but I'm at a real loss here.
Guys.
Thanks for all your help on this. Just one final thing (I hope).
The following query produces a syntax error at the line I've marked. Any ideas...?
feyd | use the freaking
Thanks for all your help on this. Just one final thing (I hope).
The following query produces a syntax error at the line I've marked. Any ideas...?
Code: Select all
// Print Dormant Users Older Than 1 Week:
$Query06 = "SELECT * FROM MembersData
WHERE IDStatus = ''
ORDER BY UserRegistered";
$RecordCount06 = mysql_query($Query06) or die("Error 06: " . mysql_error());
$VeryDormant = mysql_result($RecordCount06, 0);
while($row = mysql_fetch_array($RecordCount06))
{
$UserID = $row["UserID"];
$RegistrationDate = $row["UserRegistered"];
$UserMail = $row["UserMail"];
// Create a date:
$Query07 = "SELECT now()";
$Date07 = mysql_query($Query07) or die("Error 07: " . mysql_error());
$Now = mysql_result($Date07, 0);
echo "<br><br>Now: {$Now}";
// Select Dormant Users Older Than 7 Days:
Error--->> $Query08 = "SELECT DATEDIFF('$Now','$RegistrationDate')";
$RecordCount08 = mysql_query($Query08) or die("Error 08: " . mysql_error());
$VeryDormantUsers = mysql_result($RecordCount08, 0);
}feyd | use the freaking
Code: Select all
tags![/color]i believe you can actually do
where you should be able then to just get the list of people who are greater then 7 days.
Code: Select all
<?php
$Query08 = "SELECT id FROM tableName WHERE DATEDIFF($Now,$RegistrationDate)> 7";
?>feyd | start using
I get the following error:
Any ideas...?
feyd | start using
Code: Select all
tags, or moderators will get very cranky![/color]
Still getting that syntax error guys - can't seem to locate what's the isuse.
Here's the new queries:Code: Select all
// Select New Users Who Haven't Created a Profile:
$Query06 = "SELECT * FROM MembersData
WHERE IDStatus = ''
ORDER BY UserRegistered";
$RecordCount06 = mysql_query($Query06) or die("Error 06: " . mysql_error());
$VeryDormant = mysql_result($RecordCount06, 0);
while($row = mysql_fetch_array($RecordCount06))
{
$UserID = $row["UserID"];
$RegistrationDate = $row["UserRegistered"];
$UserMail = $row["UserMail"];
}
// Create a date:
$Query07 = "SELECT now()";
$Date07 = mysql_query($Query07) or die("Error 07: " . mysql_error());
$Now = mysql_result($Date07, 0);
// Select Dormant Users Older Than 7 Days:
$Query08 = "SELECT * FROM MembersData
WHERE DATEDIFF($Now,$RegistrationDate)> 7";
$RecordCount08 = mysql_query($Query08) or die("Error 08: " . mysql_error());
$VeryDormantUsers = mysql_result($RecordCount08, 0);I moved the closing "While" brace back to it's original position as I thought this might be the cause but it just returns the last record entry from the looks of things which I can sort out later when I construct the table.Error 08: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(2004-10-01 14:25:14,2004-10-01 12:33:06)> 7' at line 2
Any ideas...?
feyd | start using
Code: Select all
tags, or moderators will get very cranky![/color]sorry
I thought I had used DATEDIFF in the past but is says that it is was only added in version 4.1.2 and I just checked and the latest stable release from mysql is 4.0.21.
I did do a little more checking and found this that seems to work on my silly test db I created as I was getting the same error msg.
as a query which returned the results I was looking for.
but I was using a date field for my lastDate field.
hopefully that works.
I did do a little more checking and found this that seems to work on my silly test db I created as I was getting the same error msg.
Code: Select all
SELECT id
FROM storedate
WHERE DATE_SUB( CURDATE( ) , INTERVAL 7 DAY ) > lastDatebut I was using a date field for my lastDate field.
hopefully that works.