🔥 Advanced Level Date-Time Questions in MySQL

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

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

SELECT month, SUM(amount) OVER (ORDER BY month) FROM (SELECT DATE_FORMAT(col,'%Y-%m') AS month, SUM(amount) AS amount FROM table GROUP BY month) AS t;

WHERE col >= CURDATE() - INTERVAL 12 MONTH GROUP BY DATE_FORMAT(col,'%Y-%m')

SELECT AVG(TIMESTAMPDIFF(SECOND,login,logout))/3600 FROM sessions;

WHERE TIMESTAMPDIFF(HOUR,in_time,out_time)>9

SELECT user_id, DATE(login), COUNT(*) FROM logins WHERE login >= CURDATE()-INTERVAL 7 DAY GROUP BY user_id, DATE(login) HAVING COUNT(*)>1;

SELECT DATE(col), COUNT(*) AS cnt FROM orders GROUP BY DATE(col) ORDER BY cnt DESC LIMIT 5;

SELECT DAYOFWEEK(col)<=6 AS is_weekday, COUNT(*) FROM table GROUP BY is_weekday;

SELECT CONVERT_TZ(col,'UTC','Asia/Kolkata') FROM table;

CREATE VIEW this_week AS SELECT * FROM tbl WHERE YEARWEEK(col,1)=YEARWEEK(CURDATE(),1);