Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

USING MYSQL TO FORMULATE DATE INTERVALS - PAST AND FUTURE DATES

PHP & MySQL

USING MYSQL TO FORMULATE DATE INTERVALS - PAST AND FUTURE DATES

by  inlandpac  Posted    (Edited  )
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.

[sup][color blue]
Code: 
/* 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>";
[/color][/sup]

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

[color red]/**********************************************/[/color]​
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:
[color #006600][sup]
Code: 
create table dateadd(date datetime not null default '0000-00-00 00:00:00');

INSERT INTO dateadd VALUES (NOW());
[/sup][/color]

Now, we will formulate tomorrow's date and the date 1 year from now using the date and time we just inserted.
[color #006600][sup]
Code: 
SELECT `date` as `start`,
DATE_ADD(`date`,INTERVAL 1 DAY) as `tomorrow`,
DATE_ADD(`date`,INTERVAL 1 YEAR) as `end` FROM `dateadd` WHERE 1
[/sup][/color]

After executing, our result would be:
[color #330000][sup]
Code: 
start                   tomorrow                end
2001-09-04 02:54:21     2001-09-05 02:54:21     2002-09-04 02:54:21
[/sup][/color]

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!

;-)
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top