Adding new Column with current time in SQL

Question

Im trying to add a new column to my SQL table i want the data type to be TIME and the default value to be CURRENT_TIME. This is my query.

ALTER TABLE tuesday_records ADD cur_time TIME DEFAULT CURRENT_TIME

And this is the error message i get.

Error
SQL query:


ALTER TABLE tuesday_records ADD cur_time TIME DEFAULT CURRENT_TIME
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CURRENT_TIME' at line 1

Have you tried reading the MariaDB documentation? https://mariadb.com/kb/en/library/timestamp/ — Art van Scheppingen, Mar 19 '19 at 15:05
What is your version? Works for me on 10.2 and 10.3 - But I still think you want TIMESTAMP. — Paul Spiegel, Mar 19 '19 at 15:17
version 10.1.38. I need to find the time difference between 2 things i can have this column as a TIMESTAMP but i need to have the other one as TIME, so if there is a way to find the time difference between TIMESTAMP and TIME that would also help — Balor9914, Mar 19 '19 at 15:24

score 0 · Accepted Answer · answered Mar 19 '19 at 16:35

You can do what you want with generated columns:

create table t (
    id int auto_increment primary key,
    x int,
    t timestamp default now(),
    tt time generated always as (time(t))
);

That is, add a timestamp column and then extract the time.

Here is a db<>fiddle.

Although this answers your question, I'm not sure if it is the best approach to your overall problem.

Adding new Column with current time in SQL

1 Answers1