×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

PHP & MySQL

USING MYSQL TO FORMULATE DATE INTERVALS - PAST AND FUTURE DATES by inlandpac
Posted: 4 Sep 01

What happens if I want to find out what the date is, say, 1 day or even 1 year from now?

In PHP, we use a combination of date(), strtotime(), and getdate() to formulate different combinations of lengths of time (such as tomorrow's date/time is .... and 1 year from now is ....).

Here is an example of using PHP:
The case - we have a user that has just registered on our site, but that user's registration will end in exactly 1 year.

/* let's mock MySQL DATETIME format */
$startdate = date("Y-m-d H:i:s");

/* start date converted */
$str_date_start = strtotime($startdate);

/* one year from start date */
$str_date_end = strtotime($startdate) + (60 * 60 * 24 * 365);

/* one day from start date */
$str_date_tomorrow = strtotime($startdate) + (60 * 60 * 24);

/* today */
$mystartdate_array = getdate($str_date_start);
echo "Start Date: ";
echo $mystartdate_array[weekday].", ";
echo $mystartdate_array[month]." ";
echo $mystartdate_array[mday].", ";
echo $mystartdate_array[year]." - ";
echo $mystartdate_array[mon]."/".$mystartdate_array[mday]."/".$mystartdate_array[year]." ";
echo $mystartdate_array[hours].":".$mystartdate_array[minutes].":".$mystartdate_array[seconds]."<br>";

/* tomorrow */
$mytomorrowdate_array = getdate($str_date_tomorrow);
echo "Tomorrow's Date: ";
echo $mytomorrowdate_array[weekday].", ";
echo $mytomorrowdate_array[month]." ";
echo $mytomorrowdate_array[mday].", ";
echo $mytomorrowdate_array[year]." - ";
echo $mytomorrowdate_array[mon]."/".$mytomorrowdate_array[mday]."/".$mytomorrowdate_array[year]." ";
echo $mytomorrowdate_array[hours].":".$mytomorrowdate_array[minutes].":".$mytomorrowdate_array[seconds]."<br>";

/* one year from now */
$myenddate_array = getdate($str_date_end);
echo "End Date: ";
echo $myenddate_array[weekday].", ";
echo $myenddate_array[month]." ";
echo $myenddate_array[mday].", ";
echo $myenddate_array[year]." - ";
echo $myenddate_array[mon]."/".$myenddate_array[mday]."/".$myenddate_array[year]." ";
echo $myenddate_array[hours].":".$myenddate_array[minutes].":".$myenddate_array[seconds]."<br>";


The above will display today's date and time, tomorrow's date and time, and the user's membership end date and time.

/**********************************************/

Here is the really nice way (and very efficient method) of utilizing MySQL to derive different dates in the future and past from an existing date.

First, we have our table that contains a valid date (in any valid MySQL date format).  It is always easiest to allow MySQL to formulate dates, so this column should be a column type of either DATE, TIME, DATETIME, TIMESTAMP, or YEAR.  (if you use TIMESTAMP, you will have to add one more function within the example I am providing here, but all-in-all, the SQL is still the same).  The preferred types to use are DATE and DATETIME since these already contain a valid YYYY-MM-DD format (DATETIME adds hh:mm:ss which will allow formulations of smaller future and past date/time computations such as, 'what will 1 minute from the value in my DATETIME column be for this user'.)

So for this example, we will have a test table with only one column.  And of course, we are going to use the most flexible column type -- DATETIME:
create table dateadd(date datetime not null default '0000-00-00 00:00:00');

INSERT INTO dateadd VALUES (NOW());


Now, we will formulate tomorrow's date and the date 1 year from now using the date and time we just inserted.
SELECT `date` as `start`,
DATE_ADD(`date`,INTERVAL 1 DAY) as `tomorrow`,
DATE_ADD(`date`,INTERVAL 1 YEAR) as `end` FROM `dateadd` WHERE 1


After executing, our result would be:
start                   tomorrow                end
2001-09-04 02:54:21     2001-09-05 02:54:21     2002-09-04 02:54:21


There are a large number of options available and using a combination of MySQL's functions and formulations, we can come up with sum very nice results -- one of which could even be the number of days remaining until the end date.

Here are the interval types available for DATE_ADD():

SECOND
     meaning: seconds
     value format: ss
MINUTE
     meaning: minutes
     value format: mm
HOUR
     meaning: hours
     value format: hh
DAY
     meaning: days
     value format: DD
MONTH
     meaning: months
     value format: MM
YEAR
     meaning: years
     value format: YY
MINUTE_SECOND
     meaning: minutes and seconds
     value format: mm:ss
HOUR_MINUTE
     meaning: hours and minutes
     value format: hh:mm
HOUR_SECOND
     meaning: hours, minutes, and seconds
     value format: hh:mm:ss
DAY_HOUR
     meaning: days and hours
     value format: DD hh
DAY_MINUTE
     meaning: days, hours, and minutes
     value format: DD hh:mm
DAY_SECOND
     meaning: days, hours, minutes, and seconds
     value format: DD hh:mm:ss
YEAR_MONTH
     meaning: years and months
     value format: YY-MM

Hope this helps and have fun!



Back to PHP FAQ Index
Back to PHP Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close