Question about TIMESTAMP automatic initialization

Off-Topic
1

Please look at the following:

memsql> show partitions;
+---------+-----------+------+--------+--------+
| Ordinal | Host      | Port | Role   | Locked |
+---------+-----------+------+--------+--------+
|       0 | 127.0.0.1 | 3307 | Master |      0 |
|       1 | 127.0.0.1 | 3307 | Master |      0 |
...
|      19 | 127.0.0.1 | 3307 | Master |      0 |
+---------+-----------+------+--------+--------+
20 rows in set (0.00 sec)

memsql> drop table test_table;
Query OK, 0 rows affected (0.02 sec)

memsql> CREATE TABLE test_table (
    ->         idx int,
    ->         name char(20),
    ->         in_ts timestamp(6) DEFAULT CURRENT_TIMESTAMP,
    ->         SHARD KEY(idx),
    ->         KEY (idx) USING CLUSTERED COLUMNSTORE
    -> );
Query OK, 0 rows affected, 1 warning (0.03 sec)

memsql> select now(6);
+----------------------------+
| now(6)                     |
+----------------------------+
| 2019-12-20 20:06:30.301243 |
+----------------------------+
1 row in set (0.00 sec)

memsql> load data infile '/home/madamgold/test'
    -> INTO TABLE `test_table` (idx, name)
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n';
Query OK, 20000000 rows affected (2.87 sec)

memsql> select now(6);
+----------------------------+
| now(6)                     |
+----------------------------+
| 2019-12-20 20:06:33.175176 |
+----------------------------+
1 row in set (0.00 sec)

memsql> select distinct(in_ts) from test_table;
+----------------------------+
| in_ts                      |
+----------------------------+
| 2019-12-20 20:06:30.370743 |
| 2019-12-20 20:06:30.370713 |
...
| 2019-12-20 20:06:30.370978 |
+----------------------------+
20 rows in set (0.11 sec)

The automatically initialized in_ts timetamp is the start time of the load data.
Can record at the time the load completes (ie. finish time)?

Thank you.

2

Hello kyoungho.kum

The column in_ts has a default current_timestamp and the column will have a value which is when the actual insert happened. That is seen from your output because each row does have a different value.

Since the column has a default, there is no way for you to insert an end timestamp value for it.

Can you run this query and verify when the last row was inserted?
select max(in_ts) from test_table;

Thanks,

3

Thank you.
Happy new year!!

I understand the following:
“there is no way for you to insert an end timestamp value for it.”

For your reference, attach our results.

memsql> select distinct(in_ts) from test_table order by 1;
+----------------------------+
| in_ts                      |
+----------------------------+
| 2019-12-20 20:06:30.370615 |
| 2019-12-20 20:06:30.370641 |
| 2019-12-20 20:06:30.370685 |
| 2019-12-20 20:06:30.370713 |
| 2019-12-20 20:06:30.370743 |
| 2019-12-20 20:06:30.370765 |
| 2019-12-20 20:06:30.370792 |
| 2019-12-20 20:06:30.370820 |
| 2019-12-20 20:06:30.370854 |
| 2019-12-20 20:06:30.370884 |
| 2019-12-20 20:06:30.370914 |
| 2019-12-20 20:06:30.370959 |
| 2019-12-20 20:06:30.370978 |
| 2019-12-20 20:06:30.371024 |
| 2019-12-20 20:06:30.371071 |
| 2019-12-20 20:06:30.371102 |
| 2019-12-20 20:06:30.371136 |
| 2019-12-20 20:06:30.371189 |
| 2019-12-20 20:06:30.371219 |
| 2019-12-20 20:06:30.371259 |
+----------------------------+
20 rows in set (0.13 sec)
4

Problem with timestamp is, it cannot persist data before 1968 and after 2038. So we started using datetime(6). But this cannot be defaulted , it will be great if datetime enhanced to support to default value.

1 Like