Issues with Inserting Date Types in TiDB

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

Original topic: 关于tidb插入日期类型问题

| username: zhanggame1

【TiDB Usage Environment】Production Environment
【TiDB Version】7.1
I have a question. If a datetime type field is set to Current_timestamp, where does the time source come from?
If it is within a transaction, is the time taken at the beginning of the transaction or at the time of commit?

| username: 昵称想不起来了 | Original post link

Previously, an expert conducted tests based on TiDB General log records. It seems to be at the beginning.
Link: Multiple Ways to Tell You How to Calculate the Latency Time of DM Synchronizing Data to TiDB

| username: zhanggame1 | Original post link

I couldn’t see where the start time is written.

| username: 昵称想不起来了 | Original post link

Regarding this TiDB binlog

| username: 昵称想不起来了 | Original post link

:joy: Not sure if I understood it correctly, but it seems like the time is before the commit.

| username: tidb菜鸟一只 | Original post link

The transaction start time, just do a simple test to know.
SET autocommit=0;

SELECT CURRENT_TIMESTAMP;
INSERT INTO t_time(id) VALUES(1);

#Wait for 1 minute

SELECT CURRENT_TIMESTAMP;

COMMIT;

SELECT * FROM t_time;

| username: Miracle | Original post link

It should be the actual time when current_timestamp is referenced.

begin;
SELECT CURRENT_TIMESTAMP;
select sleep(5);
INSERT INTO t_time(id) VALUES(1);
select sleep(5);
SELECT CURRENT_TIMESTAMP;
commit;
select * from t_time;

| username: xfworld | Original post link

Natural time comes from the operating system and hardware… Make sure to synchronize the time…

TiUP installation generally requires time synchronization… :slightly_smiling_face:

| username: cassblanca | Original post link

To know the specific time, you need to understand the TiDB Percolator 2PC process. The Commit TS uses a calculation method similar to HLC:

  1. TiKV locally stores a max_ts. When TiDB accesses TiKV with start_ts, max_ts = max(max_ts, start_ts). max_ts caches the current maximum start_ts accessed.
  2. TiDB caches the current transaction’s min_commit_ts (initialized as start_ts + 1) and passes it to TiKV during prewrite.
  3. When TiKV receives the prewrite request, it compares prewrite.min_commit_ts with max_ts and returns an elevated value prewriteResp.MinCommitTs = max(prewrite.min_commit_ts, max_ts + 1).
  4. TiDB receives the prewrite request result and updates min_commit_ts = max(min_commit_ts, prewriteResp.MinCommitTs).

The final min_commit_ts will be used as the transaction’s Commit TS.

Column - TiDB 5.0 Two-Phase Commit | TiDB Community 302 course, Gold Lecturer Dong Fei explains it very clearly.

| username: zhanggame1 | Original post link

What is the relationship between ts and CURRENT_TIMESTAMP?

| username: zhanggame1 | Original post link

create table t_time(t datetime);
begin;
SELECT CURRENT_TIMESTAMP;
select sleep(5);
INSERT INTO t_time VALUES(CURRENT_TIMESTAMP);
select sleep(5);
INSERT INTO t_time VALUES(CURRENT_TIMESTAMP);
SELECT CURRENT_TIMESTAMP;
commit;
select * from t_time;

| username: cassblanca | Original post link

CURRENT_TIMESTAMP is the hardware clock obtained when the statement is executed. TS is the transaction time with certain internal algorithms, which essentially should also be obtained from the hardware clock.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.