Mysql – Trigger BEFORE INSERT from date to datetime

datedatetimeMySQLtrigger

I have this schema:

CREATE TABLE `prices` (
  `id_product` int(11) unsigned NOT NULL,
  `date` date NOT NULL, # Important: DATE
  `value` decimal(8,5) unsigned NOT NULL DEFAULT '0.00000'
  PRIMARY KEY (`id_product`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `prices_changes` (
  `id_product` int(11) unsigned NOT NULL,
  `date` datetime NOT NULL, # Important: DATETIME
  `value` decimal(8,5) unsigned NOT NULL DEFAULT '0.00000'
  KEY (`id_product`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELIMITER ;;
CREATE TRIGGER `prices_before_insert`
  BEFORE INSERT ON `prices`
  FOR EACH ROW
    INSERT INTO `prices_changes` (`id_product`, `date`, `value`) VALUES (NEW.`id_product`, NEW.`date`, NEW.`value`);;
DELIMITER ;

INSERT INTO `prices` SET
  `id_product` = 1,
  `date` = "2016-08-11 13:50:00", # Is a DATETIME value
  `value` = "20.50"
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);

mysql> SELECT * FROM `prices` WHERE id_product = 1;
+------------+------------+---------+
| id_product | date       | value   |
+------------+------------+---------+
| 1          | 2016-08-11 | 20.50   |
+------------+------------+---------+

mysql> SELECT * FROM `prices_changes` WHERE id_product = 1;
+------------+---------------------+---------+
| id_product | date                | value   |
+------------+---------------------+---------+
| 1          | 2016-08-11 00:00:00 | 20.50   |
+------------+---------------------+---------+

It should update prices table with last value without product/date duplicates and insert into prices_changes all updates using a trigger.

The problem is that in prices_changes the date column defined as datetime is storing the date value without the time.

How can I store into the prices_changes the data as datetime using the original value?

Best Answer

It seems each time you are inserting data into both tables i.e. price_changes and prices. Reverse process may be helpful. If possible try this :

DELIMITER ;;
CREATE TRIGGER `prices_changes_before_insert`
   BEFORE INSERT ON `prices_changes`
   FOR EACH ROW
   INSERT INTO `prices` (`id_product`, `date`, `value`) VALUES (NEW.`id_product`, NEW.`date`, NEW.`value`)
    ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);;
DELIMITER ;

Then

INSERT INTO `prices_changes` SET
   `id_product` = 3,
    `date` = "2016-08-12 14:50:00", # Is a DATETIME value
    `value` = "20.50";