0

I want to add a column in my table police_publiclocation where column must have 5hr and 30min greater value than current TIMESTAMP value.

I am doing the following but it is not working.. Please provide me some solution.. Thanks in advance..

ALTER TABLE `police_publiclocation` ADD `datetime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP+CONVERT(Datetime, '2011-09-28 05:30:00',120) AFTER `active`;

ERROR: 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 '+CONVERT(Datetime, '2011-09-28 05:30:00') AFTER active' at line 1

USING FOLLOWING

MYSQL

phpMyAdmin Version information: 4.6.6 , latest stable version: 4.7.2

Database server

Server: Localhost via UNIX socket Server type: MariaDB Server version: 10.1.20-MariaDB - MariaDB Server Protocol version: 10 Server charset: UTF-8 Unicode (utf8)

Web server Apache/2.4.6 (CentOS) OpenSSL/1.0.1e-fips Database client version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $ PHP extension: mysqliDocumentation curlDocumentation mbstringDocumentation PHP version: 7.0.8

Biddut
  • 418
  • 1
  • 6
  • 17
Abhishek Sahay
  • 141
  • 1
  • 9
  • 1
    What DBMS are you using? Please remove in irrelevant tags as the answer depends on the DBMS. – Dan Guzman Jul 01 '17 at 16:50
  • According to Maria DB documentation: DEFAULT with expressions was introduced in MariaDB 10.2.1, You wrote you are on version 10.1.20, so I guess default expressions will not work in your case. https://mariadb.com/kb/en/mariadb/alter-table/ – Ran Avnon Jul 01 '17 at 18:27
  • In versions prior to MariaDB 10.2.1, one option is to use a [PREPARE Statement](https://mariadb.com/kb/en/mariadb/prepare-statement/). – wchiquito Jul 02 '17 at 10:14

1 Answers1

0

With MariaDB 10.2, you can have arbitrary expressions in DEFAULT.

Otherwise, consider using a TRIGGER to set it.

Rick James
  • 135,179
  • 13
  • 127
  • 222