Create a computed/PERSISTED column with current date

Help
1

Hi There,

I want to create a table with computed column.
I wish to use current_date as value in one of my columns named ‘l_date’
but I am getting error. Here is my DDL:

create table comp_col(
id varchar(10),
name varchar(10),
l_date as current_date PERSISTED timestamp
);

ERROR:
ERROR 1825 ER_MEMSQL_COMPUTED_IS_TIMESTAMP: Cannot create computed column ‘l_date’ of type TIMESTAMP

Looking forward to hear from you.

Thanks,
Vishwajeet

2

Persisted computed columns have to be set to deterministic expressions of one or more other columns. Using the current time in a computed column expression is non-deterministic and thus is not supported.

If you want to use the current time value in a field when you insert or update the record, you can use something like:

memsql> create table t(a int, 
 ts datetime(6) on update current_timestamp(6) default current_timestamp(6));
Query OK, 0 rows affected (0.15 sec)

memsql> insert t(a) values(1);
Query OK, 1 row affected (0.08 sec)

memsql> select * from t;
+------+----------------------------+
| a    | ts                         |
+------+----------------------------+
|    1 | 2021-02-17 15:42:40.294605 |
+------+----------------------------+

See here for more information:

Also, using the timestamp or timestamp(6) types is not recommended–they run out in 2038. Use datetime or datetime(6) instead.

1 Like
4

Hi Hanson

This is how I tried:

create table t(
a int,
ts datetime(6) on update current_timestamp(6) default current_timestamp(6)

KEY col_key (a) USING CLUSTERED COLUMNSTORE ,
SHARD KEY shard_key (a)
);

error:

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘6) default current_timestamp(6)¶¶ KEY col_key (a) USING CLUSTERED COLUMNSTORE’ at line 1

can you pls let me know what am I doing wrong ??

thanks,
vishwajeet

5

You’ve got a missing comma before “KEY col_key(a)…”.

If you add that, it works on 7.3 for me.

1 Like
6

Hi Hanson,

Yeah even with the comma it doesn’t work.

create table t(
a int,
ts datetime(6) on update current_timestamp(6) default current_timestamp(6),

KEY col_key (a) USING CLUSTERED COLUMNSTORE ,
SHARD KEY shard_key (a)
);

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6) default current_timestamp(6),¶¶KEY col_key (a) USING CLUSTERED COLUMNSTORE ’ at line 1

image
image966×497 31.2 KB

can you please try to create this table at your end with the same DDL ??
please do add USING CLUSTERED COLUMNSTORE and SHARD key

thanks,
vishwajeet

7

Are you using a version older than 7.3?
current_timestamp(6) didn’t used to be supported for ON UPDATE or DEFAULT until a recent release. Can’t remember which one. If you don’t care about fractions, CURRENT_TIMESTAMP might work for you.

1 Like
8

I am using a older version (6.8)
If it is not possible in this version, what is an alternative for this ? can you please help me with it?
Also, can you please give you an example to create a table with computed column as current_date or CURRENT_TIMESTAMP

Thanks,
Vishwajeet

9

Try CURRENT_TIMESTAMP or NOW(). I don’t have solution for you for a column defaulting to current_date. Consider having a datetime column default to current_date and create a computed column of type date from that, or just use the datetime column in your app. That works in 7.3:

memsql> create table t (a datetime default current_timestamp, b as a persisted date);
Query OK, 0 rows affected (0.07 sec)

memsql> insert t values();
Query OK, 1 row affected (0.05 sec)

memsql> select * from t;
+---------------------+------------+
| a                   | b          |
+---------------------+------------+
| 2021-02-24 17:49:33 | 2021-02-24 |
+---------------------+------------+
10

Hi Hanson,

I am trying to calculate AGE from a AES_ENCRYPTED column which contains the Birthday . But I am facing the below similar issue. Can u suggest any work around for this

ALTER TABLE XYZ ADD TEST_VAL AS (TIMESTAMPDIFF(YEAR, cast(AES_DECRYPT(BIRTHDAY,‘secret_key’) as char), CURDATE())) PERSISTED INT;
ERROR 1824 (HY000): Forwarding Error (ip:port): Cannot create computed column ‘TEST_VAL’ that depends on date/time function
mysql>

Any help will be appreciated.

11

Hi Ann. Any persisted computed column has to be derived only from literals and other regular columns. CURDATE() is a non-deterministic function that returns the current date. So you can’t use it in a persisted computed column expression. Consider inserting CURDATE() into some other column and then making your persisted computed column a function of that other column.