To convert the interval in seconds to other units, perform the appropriate arithmetic operation. : UNIX_TIMESTAMP « Date Time « SQL / MySQL






  1. SQL / MySQL
  2. Date Time
  3. UNIX_TIMESTAMP

To convert the interval in seconds to other units, perform the appropriate arithmetic operation.

    
mysql>
mysql> SET @interval = UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @interval AS seconds,
    -> @interval / 60 AS minutes,
    -> @interval / (60 * 60) AS hours,
    -> @interval / (24 * 60 * 60) AS days,
    -> @interval / (7 * 24 * 60 * 60) AS weeks;
+---------+------------+----------+---------+--------+
| seconds | minutes    | hours    | days    | weeks  |
+---------+------------+----------+---------+--------+
| 1209600 | 20160.0000 | 336.0000 | 14.0000 | 2.0000 |
+---------+------------+----------+---------+--------+
1 row in set (0.00 sec)

mysql>
mysql>








Related examples in the same category

1.UNIX_TIMESTAMP calculation
2.UNIX_TIMESTAMP( ) can convert DATE values to seconds.
3.Converting Between Date-and-Time Values and Seconds
4.The number of seconds between dates that lie two weeks apart can be computed like this: