Calculating Dates in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Harlequin
Forum Commoner
Posts: 51
Joined: Tue Sep 21, 2004 10:51 am
Location: UK

Calculating Dates in MySQL

Post 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...?
AGISB
Forum Contributor
Posts: 422
Joined: Fri Jul 09, 2004 1:23 am

Post 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)
Last edited by AGISB on Fri Oct 01, 2004 4:31 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

mysql.com

Post 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
Harlequin
Forum Commoner
Posts: 51
Joined: Tue Sep 21, 2004 10:51 am
Location: UK

Post 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.
Harlequin
Forum Commoner
Posts: 51
Joined: Tue Sep 21, 2004 10:51 am
Location: UK

Post by Harlequin »

Scott.

I think that might be exactly what I need.

then I can simply say if the datediif > 7 do something yeah...?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

look at the forum link I posted.. it has workings for a week ago kinda stuff.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

yes

Post by phpScott »

that would be it.
I know you just needed a push in the right dirrection. :D
Harlequin
Forum Commoner
Posts: 51
Joined: Tue Sep 21, 2004 10:51 am
Location: UK

Post 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
Harlequin
Forum Commoner
Posts: 51
Joined: Tue Sep 21, 2004 10:51 am
Location: UK

Post 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]
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post 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.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post 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.
Harlequin
Forum Commoner
Posts: 51
Joined: Tue Sep 21, 2004 10:51 am
Location: UK

Post by Harlequin »

Thanks Jean, Scott :)
Harlequin
Forum Commoner
Posts: 51
Joined: Tue Sep 21, 2004 10:51 am
Location: UK

Post 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]
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

sorry

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