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

Internally everything is stored as numbers one way or another, even letters; this is how computers work. However you have told the DB to treat the values in a specific way, either as a DateTime value or as a specific point in time. How it is stored internally is irrelevant as long as it outputs it in a human readable format that you told it to be. Both answers below complete each other and paint the full picture of the difference and similarities between DateTime and Timestamp data types. — Dharman, May 09 '19 at 20:14
Then guess I have to use `int(10)` to store the actual timestamp — J. Doe, May 09 '19 at 21:03
Yes, you could store it as an integer, but the question is why? Storing it as a native MySQL type gives you more options, and for displaying you can pass it through `UNIX_TIMESTAMP()` to get the integer value of number of seconds since UNIX epoch. — Dharman, May 09 '19 at 21:06
I need to only compare if X seconds passed (for example 300 seconds since an event), this can be done easily on an int, but needs additional functions on a Datetime! even tho now I understand how they are stored, I'm still not sure why would they display timestamp as Datetime when there is an actual Datetime type available if some one needs that. — J. Doe, May 09 '19 at 21:22
[Should I use the datetime or timestamp data type in MySQL?](https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql) — Dharman, May 09 '19 at 21:30
[Datetime vs Timestamp field](https://stackoverflow.com/questions/42852404/datetime-vs-timestamp-field) — Dharman, May 09 '19 at 21:30
Possible duplicate: [TIMESTAMP vs. DATETIME MySQL](https://stackoverflow.com/questions/39552135/timestamp-vs-datetime-mysql) — Dharman, May 09 '19 at 21:33
Operations on Timstamp or DateTime are very easy e.g. `myDT + INTERVAL 300 Seconds > now()` — Dharman, May 09 '19 at 21:34
@Dharman - Get in the habit of putting the column on one side and the constant expression on the other so that the Optimizer has a chance of using an `INDEX`: `myDT > NOW() - INTERVAL 300 SECOND` — Rick James, Sep 28 '20 at 07:17

Rick James · Accepted Answer · 2019-05-08T21:15:59.907

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 |
+---------------------+---------------------+------------+------------+------------+

Very useful info, but it doesn't answer why a `timestamp` column doesn't store timestamp and stores Datetime instead? — J. Doe, May 08 '19 at 19:18
@J.Doe - It does store seconds from 1970 UTC. See addition to my Answer. — Rick James, May 08 '19 at 21:16
@J.Doe - You can't see what is under the covers. `DATETIME` stores what you feed it. `TIMESTAMP` stores UTC, converting to/from such. You can "see" the difference by asking your colleague in another timezone what he gets from a `SELECT`. — Rick James, Jan 18 '20 at 15:56

Jacob · Answer 2 · 2019-05-08T02:01:17.520

3

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.
Since MySQL 5.6.4, the storage of DateTime has been improved from 8 bytes to 5 bytes (+ fractional seconds storage) Reference

edited May 08 '19 at 02:01

answered May 08 '19 at 01:53

Jacob

1,776
14
11

Thanks for the useful info. I was not aware of those changes on MySQL 5.6.4... But this still doesn't say why timestamp column is shown as Datetime as seen on my screenshots (I edited the question to add them) – J. Doe May 08 '19 at 11:13
Integer is internally how mysql stored timestamp and datetime and it doesn't mean the UI will show you the number. My 1st point has answered your question. What you are dealing with is phpmyadmin which is just a php client. You chose the timezone for your selected value, but mysql will convert and stored as UTC value. – Jacob May 08 '19 at 11:28
If it is internally stored as integer, then why can't I compare it to integers? (the following query won't work) `DELETE FROM table WHERE time < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 DAY))` – J. Doe May 08 '19 at 19:20
This'internal' means deep inside mysql, not means you can use it as integer. You can read this from mysql document https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html – Jacob May 08 '19 at 23:34
If you want to compare integer values, you'll need to apply the unix_timestamp function to both operands. DELETE FROM table WHERE UNIX_TIMESTAMP(time) < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 2 DAY)) – itsben May 14 '19 at 15:32

itsben · Answer 3 · 2019-05-14T15:58:32.430

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.

MySQL 5.7 Timestamp field showing Datetime?

Edit 1 (Screenshots):

Edit 2:

3 Answers3