Converting Between Date-and-Time Values and Seconds

     
mysql>
mysql> CREATE TABLE datetime_val
    -> (
    ->  dt      DATETIME
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> INSERT INTO datetime_val (dt) VALUES('1970-01-01 00:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO datetime_val (dt) VALUES('1987-03-05 12:30:15');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO datetime_val (dt) VALUES('1999-12-31 09:00:00');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO datetime_val (dt) VALUES('2000-06-04 15:45:30');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM datetime_val;
+---------------------+
| dt                  |
+---------------------+
| 1970-01-01 00:00:00 |
| 1987-03-05 12:30:15 |
| 1999-12-31 09:00:00 |
| 2000-06-04 15:45:30 |
+---------------------+
4 rows in set (0.00 sec)

mysql>
mysql>
mysql> SELECT dt,
    -> UNIX_TIMESTAMP(dt) AS seconds,
    -> FROM_UNIXTIME(UNIX_TIMESTAMP(dt)) AS timestamp
    -> FROM datetime_val;
+---------------------+-----------+---------------------+
| dt                  | seconds   | timestamp           |
+---------------------+-----------+---------------------+
| 1970-01-01 00:00:00 |     28800 | 1970-01-01 00:00:00 |
| 1987-03-05 12:30:15 | 541974615 | 1987-03-05 12:30:15 |
| 1999-12-31 09:00:00 | 946659600 | 1999-12-31 09:00:00 |
| 2000-06-04 15:45:30 | 960158730 | 2000-06-04 15:45:30 |
+---------------------+-----------+---------------------+
4 rows in set (0.00 sec)

mysql>
mysql> drop table datetime_val;
Query OK, 0 rows affected (0.00 sec)

Related examples in the same category

1.	UNIX_TIMESTAMP calculation
2.	UNIX_TIMESTAMP( ) can convert DATE values to seconds.
3.	The number of seconds between dates that lie two weeks apart can be computed like this:
4.	To convert the interval in seconds to other units, perform the appropriate arithmetic operation.

Converting Between Date-and-Time Values and Seconds : UNIX_TIMESTAMP « Date Time « SQL / MySQL

Related examples in the same category