CODEWITHSUNDEEP
mysql
Lakshman Pilaka
Lakshman Pilaka

Reputation: 1951

MySQL Generated Columns of date type

i use mysql version 5.7

i have a field devicetime which is a datetime field.

for some reason i want to add a generated column which stores only the date part of the devicetime field.

i have tried the following statement

alter table mytable
add COLUMN recorddate date generated always as date(devicetime) stored;

i get an error

[Err] 1064 - 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  'date(devicetime) stored' at line 2

i have taken the inputs from MySQL documentation from this link.

my current table structure is like this

enter image description here

Upvotes: 5

Views: 5453

Answers (2)

Sagar Gangwal
Sagar Gangwal

Reputation: 7947

One solution came into my mind is,you can store TIMESTAMP into table. And when try to get anywhere in your SYSTEM you can use DATE_FORMAT() and STR_TO_DATE() function to whatever part you required from that actual data.

Upvotes: 0

Vatev
Vatev

Reputation: 7590

For whatever reason MySQL needs some ()'s around the expression:

ALTER TABLE `mytable ` 
ADD COLUMN  `recorddate` DATE GENERATED ALWAYS AS (date(devicetime)) STORED;

Upvotes: 6

Related Questions