MySql time processing and mysql Processing

MySql time processing and mysql Processing

Many times, we want to process the time when querying the Mysql database, such as formatting or other operations. This avoids further processing, mysql also has many time-related processing functions. Today, we will make a simple summary for your reference.

First of all on the comparison function of time, this everybody refer to my another article, here no longer tells: http://blog.csdn.net/fanxl10/article/details/44172569

If we want to increase or decrease the processing time, we can use the date_add () function to input two parameters. The first parameter is the time to change, the second parameter is the value to be changed. For example, if we want to add a DAY, the parameter is: INTERVAL 1 DAY. If we want to reduce the number of days, the parameter is INTERVAL-1 DAY, and a negative value is passed in, if we want to add a MONTH, the parameter is INTERVAL 1 MONTH, and so on. Adding one YEAR is INTERVAL 1 YEAR, and adding one HOUR is INTERVAL 1 HOUR, we should have understood how to use it here. Let's look at the example below:

Check the start time and add one day:

SELECT START, DATE_ADD(START , INTERVAL 1 DAY) AS addStartFROM ts_tickeyWHERE id='373'

The result is as follows:

If we want to increase the start time in the database by one day, it is also very simple:

UPDATE ts_tickey SET START=DATE_ADD(START ,INTERVAL 1 DAY)WHERE id='373'

For other usage, you can try it yourself. Basically, there is nothing to say.

In addition, the Mysql time Formatting Function uses DATE_FORMAT (). Likewise, two parameters are transmitted. The first parameter is the time to be formatted, and the second parameter is formatted. For example:

DATE_FORMAT(a.start,'%Y-%m-%d') as start

Format the start time to yyyy-mm-dd.

You can also format the format to hour, minute, and second:

DATE_FORMAT(a.uploadDate,'%H:%i:%s')