Menu
Hi i have table with datetime variable. I was wondering if i can somehow change the datetime column to add 1O minutes to stored date. Perhaps some trigger has to be involved.
Thanks for help
497
MySQL ADDTIME() function In MySQL the ADDTIME() returns a time or datetime after adding a time value with a time or datetime.
SELECT col1, col2, col3 FROM table WHERE DATE_ADD(last_seen, INTERVAL 10 MINUTE) >= NOW();
DATE_ADD() function in MySQL is used to add a specified time or date interval to a specified date and then return the date. Specified date to be modified. Here the value is the date or time interval to add. This value can be both positive and negative.
Introduction to MySQL DATE data type This format is fixed and it is not possible to change it. For example, you may prefer to use mm-dd-yyyy format but you can't. Instead, you follow the standard date format and use the DATE_FORMAT function to format the date the way you want. MySQL uses 3 bytes to store a DATE value.
I like the INTERVAL expr unit notation. It feels more readable to me:
SELECT NOW(),
NOW() + INTERVAL 10 MINUTE;
+--------------------------------+-------------------------------+
| NOW() | NOW() + INTERVAL 10 MINUTE |
+--------------------------------+-------------------------------+
| August, 12 2013 14:12:56+0000 | August, 12 2013 14:22:56+0000 |
+--------------------------------+-------------------------------+
If you want to select existing rows and add 10 minutes to the result:
SELECT the_date + INTERVAL 10 MINUTE FROM tbl;
If you want to alter existing rows stored in a table, you could use:
UPDATE tbl SET the_date = the_date + INTERVAL 10 MINUTE;
If you want increase by force a value by 10 minutes while inserting, you need a trigger:
CREATE TRIGGER ins_future_date BEFORE INSERT ON tbl
FOR EACH ROW
SET NEW.the_date = NEW.the_date + INTERVAL 10 MINUTE
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
