# Create a computed/PERSISTED column with current date

**URL:** <https://www.singlestore.com/forum/t/create-a-computed-persisted-column-with-current-date/2776>\
**Category:** Help\
**Created:** [February 17, 2021, 11:37am UTC](https://www.singlestore.com/forum/t/create-a-computed-persisted-column-with-current-date/2776 "2021-02-17T11:37:02Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![vishwajeetdabholkar](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/v/3d9bf3/32.png) [@vishwajeetdabholkar](https://www.singlestore.com/forum/u/vishwajeetdabholkar)\
**Post date:** [February 17, 2021, 11:37am UTC](https://www.singlestore.com/forum/t/create-a-computed-persisted-column-with-current-date/2776/1 "2021-02-17T11:37:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![hanson](https://www.singlestore.com/forum/user_avatar/www.singlestore.com/hanson/32/196_2.png) [@hanson](https://www.singlestore.com/forum/u/hanson)\
**Post date:** [February 17, 2021, 11:46pm UTC](https://www.singlestore.com/forum/t/create-a-computed-persisted-column-with-current-date/2776/2 "2021-02-17T23:46:00Z")

</div>

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:

```auto
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:

> **[Data Types · SingleStore Documentation](https://docs.singlestore.com/db/v8.1/reference/sql-reference/data-types/)**
>
> SingleStore is a modern relational database for cloud and on-premises that delivers immediate insights for modern applications and analytical systems. Book a demo or trial today!

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

---

<div class="post-metadata">

**Author:** ![vishwajeetdabholkar](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/v/3d9bf3/32.png) [@vishwajeetdabholkar](https://www.singlestore.com/forum/u/vishwajeetdabholkar)\
**Post date:** [February 22, 2021, 2:21pm UTC](https://www.singlestore.com/forum/t/create-a-computed-persisted-column-with-current-date/2776/4 "2021-02-22T14:21:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![hanson](https://www.singlestore.com/forum/user_avatar/www.singlestore.com/hanson/32/196_2.png) [@hanson](https://www.singlestore.com/forum/u/hanson)\
**Post date:** [February 22, 2021, 11:09pm UTC](https://www.singlestore.com/forum/t/create-a-computed-persisted-column-with-current-date/2776/5 "2021-02-22T23:09:30Z")

</div>

You’ve got a missing comma before “KEY col\_key(a)…”.

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

---

<div class="post-metadata">

**Author:** ![vishwajeetdabholkar](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/v/3d9bf3/32.png) [@vishwajeetdabholkar](https://www.singlestore.com/forum/u/vishwajeetdabholkar)\
**Post date:** [February 23, 2021, 6:36am UTC](https://www.singlestore.com/forum/t/create-a-computed-persisted-column-with-current-date/2776/6 "2021-02-23T06:36:01Z")

</div>

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](https://forum-prod-assets20231213123829991800000001.s3.dualstack.us-east-1.amazonaws.com/original/1X/ff20a16ff514234b874b7f416aba268e03b55298.png)

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

---

<div class="post-metadata">

**Author:** ![hanson](https://www.singlestore.com/forum/user_avatar/www.singlestore.com/hanson/32/196_2.png) [@hanson](https://www.singlestore.com/forum/u/hanson)\
**Post date:** [February 23, 2021, 10:59pm UTC](https://www.singlestore.com/forum/t/create-a-computed-persisted-column-with-current-date/2776/7 "2021-02-23T22:59:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![vishwajeetdabholkar](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/v/3d9bf3/32.png) [@vishwajeetdabholkar](https://www.singlestore.com/forum/u/vishwajeetdabholkar)\
**Post date:** [February 24, 2021, 6:54am UTC](https://www.singlestore.com/forum/t/create-a-computed-persisted-column-with-current-date/2776/8 "2021-02-24T06:54:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![hanson](https://www.singlestore.com/forum/user_avatar/www.singlestore.com/hanson/32/196_2.png) [@hanson](https://www.singlestore.com/forum/u/hanson)\
**Post date:** [February 25, 2021, 1:45am UTC](https://www.singlestore.com/forum/t/create-a-computed-persisted-column-with-current-date/2776/9 "2021-02-25T01:45:51Z")

</div>

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:

```auto
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 |
+---------------------+------------+

```

---

<div class="post-metadata">

**Author:** ![ann.chinnu](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/a/a698b9/32.png) [@ann.chinnu](https://www.singlestore.com/forum/u/ann.chinnu)\
**Post date:** [July 21, 2025, 6:45pm UTC](https://www.singlestore.com/forum/t/create-a-computed-persisted-column-with-current-date/2776/10 "2025-07-21T18:45:25Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![hanson1](https://www.singlestore.com/forum/user_avatar/www.singlestore.com/hanson1/32/2092_2.png) [@hanson1](https://www.singlestore.com/forum/u/hanson1)\
**Post date:** [July 21, 2025, 7:01pm UTC](https://www.singlestore.com/forum/t/create-a-computed-persisted-column-with-current-date/2776/11 "2025-07-21T19:01:49Z")

</div>

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.
