What is the difference between UNIX TIMESTAMPS and MySQL TIMESTAMPS?

MySQLMySQLi Database

In MySQL, UNIX TIMESTAMPS are stored as 32-bit integers. On the other hand MySQL TIMESTAMPS are also stored in similar manner but represented in readable YYYY-MM-DD HH:MM:SS format.

Example

mysql> Select UNIX_TIMESTAMP('2017-09-25 02:05:45') AS 'UNIXTIMESTAMP VALUE';
+---------------------+
| UNIXTIMESTAMP VALUE |
+---------------------+
| 1506285345          |
+---------------------+
1 row in set (0.00 sec)

The query above shows that UNIX TIMESTAMPS values are stored as 32 bit integers whose range is same as MySQL INTEGER data type range.

mysql> Select FROM_UNIXTIME(1506283345) AS 'MySQLTIMESTAMP VALUE';
+----------------------+
| MySQLTIMESTAMP VALUE |
+----------------------+
| 2017-09-25 01:32:25  |
+----------------------+
1 row in set (0.00 sec)

The query above shows that MySQL TIMESTAMPS values are also stored as 32 bit integers, but in a readable format, whose range is same as MySQL TIMESTAMP data type range.

raja
Published on 19-Feb-2018 15:50:28
Advertisements