MariaDB CURRENT_TIMESTAMP default

Question

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?

Amalamalbena

Answer 1

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

`uRegDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Ossuary

Right, CURRENT_TIMESTAMP as a default for DATETIME type is only available in 10.x. If you're using 5.5, it should a TIMESTAMP column. – Ha

Answer 2

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

Inconclusive

Answer 3

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.

Intuitive