hello I have a datetime column and I would like to put a time restriction on it how would I do this?
For example a range of time from 3:00:00 to 15:00:00 all data that fits this criteria is stored if not throw and error up and stop the entering of data in the column
In MySQL, you'd have to do this with a trigger on INSERT and UPDATE, so if someone tries to enter a value that doesn't meet your criteria, you raise a SIGNAL.
mysql> CREATE TABLE MyTable (
my_datetime DATETIME
);
mysql> DELIMITER ;;
mysql> CREATE TRIGGER MyTable_ins BEFORE INSERT ON MyTable
FOR EACH ROW BEGIN
IF (NOT TIME(NEW.my_datetime) BETWEEN '03:00:00' AND '15:00:00') THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Time does not fall in the range allowed.'
END IF;
END;;
mysql> DELIMITER ;
I get the error if try to do something I shouldn't:
mysql> INSERT INTO MyTable SET my_datetime = '2017-01-13 18:00:00';
ERROR 1644 (45000): time does not fall in the range allowed
But it works if I choose a time that's allowed:
mysql> INSERT INTO MyTable SET my_datetime = '2017-01-13 11:00:00';
Query OK, 1 row affected (0.00 sec)
I did some digging and some reading. I tested some stuff out on my own server. It doesn't work. Then I found this answer:
CHECK constraint in MySQL is not working
Yep, it accepts a CHECK constraint as valid syntax, then completely ignores it.
And while I was testing and writing up, Bill has posted the correct answer for MySQL. Do what he says.
