Adding minutes to datetime on insert - mysql

Question

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!

Answer 1

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

Answer 2

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)

Answer 3

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