CODEWITHSUNDEEP
mysqldatedefaultmariadb
Curunir
Curunir

Reputation: 1288

MariaDB CURRENT_TIMESTAMP default

I'm trying to set up a DB for user registration and in want to have a column with the registration date in it, which should be the current time by default. I tried running this query:

CREATE TABLE `users` (
  <some stuff>
  `uRegDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
)

But it doesn't work with MariaDB. Anyone got a solution?

Upvotes: 8

Views: 24475

Answers (3)

bcag2
bcag2

Reputation: 2439

I'm using MariaDB 10.3.39, on existing table, first I add attribute (created_at in my example) nullable default null, then run :

ALTER TABLE `my_table` CHANGE `created_at` `created_at` TIMESTAMP NULL 
DEFAULT CURRENT_TIMESTAMP;

In some case, if you use phpmyadmin, it uses 'CURRENT_TIMESTAMP' between quotes as a string. In this case, remove quotes by editing the request.

Upvotes: 0

philantrovert
philantrovert

Reputation: 10092

I'm using MariaDB 10.1.12 and your statement works for me.

create table datetest (
 date1 datetime default current_timestamp,
 date2 timestamp default current_timestamp);
insert into datetest values ( );

The above statements execute without any error and the following result is displayed 

+---------------------+---------------------+
| date1               | date2               |
+---------------------+---------------------+
| 2016-11-29 17:16:58 | 2016-11-29 17:16:58 |
+---------------------+---------------------+

Upvotes: 2

Rahul
Rahul

Reputation: 77926

What if you change the datatype to TIMESTAMP instead of DATETIME like

`uRegDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Upvotes: 13

Related Questions