Converting TiDB TSO Timestamps to Natural Time

This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB TSO 时间戳转换为自然时间

| username: 麻烦是朋友

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Encountered Issues: Problem Phenomenon and Impact]
In daily use, it may be necessary to convert TSO into a readable date type. According to the official documentation, there are two ways to do this. Here they are for everyone’s convenience.

In TiDB, the Placement Driver (PD) acts as the TSO timestamp allocator, responsible for distributing timestamps to various components within the cluster. These timestamps are used to assign time markers for transactions and data. This allocation mechanism is crucial for enabling the Percolator model in TiDB. The Percolator model supports Multi-Version Concurrency Control (MVCC) and transaction management.

The following example shows how to get the current TSO in TiDB:

BEGIN; SET @ts := @@tidb_current_ts; ROLLBACK;
Query OK, 0 rows affected (0.0007 sec)
Query OK, 0 rows affected (0.0002 sec)
Query OK, 0 rows affected (0.0001 sec)

| @ts                |
| 443852055297916932 |
1 row in set (0.00 sec)

Note that since TSO timestamps are allocated per transaction, you need to get the timestamp from a transaction containing BEGIN; ...; ROLLBACK.

The TSO timestamp obtained from the above example is a decimal number. You can use the following SQL functions to parse the timestamp:

SELECT TIDB_PARSE_TSO(443852055297916932);
| TIDB_PARSE_TSO(443852055297916932) |
| 2023-08-27 20:33:41.687000         |
1 row in set (0.00 sec)
SELECT TIDB_PARSE_TSO_LOGICAL(443852055297916932);
| TIDB_PARSE_TSO_LOGICAL(443852055297916932) |
|                                          4 |
1 row in set (0.00 sec)

The following example shows the binary details of the TSO timestamp:

0000011000101000111000010001011110111000110111000000000000000100  ← This value is the binary form of 443852055297916932
0000011000101000111000010001011110111000110117                    ← The first 46 bits are the physical timestamp
                                              000000000000000100  ← The last 18 bits are the logical timestamp

The TSO timestamp consists of two parts:

  • Physical timestamp: UNIX timestamp since January 1, 1970, in milliseconds.
  • Logical timestamp: Incremental counter used in cases where multiple timestamps are needed within one millisecond or certain events may trigger clock process reversal. In these cases, the physical timestamp remains unchanged while the logical timestamp keeps incrementing. This mechanism ensures the integrity of the TSO timestamp, ensuring that timestamps always increment without rolling back.

You can further inspect the TSO timestamp through SQL statements, as shown in the example below:

SELECT @ts, UNIX_TIMESTAMP(NOW(6)), (@ts >> 18)/1000, FROM_UNIXTIME((@ts >> 18)/1000), NOW(6), @ts & 0x3FFFF\G
*************************** 1. row ***************************
                            @ts: 443852055297916932
         UNIX_TIMESTAMP(NOW(6)): 1693161835.502954
               (@ts >> 18)/1000: 1693161221.6870
FROM_UNIXTIME((@ts >> 18)/1000): 2023-08-27 20:33:41.6870
                         NOW(6): 2023-08-27 20:43:55.502954
                  @ts & 0x3FFFF: 4
1 row in set (0.00 sec)

The >> 18 operation represents a bitwise right shift by 18 bits, used to extract the physical timestamp. Since the physical timestamp is in milliseconds, different from the more common UNIX timestamp format in seconds, it needs to be divided by 1000 to convert it to a format compatible with FROM_UNIXTIME(). This conversion process is consistent with the functionality of TIDB_PARSE_TSO().

You can also extract the logical timestamp 000000000000000100 (i.e., 4 in decimal) from the binary.

You can also parse the timestamp through the CLI tool with the following command:

$ tiup ctl:v7.1.0 pd tso 443852055297916932
system:  2023-08-27 20:33:41.687 +0200 CEST
logic:   4

As you can see, the physical timestamp is in the line starting with system:, and the logical timestamp is in the line starting with logic:. The SQL function method cannot display all the physical details and requires separate function calls, while the CLI command method provides a comprehensive display and requires calling the command. If frequent use is needed, it is best to organize it into a script for convenient invocation.

| username: zhanggame1 | Original post link


| username: wangccsy | Original post link

Equivalent to a system function.

| username: 像风一样的男子 | Original post link

Unclear but feels impressive.

| username: 小龙虾爱大龙虾 | Original post link

It’s a good way to earn points. :joy_cat:

| username: forever | Original post link

That’s a bit blunt. :+1:

| username: ShawnYan | Original post link

Pretty good, next time you can mark the address of the referenced document.

| username: Kongdom | Original post link

:yum: Support practical sharing~

| username: 春风十里 | Original post link

Is this one-millisecond logical increment counter fixed and unchanging?

| username: dba远航 | Original post link

Well done!

| username: 烂番薯0 | Original post link

:wave: :wave: :wave:

| username: kelvin | Original post link

Learned, give it a thumbs up.