I've tried this
INSERT INTO products
(product_id, product_type, created_dt, end_dt)
VALUES
('11', '1', '2010-10-08 00:11:10', DATE_SUB(2010-10-08 00:11:10, INTERVAL 59 Minute))
But this doesn't work. Any other ways to do this within Mysql?
Thanks!
Use:
INSERT INTO products
(product_id, product_type, created_dt, end_dt)
VALUES
(11,
'1',
'2010-10-08 00:11:10',
DATE_ADD(STR_TO_DATE('2010-10-08 00:11:10', '%Y-%m-%d %H:%i:%s'), INTERVAL 59 MINUTE)
)
On MySQL 5.1.49, it wouldn't add/subtract because it wasn't implicitly converting the string to a DATETIME data type. So I had to use STR_TO_DATE to explicitly convert the string into a DATETIME. Otherwise, MySQL workbench returns BLOB...
Use timestamp for your database..
like
$date = time();
and save this variable as a value in your database.
If you want to retrieve a date in date format then use this time stamp in
time('M-Y-D',timestamp);
it will format the timestampin date format or format u put in the function...
check PHP mannual for time() here
Try:
INSERT INTO products
(product_id, product_type, created_dt, end_dt)
VALUES
('11', '1', '2010-10-08 00:11:10', '2010-10-08 00:11:10' - INTERVAL 59 Minute)
'2010-10-08 00:11:10' - INTERVAL 59 Minute returns BLOB in MySQL Workbench, against MySQL 5.1.49