EmergencyEMERGENCY? Get 24/7 Help Now!

Recovering temporal types in MySQL 5.6: TIME, TIMESTAMP and DATETIME

 | July 16, 2013 |  Posted In: Insight for DBAs, MySQL


MySQL 5.6 introduces a new feature – microseconds resolution in some temporal types. As of 5.6.4 TIME, TIMESTAMP and DATETIME can have a fractional part. To create a field with subseconds you can specify precision in brackets: TIME(3), DATETIME(6) etc.

Obviously, the new feature requires the format change. All three types may now have a tail with a fractional part. The tail may be one, two or three bytes long and can store up to six digits after the point.

The non-fractional part has changed too. Thus, DATETIME uses only five bytes comparing to eight in previous versions.

As of revision 79 the recovery toolkit supports the new format.

Let’s recover a sample table to see how it works. The table is

First, we should gerenrate a table definition file:

Then recompile the tool and split a table space that contains records of the table t:

The next step is to get records from the InnoDB index:

To load this dump use the LOAD DATA INFILE command that’s generated by the contraints_parser, I then redirected it to /dev/null in the example above.

As you can see it’s pretty straightforward. There are two notes:

  • Because of format differences the tool can detect whether the field is in the new format or old
  • For the TIME field (w/o fractional part) in the new format you need to give a hint to contraints_parser. This is -6 option. Let me illustrate this

Before 5.6.4 TIME was packed in three bytes: DD×24×3600 + HH×3600 + MM×60 + SS. As of 5.6.4 it still uses three bytes, but format is different:

TIME new format 

bits description values
1 sign 1= non-negative, 0= negative
1 unused reserved for future extensions
10 hour 0-838
6 minute 0-59
6 second 0-59

If a field is created without a fractional part it’s impossible to determite the format from a field value. Let’s take a table:

If there is no fractional part constraints_parser assumes old format. So if we try to recover the the records from the table above the result will be wrong:

Thus, we need to give a hint, then TIME values are correct:

Aleksandr Kuzminsky

Aleksandr is a consultant and data recovery specialist. He is a former Percona employee.


  • It is not that obvious that this new feature requires a format change. In fact, microseconds were implemented before MySQL5.6 , in MariaDB 5.3, and this did not require backward-incompatible changes in data representation.

  • I’m migrating to 5.6 from 5.0 and I have a lot of tables with TIME/TIMESTAMP/DATETIME, but none of them with (number) on their definition.
    Do I have to worry? (I’m doing a non-binary upgrade, that is, dumping and load the dump in 5.6)

Leave a Reply


Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.