MySQL 5.7 Timestamp field showing Datetime?

Question

I have a column in my database called time. the type of this column is timestamp and Default value is CURRENT_TIMESTAMP

But after some inserts, in phpMyAdmin it shows the value as datetime, e.g. 2019-05-05 04:24:45 and even the Timezone is shown there and can be changed!

I thought MySQL's timestamp is 4 bytes (compared to 8 bytes of datetime) and doesn't store timezone and data is same as INT(10) such as: 1557094115 (seconds passed since 1970 or something like that)

Can any one please explain this, is it a bug or something?

MySQL version 5.7.25

Edit 1 (Screenshots):

It is a TIMESTAMP column, with default value of CURRENT_TIMESTAMP

As you see it is shown as DATETIME and I cannot compare it with integer value of unix_timestamp... also we can change TimeZone to any value (I thought timestamp doesn't store timezone...)

Edit 2:

If (based on one answer) MySQL stores it as an integer internally, then why can't I compare it with integers? (the following query won't work)

DELETE FROM `table` WHERE time < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL :days DAY))

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '1555980012' for column 'time' at row 1

I also tried it in Sequel Pro and MySQLWorkbench with same results

Answer 1

If you need to see 1557094115, then apply the function UNIX_TIMESTAMP() to the TIMESTAMP or DATETIME column. It's inverse is FROM_UNIXTIME().

mysql> SELECT UNIX_TIMESTAMP("2019-05-05 04:24:45"), FROM_UNIXTIME(1557055485);
+---------------------------------------+---------------------------+
| UNIX_TIMESTAMP("2019-05-05 04:24:45") | FROM_UNIXTIME(1557055485) |
+---------------------------------------+---------------------------+
|                            1557055485 | 2019-05-05 04:24:45       |
+---------------------------------------+---------------------------+

More

The internal storage for TIMESTAMP is 1557055485 in UTC; the timezone is added/removed as it is fetched/stored.

The internal storage for DATETIME is (logically, but not actually) the string "2019-05-05 04:24:45" with no hint of timezone. (Actually, it is packed into 5 bytes in some fashion.)

Without any conversion function, fetching TIMESTAMP and DATETIME look the same:

CREATE TABLE `dtts` (
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `dt` datetime DEFAULT NULL,
  `just_date` date NOT NULL,
  `di` int(11) DEFAULT NULL,
  `ts_int` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
1 row in set (0.00 sec)

mysql> select * from dtts
    -> ;
+---------------------+---------------------+------------+------------+------------+
| ts                  | dt                  | just_date  | di         | ts_int     |
+---------------------+---------------------+------------+------------+------------+
| 2017-06-26 17:52:53 | 2011-06-08 20:45:55 | 2011-06-08 |   20110608 | 1465404577 |
| 2017-06-26 17:52:53 | 2013-03-10 02:35:47 | 2013-03-10 |   20130310 | 1465404577 |

Adding NOW() to both, then SELECTing:

mysql> INSERT INTO dtts (ts, dt) VALUES (NOW(), NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)

| 2019-05-08 14:14:07 | 2019-05-08 14:14:07 | 0000-00-00 |       NULL |       NULL |
+---------------------+---------------------+------------+------------+------------+

Answer 2

1. DateTime doesn't store timezone information (it's value only), while MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and convert from UTC to the current time zone for retrieval. What you are seeing from PhpMyAdmin is the retrieved value, not stored value.
2. Since MySQL 5.6.4, the storage of DateTime has been improved from 8 bytes to 5 bytes (+ fractional seconds storage) Reference