Example
Format a time:
SELECT TIME_FORMAT("19:30:10", "%H %i %s");
Definition and Usage
The TIME_FORMAT() function formats a time by a specified format.
Syntax
TIME_FORMAT(time, format)
MySQL TIME Function
Example
Extract the time part from a time expression:
SELECT TIME("19:30:10");
Definition and Usage
The TIME() function extracts the time part from a given time/datetime.
Note: This function returns “00:00:00” if expression is not a datetime/time, or NULL if expression is NULL.
Syntax
TIME(expression)
MySQL SYSDATE Function
Example
Return the current date and time:
SELECT SYSDATE();
Definition and Usage
The SYSDATE() function returns the current date and time.
Note: The date and time is returned as “YYYY-MM-DD HH:MM:SS” (string) or as YYYYMMDDHHMMSS (numeric).
Syntax
SYSDATE()
MySQL SUBTIME Function
Example
Subtract 5.000001 seconds and return the datetime:
SELECT SUBTIME("2017-06-15 10:24:21.000004", "5.000001");
Definition and Usage
The SUBTIME() function subtracts time from a time/datetime expression and then returns the new time/datetime.
Syntax
SUBTIME(datetime, time_interval)
MySQL SUBDATE Function
Example
Subtract 10 days from a date and return the date:
SELECT SUBDATE("2017-06-15", INTERVAL 10 DAY);
Definition and Usage
The SUBDATE() function subtracts a time/date interval from a date and then returns the date.
Syntax
SUBDATE(date, INTERVAL value unit)
OR:
SUBDATE(date, days)
MySQL STR_TO_DATE Function
Example
Return a date based on a string and a format:
SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
Definition and Usage
The STR_TO_DATE() function returns a date based on a string and a format.
Syntax
STR_TO_DATE(string, format)
MySQL SEC_TO_TIME Function
Example
Return a time value based on a specified seconds value:
SELECT SEC_TO_TIME(1);
Definition and Usage
The SEC_TO_TIME() function returns a time value (in format HH:MM:SS) based on the specified seconds.
Syntax
SEC_TO_TIME(seconds)
MySQL SECOND Function
Example
Return the seconds part of a datetime value:
SELECT SECOND("2017-06-20 09:34:00.000023");
Definition and Usage
The SECOND() function returns the seconds part of a time/datetime (from 0 to 59).
Syntax
SECOND(datetime)
MySQL QUARTER Function
Example
Return the quarter of the year for the date:
SELECT QUARTER("2017-06-15");
Definition and Usage
The QUARTER() function returns the quarter of the year for a given date value (a number from 1 to 4).
- January-March returns 1
- April-June returns 2
- July-Sep returns 3
- Oct-Dec returns 4
Syntax
QUARTER(date)
MySQL PERIOD_DIFF Function
Example
Return the difference between two periods:
SELECT PERIOD_DIFF(201710, 201703);
Definition and Usage
The PERIOD_DIFF() function returns the difference between two periods. The result will be in months.
Note: period1 and period2 should be in the same format.
Syntax
PERIOD_DIFF(period1, period2)