Database Reference
In-Depth Information
| 2000-06-04 15:45:30 | 960133530 | 2000-06-04 15:45:30 |
| 2017-03-16 12:30:15 | 1489667415 | 2017-03-16 12:30:15 |
+---------------------+------------+---------------------+
UNIX_TIMESTAMP() can convert DATE values to seconds, too. It treats such values as
having an implicit time-of-day part of 00:00:00 :
mysql> SELECT
-> CURDATE(),
-> UNIX_TIMESTAMP(CURDATE()),
-> FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE()))\G
*************************** 1. row ***************************
CURDATE(): 2014-02-20
UNIX_TIMESTAMP(CURDATE()): 1392876000
FROM_UNIXTIME(UNIX_TIMESTAMP(CURDATE())): 2014-02-20 00:00:00
6.11. Calculating Intervals Between Dates or Times
Problem
You want to know how long it is between two dates or times; that is, the interval between
them.
Solution
To calculate an interval, use one of the temporal-difference functions, or convert your
values to basic units and take the difference. The permitted functions depend on the
types of the values for which you want to know the interval.
Discussion
The following discussion shows several ways to perform interval calculations.
Calculating intervals with temporal-difference functions
To calculate an interval in days between two date values, use the DATEDIFF() function:
mysql> SET @d1 = '2010-01-01', @d2 = '2009-12-01';
mysql> SELECT DATEDIFF(@d1,@d2) AS 'd1 - d2', DATEDIFF(@d2,@d1) AS 'd2 - d1';
+---------+---------+
| d1 - d2 | d2 - d1 |
+---------+---------+
| 31 | -31 |
+---------+---------+
DATEDIFF() also works with date-and-time values, but ignores the time part. This makes
it suitable for producing day intervals for DATE , DATETIME , or TIMESTAMP values.
Search WWH ::




Custom Search