MySQL Cookbook Reading notes the sixth chapter

1, modify the format of the date in MySQL

When a date value is displayed, MySQL displays the date in ISO format (CCYY-MM0DD) if it is not specifically specified. If you do not want to output the time and date values in MySQL's default format, you can use the Date_format () or Time_format () function to rewrite the date or time value in the format expected by the user

Date_format (), Time_format (), and str_to_date () three functions all accept the format string as arguments:

2, set the client time zone

If the client and server are in different time zones, the actual saved result is not the correct UTC time when the client saves the timestamp type value on the server.

WORKAROUND: When the client connects to the server, the client time zone is changed by setting the system variable Time_zone.

Assume that the server and client are the same time zone:

The value of the client query is the same as the value in the database, but if it is not in the same time zone as the server, we change session time_zone= ' +04:00 ' then the time will change accordingly.

3, get the current date or time

You can use the Curdate (), Curtime () or Now () function to get the current date and time, using the Utc_date,utc_time or Utc_timestamp () function to get the current UTC date and time

Use timestamp to track line modification times:

First create a table tsdemo1 that contains the timestamp type column:

View the properties of the Tsdemo1 at this time:

If you want to insert it in the Tsdemo1 table, the TS column will record the time of the insertion.

The update operation also logs the time of the update (the time is not updated when the original value is updated):

If you only want the timestamp column to be automatically initialized to the date and time of row insertion, and you do not want to change in the future, you can make timestamp enumerate

Auto-initialize property, but no Auto-update property

At this point, the properties of the table:

No on update current_timestamp in Tsdemo1

After update, timestamp does not update:

4, a partial value is decomposed from a date or time value

A, use a specific function to remove a specific part from a time or date, such as the month () function, the minute () function

b, use the Format function date_format () or Time_format () and a specific format string to get a date or time value to decompose the required part

C, treat a time or date as a string, and then use the left () or mid () function to get the desired portion from it.

Extract () function to decompose a date or time value

Use the Format function to decompose a date or time value:

Benefit: Display the resulting decomposition results in a user-specified format

Use a string to decompose time or date values:

5, calculates the interval between two dates or times

Use the time difference function:

Calculates the interval and divides the interval into hours, minutes, seconds, and displays:

You can also use Timestampdiff (UNIT,VAL1,VAL2):

Use basic time units to calculate the time interval:

6. Switch the time zone to a date and time

The Convert-tz () function accepts three parameters: one date-and-time value and two time zone indicator.

Because my MySQL database does not have a time zone table that supports the time zone name, I try to install the time zone table without knowing why it is still null, using UTC is OK

Found the cause of the error is that I signed in to MySQL when I specified the login table is cookbook, and the Time zone table is in the MySQL directory, here is not successful is normal