-- Enable the flashback query feature.
SET persist tdsql_enable_stale_read = ON;

SELECT ... FROM <table_name> [ AS OF {TIMESTAMP timestamp_option | GTS gts_option} ]

-- Create a test table.
CREATE TABLE t1(a INT PRIMARY KEY, b INT);

SELECT sleep(3);

-- Record the initial timestamp.
SELECT NOW() INTO @time0;
SELECT ((UNIX_TIMESTAMP(@time0) << 24) | 0xFFFFFF) INTO @gts0;

SELECT sleep(3);

-- Insert test data.
INSERT INTO t1 VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,1),(7,2),(8,3),(9,4),(10,5);

SELECT sleep(3);

-- Record the timestamp after data insertion.
SELECT NOW() INTO @time1;
SELECT ((UNIX_TIMESTAMP(@time1) << 24) | 0xFFFFFF) INTO @gts1;

SELECT sleep(3);

-- Update data.
UPDATE t1 SET b=b*10;

-- Query for current data
SELECT * FROM t1 ORDER BY a;
a»b
1»10
2»20
3»30
4»40
5»50
6»10
7»20
8»30
9»40
10»■50

-- Flashback query to the initial state (empty table).
SELECT * FROM t1 ORDER BY a AS OF TIMESTAMP @time0;
a»b
SELECT * FROM t1 ORDER BY a AS OF GTS @gts0;
a»b

-- Flashback query to the state after data insertion.
SELECT * FROM t1 ORDER BY a AS OF TIMESTAMP @time1;
a»b
1»1
2»2
3»3
4»4
5»5
6»1
7»2
8»3
9»4
10»■5


SELECT * FROM t1 ORDER BY a AS OF GTS @gts1;
a»b
1»1
2»2
3»3
4»4
5»5
6»1
7»2
8»3
9»4
10»■5

-- 0: Leader node, 1: Follower node.
SET persist tdsql_stale_read_role=1;

-- Query current data.
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
+----+-----+

set tdsql_read_staleness_t='-10'; #Read data from 10 seconds ago.

-- Insert new data.
insert into t1 values (5,50);

-- Check the current time.
select now();
+---------------------+
| now()               |
+---------------------+
| 2025-02-16 17:57:50 |
+---------------------+

-- Query current data.
select * from t1; # Return historical data.
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
+----+-----+

-- Wait for some time.
-- Check the current time.
select now();
+---------------------+
| now()               |
+---------------------+
| 2025-02-16 17:58:01 |
+---------------------+

-- Query current data again.
select * from t1; # Return new data.
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
|  5 |  50 |
+----+-----+

-- Query current data.
select * from t1;
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
|  5 |  50 |
+----+-----+

-- Insert new data.
insert into t1 values (6,60);

-- Begin transaction.
begin;
select * from t1; # Return historical data.
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
|  5 |  50 |
+----+-----+
select sum(num) as total_num from t1;
+-----------+
| total_num |
+-----------+
|       150 |
+-----------+
rollback;

-- Wait for some time.
-- Begin transaction.
begin;
select * from t1; # Return new data.
+----+-----+
| id | num |
+----+-----+
|  1 |  10 |
|  2 |  20 |
|  3 |  30 |
|  4 |  40 |
|  5 |  50 |
|  6 |  60 |
+----+-----+
select sum(num) as total_num from t1;
+-----------+
| total_num |
+-----------+
|       210 |
+-----------+
rollback;