Blog

MySQL DATE_SUB

If you are working with date type columns you need to know the DATE_SUB function.

Do you know her?

Yeah?

Here I leave you a bit of everything, this is a tutorial with our KISS methodology: theory, examples, more examples.

Everything explained for novices.

Inside the real walkthrough.

Content

  • How do you use DATE_SUB SQL?
    • Syntax
  • Current date minus 7 days in SQL
  • Date 30 days ago in MySQL

How do you use DATE_SUB SQL?

Subtract days, minutes, seconds or milliseconds from a date, or what is the same: any interval allowed in DATESUB:

  • MICROSECOND
  • SECOND
  • MINUTE
  • TIME
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

Yes, as you read it, this is what the DATE_SUB() function offers us.

Like any other SQL function, it can be used in an infinite number of different queries.

Allows us subtract intervals from dates or dates and times.

Syntax

DATE_SUB(date, INTERVAL value interval)

Basic example for you to understand in 2 seconds:

SELECT DATE_SUB("2023-02-21 22:22:22", INTERVAL 15 MINUTE);

Result of this simple query:

DATE_SUB(“2023-02-21 22:22:22”, INTERVAL 15 MINUTE)
2023-02-21 22:07:22

Do you want more? Follow down:

UPDATE table_novato
SET fecha_que_aprendio_curdate = NOW()
WHERE visita > DATE_SUB( NOW(), INTERVAL 2 MINUTE);

#----------------

INSERT momento_sonrisa
SET fecha_creada = DATE_SUB( NOW(), INTERVAL 2 SECOND );

Now you have the base, now I continue with the typical examples of use.

Copy and paste 🙂

Current date minus 7 days in SQL

Specifically, with the DBMS MySQL or MariaDBin case there are any doubts, since in srcodigofuente we always work with free software.

#mysql current date minus 7 days
SELECT DATE_SUB( CURDATE(), INTERVAL 7 DAY) as fecha_hace_7_días;

#Ahora con un WHERE. Usuarios registrados en los últimos 7 días:
SELECT username
FROM table_users
WHERE date_add > DATE_SUB( CURDATE(), INTERVAL 7 DAY)

#¿y si lo hacemos en el select y utilizando una columna como fecha? No problem:
SELECT DATE_SUB(date_add, INTERVAL 7 DAY)
FROM table_users

Date 30 days ago in MySQL

Now another typical SQL example: get the date in SQL of the last 30 days.

As in all the examples I am going to try to mix it with the real world, yes, so that we can put ourselves in a situation:

#comentarios de usuarios de los últimos 30 días
SELECT text
FROM user_comment
WHERE date_add > DATE_SUB( NOW(), INTERVAL 30 DAY)

Leave a Reply