The TIME_FORMAT SQL Function in MySQL and MariaDB - Format time |
|
| | TIME_FORMAT | Syntax: | TIME_FORMAT(Time, Format) | | Return value: | CHAR | | Function type: | Date and Time function | |
| | The TIME_FORMAT() function formats "Time" according to the format string in argument "Format".
The function only accepts information about formatting the time in the format string.
If the value for the "Time" or the "Format" argument is invalid or NULL, the TIME_FORMAT() function returns NULL.
Options for the "Format" string:
| Option | Description |
| %f | Microseconds 6 digits. |
| %H | Hour with 2 digits between 00-23. |
| %h | Hour with 2 digits between 01-12. |
| %I | Hour with 2 digits between 01-12. |
| %i | Minute with 2 digits. |
| %k | Hour with 1 digits between 0-23. |
| %l | Hour with 1 digits between 1-12. |
| %p | AM/PM according to current locale (Variable lc_time_names). |
| %r | Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'. |
| %S | Seconds with 2 digits. |
| %s | Seconds with 2 digits. |
| %T | Time in 24 hour format. Short for '%H:%i:%S'. |
| %% | A literal % character. |
| SQL Examples for the TIME_FORMAT function |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
| SELECT time_format(now(), '%h:%i:%s');
SELECT time_format(now(), '%h+%i+%s');
SELECT time_format('09:13:30', '%p %h:%i:%s');
SELECT time_format('19:13:30', '%p %h:%i:%s');
set @form='%h:%i:%s';
SELECT time_format(now(), @form);
SELECT time_format(now(), '%y-%m-%d %h:%i:%s');
SELECT time_format(now(), '');
SELECT time_format(null, '%y-%m-%d');
|
|
| time_format(now(), '%h:%i:%s') |
|---|
| varchar(8) BINARY |
|---|
| 11:58:22 |
|
|
| time_format(now(), '%h+%i+%s') |
|---|
| varchar(8) BINARY |
|---|
| 11+58+22 |
|
|
| time_format('09:13:30', '%p %h:%i:%s') |
|---|
| varchar(11) BINARY |
|---|
| AM 09:13:30 |
|
|
| time_format('19:13:30', '%p %h:%i:%s') |
|---|
| varchar(11) BINARY |
|---|
| PM 07:13:30 |
|
|
| time_format(now(), @form) |
|---|
|
|---|
| 11:58:22 |
|
|
| time_format(now(), '%y-%m-%d %h:%i:%s') |
|---|
| varchar(17) BINARY |
|---|
| NULL |
|
|
| time_format(now(), '') |
|---|
| varchar(0) BINARY |
|---|
| NULL |
|
|
| time_format(null, '%y-%m-%d') |
|---|
| varchar(8) BINARY |
|---|
| NULL |
|
|
| | The examples were created with the MyWAY SQL manager: | How to use the TIME_FORMAT() function in MySQL and MariaDB databases | | In MySQL and MariaDB the TIME_FORMAT() function can be used to format a time value according to a specified format and thereby customize the display of time values, including hours, minutes, seconds and fractions of a second. Column names of tables or other expressions in SQL statements can also be used as parameters to customize the display of time values and present them in a specific format according to the requirements for presentation in database result sets or reports. | | Further MySQL und MariaDB SQL Date and Time functions | | ADDDATE | ADDDATE(Date, [INTERVAL] Number [Unit]) | | More about ADDDATE Function |
| | ADDTIME | ADDTIME(Date, Expression) | | More about ADDTIME Function |
| | CONVERT_TZ | CONVERT_TZ(Date, From time zone, To time zone) | | More about CONVERT_TZ Function |
| | CURDATE | CURDATE() | | More about CURDATE Function |
| | CURTIME | CURTIME([Precision]) | | More about CURTIME Function |
| | CURRENT_DATE | CURRENT_DATE(-) | | More about CURRENT_DATE Function |
| | CURRENT_TIME | CURRENT_TIME([Precision]) | | More about CURRENT_TIME Function |
| | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP([Precision]) | | More about CURRENT_TIMESTAMP Function |
| | DATE | DATE(Date and Time) | | More about DATE Function |
| | DATE_ADD | DATE_ADD(Date, INTERVAL Number Unit) | | More about DATE_ADD Function |
| | DATE_SUB | DATE_SUB(Date, INTERVAL Number Unit) | | More about DATE_SUB Function |
| | FROM_DAYS | FROM_DAYS(Number of days) | | More about FROM_DAYS Function |
| | FROM_UNIXTIME | FROM_UNIXTIME(Unix timestamp [, Format]) | | More about FROM_UNIXTIME Function |
| | LOCALTIME | LOCALTIME([Precision]) | | More about LOCALTIME Function |
| | LOCALTIMESTAMP | LOCALTIMESTAMP([Precision]) | | More about LOCALTIMESTAMP Function |
| | MAKEDATE | MAKEDATE(Year, Day of year) | | More about MAKEDATE Function |
| | MAKETIME | MAKETIME(Hours, Minutes, Seconds) | | More about MAKETIME Function |
| | NOW | NOW([Precision]) | | More about NOW Function |
| | SEC_TO_TIME | SEC_TO_TIME(Seconds) | | More about SEC_TO_TIME Function |
| | SLEEP | SLEEP(Seconds) | | More about SLEEP Function |
| | STR_TO_DATE | STR_TO_DATE(String, Format) | | More about STR_TO_DATE Function |
| | SUBDATE | SUBDATE(Date, [INTERVAL] Number [Unit]) | | More about SUBDATE Function |
| | SUBTIME | SUBTIME(Date and Time, Deduction) | | More about SUBTIME Function |
| | SYSDATE | SYSDATE() | | More about SYSDATE Function |
| | TIME | TIME(Date and Time) | | More about TIME Function |
| | TIMEDIFF | TIMEDIFF(Date 1, Date 2) | | More about TIMEDIFF Function |
| | TIMESTAMP | TIMESTAMP(Date [, Addition]) | | More about TIMESTAMP Function |
| | TIMESTAMPADD | TIMESTAMPADD(Unit, Number, Date) | | More about TIMESTAMPADD Function |
| | TIMESTAMPDIFF | TIMESTAMPDIFF(Unit, Date 1, Date 2) | | More about TIMESTAMPDIFF Function |
| | UTC_DATE | UTC_DATE() | | More about UTC_DATE Function |
| | UTC_TIME | UTC_TIME([Precision]) | | More about UTC_TIME Function |
| | UTC_TIMESTAMP | UTC_TIMESTAMP([Precision]) | | More about UTC_TIMESTAMP Function |
| | DAY | DAY(Date) | | More about DAY Function |
| | DAYOFMONTH | DAYOFMONTH(Date) | | More about DAYOFMONTH Function |
| | DAYOFWEEK | DAYOFWEEK(Date) | | More about DAYOFWEEK Function |
| | DAYOFYEAR | DAYOFYEAR(Date) | | More about DAYOFYEAR Function |
| | EXTRACT | EXTRACT(Unit FROM Date) | | More about EXTRACT Function |
| | HOUR | HOUR(Date and Time) | | More about HOUR Function |
| | MICROSECOND | MICROSECOND(Date and Time) | | More about MICROSECOND Function |
| | MINUTE | MINUTE(Date and Time) | | More about MINUTE Function |
| | MONTH | MONTH(Date) | | More about MONTH Function |
| | QUARTER | QUARTER(Date) | | More about QUARTER Function |
| | SECOND | SECOND(Date and Time) | | More about SECOND Function |
| | TIME_TO_SEC | TIME_TO_SEC(Time) | | More about TIME_TO_SEC Function |
| | TO_DAYS | TO_DAYS(Date) | | More about TO_DAYS Function |
| | TO_SECONDS | TO_SECONDS(Date and Time) | | More about TO_SECONDS Function |
| | UNIX_TIMESTAMP | UNIX_TIMESTAMP([Date and Time]) | | More about UNIX_TIMESTAMP Function |
| | WEEK | WEEK(Date [, Mode]) | | More about WEEK Function |
| | WEEKDAY | WEEKDAY(Date) | | More about WEEKDAY Function |
| | WEEKOFYEAR | WEEKOFYEAR(Date) | | More about WEEKOFYEAR Function |
| | YEAR | YEAR(Date) | | More about YEAR Function |
| | YEARWEEK | YEARWEEK(Date [, Mode]) | | More about YEARWEEK Function |
| | DAYNAME | DAYNAME(Date) | | More about DAYNAME Function |
| | DATE_FORMAT | DATE_FORMAT(Date and Time, Format) | | More about DATE_FORMAT Function |
| | GET_FORMAT | GET_FORMAT(Type, Format) | | More about GET_FORMAT Function |
| | MONTHNAME | MONTHNAME(Date) | | More about MONTHNAME Function |
|
|
| | | More information about the TIME_FORMAT SQL function: and and |
|
|
|
|
