MySQL Date and Time Functions

SELECT NOW(); # current datetime
SELECT CURDATE(); # current date
SELECT CURTIME(); # current time

SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM orders; # format date
SELECT DATE_FORMAT(created_at, '%W, %M %d, %Y'); # Monday, January 01, 2024

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); # add 7 days
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); # subtract 1 month
SELECT created_at + INTERVAL 1 YEAR FROM users; # add 1 year

SELECT DATEDIFF(end_date, start_date) FROM projects; # difference in days

SELECT YEAR(created_at) FROM orders; # extract year
SELECT MONTH(created_at); # extract month
SELECT DAY(created_at); # extract day
SELECT HOUR(created_at); # extract hour

SELECT UNIX_TIMESTAMP(created_at); # convert to unix timestamp
SELECT FROM_UNIXTIME(1609459200); # convert from unix timestamp