Different output for UNIX_TIMESTAMP between mysql 5.7 and mysql 8

user_1541784 0
Viewed 86

I have a table defined as follow:

CREATE TABLE IF NOT EXISTS `user_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` DATETIME NOT NULL,
  ...
  PRIMARY KEY (`id`),
  INDEX `date` (`date` ASC))
ENGINE = InnoDB

When I run:

SELECT date, date_format(date,'%Y-%m-%d %H'), UNIX_TIMESTAMP(date_format(date,'%Y-%m-%d %H')) AS timestamp FROM user_logs ;

On Percona 5.7.35-38 :

date,"date_format(date,'%Y-%m-%d %H')",timestamp
"2020-12-20 16:42:27","2020-12-20 16",1608480000
"2021-03-12 21:23:56","2021-03-12 21",1615582800
"2021-03-14 10:57:41","2021-03-14 10",1615716000
"2021-03-14 10:57:52","2021-03-14 10",1615716000
"2021-03-18 23:36:55","2021-03-18 23",1616108400

On Percona 8.0.28-19.1 :

date,"date_format(date,'%Y-%m-%d %H')",timestamp
"2020-12-20 16:42:27","2020-12-20 16",1608480000.000000
"2021-03-12 21:23:56","2021-03-12 21",1615582800.000000
"2021-03-14 10:57:41","2021-03-14 10",1615716000.000000
"2021-03-14 10:57:52","2021-03-14 10",1615716000.000000
"2021-03-18 23:36:55","2021-03-18 23",1616108400.000000

Any reason why in 8 I get the timestamp with decimals ? I tested also date_format(date,'%Y-%m-%d') and it's the same. If I use DATE(date) it's fine .. but I need also the hour into my scenario. I mean the timestamp from date but without minutes and seconds.

Silviu

timestamp mysql
1 Answers

MySQL 8.0.28 made some changes to support 64-bit values for UNIX_TIMESTAMP().

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html#mysqld-8-0-28-feature

  • The functions FROM_UNIXTIME(), UNIX_TIMESTAMP(), and CONVERT_TZ() now handle 64-bit values on platforms that support them, including 64-bit versions of Linux, MacOS, and Windows.

Apparently they also default to report fractional seconds, even if your DATETIME column isn't configured to have fractional seconds.

I tested casting the result to a DECIMAL:

mysql> SELECT date, CAST(UNIX_TIMESTAMP(date) AS decimal) AS timestamp FROM user_logs ;
+---------------------+------------+
| date                | timestamp  |
+---------------------+------------+
| 2022-09-04 08:26:36 | 1662305196 |
| 2040-12-31 00:00:00 | 2240553600 |
+---------------------+------------+

Notice I added a value that is past the end of the epoch (2038), to demonstrate that it supports larger date values.

There's no need to use date_format() in this expression as you were doing.

Bill_Karwin_208600
Related