Date & Time Functions

MySQL date and time functions can be used to change the value of date and time columns.

ADDDATE( )

This MySQL function can be used to add a given interval of time to a date or the value of a date column

ADDTIME( )

Similar to ADDDATE(), but specifically and supposedly for adding time to a value. However, both MySQL functions allow the addition of date and time to a value.

CONVERT_TZ( )

This MySQL function converts a date and time for given time zone to another which is specified.

CURDATE( )

This MySQL function returns the current system date in yyyy-mm-dd format.

CURRENT_DATE( )

This MySQL function is the same as CURDATE().

CURRENT_TIME( )

This MySQL function returns the current time in hh:mm:ss format. This is synonymous with CURTIME( ).

CURRENT_TIMESTAMP( )

This MySQL function returns the current date and time in yyyy-mm-dd hh:mm:ss format.

CURTIME( )

This MySQL function returns the current system time in hh:mm:ss format.

DATE( )

This MySQL function returns the date from a given string, value, or expression that is submitted in a date or datetime format.

DATEDIFF( )

This MySQL function returns the number of days of difference between the two dates given.

DATE_ADD( )

Using the date or datetime given, this MySQL function adds the number of intervals specified. It's fairly synonymous with the ADDDATE( ) function.

DATE_FORMAT( )

This MySQL function returns a date and time in a desired format, based on formatting codes listed within quotes for the second argument of the function.

DATE_SUB( )

Use this MySQL function to subtract from the results of a date or time data type column. It's fairly synonymous with the SUBDATE( ).

DAY( )

This MySQL function returns the day of the month for a given date.

DAYNAME( )

This MySQL function returns the name of the day for the date provided.

DAYOFMONTH( )

This MySQL function returns the day of the month for the date given.

DAYOFWEEK( )

This MySQL function returns the numerical day of the week for a given date.

DAYOFYEAR( )

This MySQL function returns the day of the year. January 1 would give a value of 1.

EXTRACT( )

This MySQL function extracts date or time information from a date or a datetime expression in the format type requested.

FROM_DAYS( )

This MySQL function returns the date based on the number of days given, which are from the beginning of the currently used standard calendar.

FROM_UNIXTIME( )

This MySQL function returns the date based on Unix time, which is the number of seconds since January 1, 1970, Greenwich Mean Time (GMT).

GET_FORMAT( )

This MySQL function returns the format for a given data type, based on the standard given as the second argument. The format codes returned are the same codes used by the DATE_FORMAT() function.

HOUR( )

This MySQL function returns the hour for a DATETIME or TIME column.

This MySQL function returns the date of the last day of the month for a given date or datetime value.

LOCALTIME( )

This MySQL function returns the current system date in yyyy-mm-dd hh:mm:ss format. It's synonymous with LOCALTIMESTAMP( ) and NOW( ).

LOCALTIMESTAMP( )

This MySQL function is synonymous with LOCALTIME( ) and NOW( ).

MAKEDATE( )

This MySQL function determines the date requested from the start of the given year, by adding the number of days given.

MAKETIME( )

This MySQL function converts a given hour, minute, and second to hh:mm:ss format.

MICROSECOND( )

This MySQL function extracts the microseconds value of a given time.

MINUTE( )

This MySQL function returns the minute value (0-59) of a given time.

MONTH( )

This MySQL function returns the numeric value of the month (0-12) for the date provided.

MONTHNAME( )

This MySQL function returns the name of the month for the date provided.

NOW( )

This MySQL function returns the current date and time. The format returned is yyyy-mm-dd hh:mm:ss.nnnnnn. It's synonymous with LOCALTIME( ) and LOCALTIMESTAMP( ).

PERIOD_ADD( )

This MySQL function adds a specified number of months to a period, which is a string containing only the year and month in either yyyymm or yymm format.

PERIOD_DIFF( )

This MySQL function returns the number of months between periods given.

This MySQL function returns the number of the quarter (1-4) for the date provided. The first quarter (i.e., three months) of each year has a value of 1.

SECOND( )

This MySQL function returns the seconds value (0-59) for a given time.

This MySQL function returns the period for a given number of seconds in the format hh:mm:ss.

This MySQL function pauses the execution of an SQL statement in which it is given, for the number of seconds given.

STR_TO_DATE( )

This MySQL function returns the date and time of a given string for a given format.

SUBDATE( )

Use this MySQL function to subtract a date or time interval from the results of a DATE or TIME data type column. It's an alias for DATE_SUB( ).

This MySQL function returns the date and time for the given string or column, decreased by the time given as the second argument (d hh:mm:ss).

SYSDATE( )

This MySQL function returns the system date in the yyyy-mm-dd hh:mm:ss format at the time it's executed.

TIME( )

This MySQL function returns the time from a given string or column containing date and time data.

TIMEDIFF( )

This MySQL function returns the time difference between the two times given.

TIMESTAMP( )

This MySQL function merges the date and time from given strings or columns that contain date and time data separately; the result is returned in yyyy-mm-dd hh:mm:ss format.

This MySQL function adds the given number of intervals of time to the given date or time.

TIMESTAMPDIFF( )

This MySQL function returns the time difference between the two times given, but only for the interval being compared.

TIME_FORMAT( )

This MySQL function returns the time value of the time element provided and formats it according to formatting codes given.

TIME_TO_SEC( )

This MySQL function returns the number of seconds that the given time represents.

TO_DAYS( )

This MySQL function returns the date based on the number of days given, which are from the beginning of the currently used standard calendar.

UNIX_TIMESTAMP( )

This MySQL function returns the number of seconds since the start of the Unix epoch (January 1, 1970, Greenwich Mean Time).

UTC_DATE( )

This MySQL function returns the current Universal Time, Coordinated (UTC) date in yyyy-mm-dd format.

UTC_TIME( )

This MySQL function returns the current Universal Time, Coordinated (UTC) time in hh:mm:ss format.

UTC_TIMESTAMP( )

This MySQL function returns the current UTC date and time in yyyy-mm-dd hh:mm:ss format.

WEEK( )

This MySQL function returns the number of the week starting from the beginning of the year for the date provided.

WEEKDAY( )

This MySQL function returns the number for the day of the week. Monday is considered the first day of the week.

WEEKOFYEAR( )

This MySQL function returns the calendar week of the year for a given date. It's a bit synonymous with WEEK( ).

YEAR( )

This MySQL function returns the year of the date provided.

YEARWEEK( )

This MySQL function returns the year coupled with the number of the week into the year: yyyyww. By default, the first day of the week is Sunday and the basis of the calculation. This MySQL function is somewhat synonymous with WEEK( ).