Page 1 of 2

Calculating Dates in MySQL

Posted: Fri Oct 01, 2004 4:24 am
by Harlequin
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...?

Posted: Fri Oct 01, 2004 4:30 am
by AGISB
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)

Posted: Fri Oct 01, 2004 4:31 am
by feyd
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

mysql.com

Posted: Fri Oct 01, 2004 4:37 am
by phpScott
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.
DATEDIFF(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.
and see if that helps

Posted: Fri Oct 01, 2004 4:42 am
by Harlequin
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.

Posted: Fri Oct 01, 2004 4:43 am
by Harlequin
Scott.

I think that might be exactly what I need.

then I can simply say if the datediif > 7 do something yeah...?

Posted: Fri Oct 01, 2004 4:44 am
by feyd
look at the forum link I posted.. it has workings for a week ago kinda stuff.

yes

Posted: Fri Oct 01, 2004 4:45 am
by phpScott
that would be it.
I know you just needed a push in the right dirrection. :D

Posted: Fri Oct 01, 2004 4:45 am
by Harlequin
Actually Scott.

After reading that section of the manual it's exactly what I want as it doesn't include the time in a date/time field value.

Excellent...! :D

Posted: Fri Oct 01, 2004 5:33 am
by Harlequin
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...?

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]

Posted: Fri Oct 01, 2004 5:56 am
by Jean-Yves
At a guess (I haven't checked), you need to take the vars out of the single quotes, since you want to compare dates not strings.

Posted: Fri Oct 01, 2004 6:37 am
by phpScott
i believe you can actually do

Code: Select all

<?php
$Query08 = "SELECT id FROM tableName WHERE DATEDIFF($Now,$RegistrationDate)> 7"; 
?>
where you should be able then to just get the list of people who are greater then 7 days.

Posted: Fri Oct 01, 2004 6:39 am
by Harlequin
Thanks Jean, Scott :)

Posted: Fri Oct 01, 2004 8:29 am
by Harlequin
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 get the following error:
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
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.
Any ideas...?


feyd | start using

Code: Select all

tags, or moderators will get very cranky![/color]

sorry

Posted: Fri Oct 01, 2004 9:05 am
by phpScott
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.

Code: Select all

SELECT id
FROM storedate
WHERE DATE_SUB( CURDATE(  ) ,  INTERVAL 7 DAY  )  > lastDate
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.