How to format time into a specific string using TIME_FORMAT function in MySQL

How to use the TIME_FORMAT function in MySQL to format time into a specific string

MySQL is a widely used relational database management system that provides a wealth of functions and operators to process data. In MySQL, there is a very useful function, the TIME_FORMAT function, which can format the time in a specified format and return a string.

The basic syntax of the TIME_FORMAT function is as follows:

TIME_FORMAT(time, format)

Among them, time is a valid time value, format is a time format used to describe String. The format string can contain the following placeholders:

• %H: represents the hour (00-23)
• %h: represents the hour (01-12)
• %i: Indicates minutes (00-59)
• %s: Indicates seconds (00-59)
• %p: Indicates morning or afternoon (AM or PM)

The following is an example of using the TIME_FORMAT function to format time into a specific string:

SELECT TIME_FORMAT('10:15:30', '%h:%i:%s %p');

Executing the above code will return the result as "10:15:30 AM", formatting the given time It is in the form of "hour:minute:second am/pm".

In addition, the TIME_FORMAT function can also be used with other MySQL functions to further customize the required time format. For example, combining the DATE_FORMAT function and the NOW function can output the current time in the specified format:

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

Executing the above code will return the current time in the format of year, month, day, hour, minute, and second, such as "2022-01-01 12: 00:00".

In addition to the above examples, the TIME_FORMAT function can also be used with other functions and operators to meet more complex needs. For example, combined with the DATE_ADD function, you can add and subtract time and format the result into a specific string:

SELECT TIME_FORMAT(DATE_ADD('10:15:30', INTERVAL 1 HOUR), '%h:%i:%s %p');

Executing the above code will return the result as "11:15:30 AM", which will be given Add 1 hour to the time and format the output.

To summarize, the TIME_FORMAT function in MySQL is very useful. It can format the time according to the specified format and return a string. By properly using the TIME_FORMAT function, we can display time in various forms to meet different needs. The above is an introduction and example about using the TIME_FORMAT function to format time. I hope it will be helpful to your MySQL development work.

The above is the detailed content of How to format time into a specific string using TIME_FORMAT function in MySQL. For more information, please follow other related articles on the PHP Chinese website!