🔥 Medium Level Date-Time Questions in MySQL

WHERE YEARWEEK(col,1)=YEARWEEK(CURDATE(),1)

WHERE YEARWEEK(col,1)=YEARWEEK(CURDATE()-INTERVAL 1 WEEK,1)

WHERE col >= CURDATE()-INTERVAL 30 DAY

SELECT * FROM users WHERE last_login < CURDATE()-INTERVAL 60 DAY

SELECT DATE_FORMAT(date,'%Y-%m'), SUM(amount) FROM orders GROUP BY DATE_FORMAT(date,'%Y-%m')

SELECT customer_id, MIN(date), MAX(date) FROM orders GROUP BY customer_id

SELECT DATEDIFF(d2, d1) FROM table;

SELECT TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) FROM users;

WHERE DAYOFWEEK(col) IN (1,7)

WHERE TIME(col) BETWEEN '09:00:00' AND '18:00:00'

WHERE end_date < CURDATE()

WHERE DAY(col)=1

SELECT TIMESTAMPDIFF(HOUR,in_time,out_time) FROM attendance;

WHERE updated_at IS NULL OR updated_at < NOW() - INTERVAL 1 YEAR

SELECT MONTH(col), SUM(amount) FROM table WHERE YEAR(col)=YEAR(CURDATE()) GROUP BY MONTH(col)

SELECT LEAST(dt1, dt2) FROM table;

WHERE QUARTER(col)=1 AND YEAR(col)=YEAR(CURDATE())

SELECT user_id, MIN(login), MAX(login) FROM logins GROUP BY user_id;

WHERE
  (
    MONTH(col)>=4 AND YEAR(col)=YEAR(CURDATE())
  ) OR (
    MONTH(col)<4 AND YEAR(col)=YEAR(CURDATE())-1
  )