Exploring The Time Functions in MySQL
Here’s a list of some of MySQL’s time functions with SQL samples:
NOW() – returns the current date and time in the format ‘YYYY-MM-DD HH:MM:SS’
Example: SELECT NOW();
CURDATE() – returns the current date in the format ‘YYYY-MM-DD’
Example: SELECT CURDATE();
CURTIME() – returns the current time in the format ‘HH:MM:SS’
Example: SELECT CURTIME();
DATE() – extracts the date part of a date or datetime expression
Example: SELECT DATE(‘2022-03-30 16:30:45’);
TIME() – extracts the time part of a date or datetime expression
Example: SELECT TIME(‘2022-03-30 16:30:45’);
YEAR() – extracts the year from a date or datetime expression
Example: SELECT YEAR(‘2022-03-30’);
MONTH() – extracts the month from a date or datetime expression
Example: SELECT MONTH(‘2022-03-30’);
DAY() – extracts the day from a date or datetime expression
Example: SELECT DAY(‘2022-03-30’);
HOUR() – extracts the hour from a time or datetime expression
Example: SELECT HOUR(’16:30:45′);
MINUTE() – extracts the minute from a time or datetime expression
Example: SELECT MINUTE(’16:30:45′);
SECOND() – extracts the second from a time or datetime expression
Example: SELECT SECOND(’16:30:45′);
TIMEDIFF() – returns the difference between two time or datetime expressions
Example: SELECT TIMEDIFF(‘2022-03-30 16:30:45’, ‘2022-03-30 15:00:00’);
DATE_ADD() – adds a specified time interval to a date or datetime expression
Example: SELECT DATE_ADD(‘2022-03-30’, INTERVAL 1 MONTH);
DATE_SUB() – subtracts a specified time interval from a date or datetime expression
Example: SELECT DATE_SUB(‘2022-03-30’, INTERVAL 1 DAY);