phpmyadmin Question posted in
The official documentation can be found here.

MySQL 5.7 Timestamp field showing Datetime? – Phpmyadmin

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
enter image description here

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…)
enter image description here

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

Tags:

3

Answers


  1. 0
    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
    Login or Signup to reply.
  2. 0

    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 |
+---------------------+---------------------+------------+------------+------------+
    Login or Signup to reply.
  3. 0

    Since MySQL 5.6.4, the DATETIME field requires 5 bytes + 3 bytes fractional. The TIMESTAMP type requires 4 bytes + 3 bytes fractional. Neither of these data types store time zone information. However, both MySQL and phpMyAdmin display TIMESTAMP fields according to the timezone of the database server. You can retrieve the database server’s timezone info with the following statements:

    SELECT @@global.time_zone, @@session.time_zone;
SELECT EXTRACT(HOUR FROM (TIMEDIFF(NOW(), UTC_TIMESTAMP))) AS `timezone`

    If you would like phpMyAdmin to display a different timezone from the database server, you can set the SessionTimeZone property inside of phpMyAdmin’s config.inc.php file.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search