39

How can I subtract time in MySQL? For example, today is 16 March; I want to subtract 15 days to reach 1 March. Are there any methods that can be used to subtract 15 days from the current date?

ashleedawg
  • 20,365
  • 9
  • 72
  • 105

5 Answers5

63
SELECT DATE(NOW()-INTERVAL 15 DAY)

For a list of units see http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add

AndreKR
  • 32,613
  • 18
  • 106
  • 168
8

Not entirely related to this question but is related to the title:

SELECT SUBTIME("10:24:21", "5");        -- subtracts 5 seconds. (returns "10:24:16")

SELECT SUBTIME("10:24:21", "01:00:00");  -- subtracts one hour. (returns "09:24:21")

Documentation: MySQL SUBTIME function

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Riaan Schutte
  • 535
  • 1
  • 5
  • 14
  • 1
    Note that the first example (subtracting a number of seconds) only works up to 59 seconds. Durations of a minute or more must be specified as `HH:MM:SS`. – ashleedawg Jan 23 '21 at 14:03
  • 1
    Also this answer *did* match the question's title, until I fixed the title. :-) – ashleedawg Jan 23 '21 at 14:07
4

Use:

SELECT NOW() - INTERVAL 15 DAY

to keep the datetime precision.

Nae
  • 14,209
  • 7
  • 52
  • 79
0

You can use this :

SELECT DATE(NOW()-INTERVAL 15 DAY);

for when you want to subtract the number of days.

In order to subtract the time instead, say 15 minutes, the following should work:

SELECT(DATE_SUB(NOW(), INTERVAL '15:0' MINUTE_SECOND));

Adding the reference link again :- https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-add.

Pancham
  • 1
  • 1
0

Yes its possible using date function in Mysql

    select distinct
 lastname, 
      changedat, date_add(changedat, interval -15 day) as newdate
from employee_audit;

lastname and changedat is field name and employee_audit is table name.

enter image description here

I have subtract 15 days from my date - check image please. thanks

Solomon Suraj
  • 1,162
  • 8
  • 8