PHP MYSQL - DATE and TIME functions

TIMEDIFF( )

Synopsis

TIMEDIFF(time, time)

This function returns the time difference between the two times given. Although the arguments may be given in time or date-and-time format, both arguments must be of the same datatype. This function is available as of Version 4.1.1 of MySQL.

SELECT appointment AS Appointment, NOW( ) AS Now,
       TIMEDIFF(appointment, NOW( )) AS 'Time Remaining'
   FROM appointments
   WHERE rec_id='3783';
+--------------------+--------------------+----------------+
| Appointment        | Now                | Time Remaining |
+--------------------+--------------------+----------------+
| 2005-01-11 10:30:00| 2005-01-11 22:28:09| 12:01:51       |
+--------------------+--------------------+----------------+

TIME( )

Synopsis

TIME(time)

This function returns the time from a given string or column containing date and time data. This function is available as of Version 4.1.1 of MySQL.

SELECT TIME(NOW( )) , NOW( );
+-------------+---------------------+
| TIME(NOW( )) | NOW( )                |
+-------------+---------------------+
| 21:14:20    | 2005-01-11 21:14:20 |
+-------------+---------------------+

SYSDATE( )

Synopsis

SYSDATE( )

This function returns the system date. It will return the date and time in ayyyy-mm-dd hh:mm:ss format, but will return the data in a yyyymmddformat if it's used as part of a numeric calculation. This function is an alias for the NOW( ) function.

SELECT SYSDATE( );
+---------------------+
| SYSDATE( )           |
+---------------------+
| 2004-05-09 18:44:51 |
+---------------------+

SUBTIME( )

Synopsis

SUBTIME(datetime, datetime)

This function returns the date and time for the given string or column, decreased by the time given as the second argument (d hh:mm:ss). If a negative number is given, the time is added and the function is the equivalent of ADDTIME(). This function is available as of Version 4.1.1 of MySQL.

SELECT NOW( ) AS Now,
       SUBTIME(NOW( ), '1:00:00.00') AS 'Hour Ago';
+---------------------+---------------------+
| Now                 | Hour Ago            |
+---------------------+---------------------+
| 2005-01-12 00:54:59 | 2005-01-11 23:54:59 |
+---------------------+---------------------+

Notice that the hour was decreased by one, and because the time is just after midnight, the function causes the date to be altered by one day, as well. To decrease the date, add the number of days before the time (separated by a space) like so:

 SELECT NOW( ) AS Now,
        SUBTIME(NOW( ), '30 0:0.0') AS 'Thirty Days Ago';
+---------------------+---------------------+
| Now                 | Thirty Days Ago     |
+---------------------+---------------------+
| 2005-01-12 00:57:04 | 2004-12-13 00:57:04 |
+---------------------+---------------------+

SUBDATE( )

Synopsis

SUBDATE(date, INTERVAL value type)

Use this function to subtract a time interval from the results of a date or timedatatype column. If a negative value is given, the interval is added and is equivalent to the ADDDATE( ) function. This is an alias for the DATE_SUB( )function. See DATE_ADD( ) for a table of incremental types.

SELECT SUBDATE(NOW( ), INTERVAL 1 DAY)
          AS 'Yesterday',
       SUBDATE(NOW( ), INTERVAL -1 DAY)
          AS 'Tomorrow';
+---------------------+---------------------+
| Yesterday           | Tomorrow            |
+---------------------+---------------------+
| 2004-05-09 16:11:56 | 2004-05-11 16:11:56 |
+---------------------+---------------------+

As of Version 4.1 of MySQL, for subtracting days the second argument of the function may simply be the number of days (i.e., just 1 instead of INTERVAL 1DAY).

TIMESTAMPDIFF( )

TIMESTAMPDIFF(interval, datetime, datetime)

This function returns the time difference between the two times given but only for the interval being compared. The intervals accepted are the same as those for the TIMESTAMPADD( ) function. This function is available as of Version 5 of MySQL.

SELECT NOW( ) AS Today,
       TIMESTAMPDIFF(DAY, NOW( ), LAST_DAY(NOW( )))
          AS 'Days Remaining in Month';
+---------------------+-------------------------+
| Today               | Days Remaining in Month |
+---------------------+-------------------------+
| 2016-02-24 07:03:41 | 4                       |
+---------------------+-------------------------+

TIMESTAMP( )

Synopsis

TIMESTAMP(date, time)

This function returns date and time (in yyyy-mm-dd hh:mm:ss format) from a given string or column containing date and time data, respectively. If only the date or only the time is given, the function will return zeros for the missing parameters. This function is available as of Version 4.1.1 of MySQL.

SELECT TIMESTAMP(appt_date, appt_time)
   FROM appointments LIMIT 1;
+---------------------------------+
| TIMESTAMP(appt_date, appt_time) |
+---------------------------------+
| 2005-01-15 10:30:00             |
+---------------------------------+