Welcome to the Linux community forum and interact with 2 million technical staff. If the MySQL database uses the varchar (or char) type at a certain time, this makes it easy for the system to adapt to different database versions without converting the time type. When you want to convert the retrieved field to the time type, you can perform the following operations:
Welcome to the Linux community forum and interact with 2 million technical staff> If the MySQL database uses the varchar (or char) type at a certain time, this makes it easy for the system to adapt to different database versions without converting the time type. When you want to convert the retrieved field to the time type, you can perform the following operations:
Welcome to the Linux community forum and interact with 2 million technicians>
If the MySQL database uses the varchar (or char) type at a certain time, it is convenient for the system to use it, instead of converting the time type to adapt to different database versions, to convert the retrieved field to a time type, perform the following operations:
(1) str_to_date
This function can fully translate the string time.
The SQL statement can be written as follows:
SELECT str_to_date ('tablename'. 'eventtime', '% Y-% m-% d % H: % I: % s') from tablename
In the database, the eventTime type is VARCHAR (20), so that you can replace the type with the time type.
(2) to_days
Just like its name, it can only be converted to every day, that is, the time string of a day is converted into a number.
To:
The string type is converted to the time type. The string value must meet the 17:08:00 mode.
SELECT STR_TO_DATE ('2014/1/04/31: 2: 26', '% m/% d/% Y, % H: % I: % s'); success.
Note: The mode is case sensitive.
SELECT STR_TO_DATE ('1970-10-19, 17: 08 ',' % Y-% m-% d, % H: % I '); failed.
SELECT STR_TO_DATE ('1970-10-19,17: 08', '% Y-% m-% d, % H: % I'); after all spaces are removed, the operation succeeds.
SELECT STR_TO_DATE (replace (createTime, '',''), '% Y-% m-% d, % H: % I') from upload; the conversion result is displayed in advance.
As expected.
The official conversion is as follows:
Update upload set createTime = STR_TO_DATE (replace (createTime, '',''), '% Y-% m-% d, % H: % I ');
Update uzer set createTime = STR_TO_DATE (replace (createTime, '',''), '% Y-% m-% d, % H: % I ');
Update hlog set createTime = STR_TO_DATE (replace (createTime, '',''), '% Y-% m-% d, % H: % I ');
Convert the column definition to the datetime type.
Note that mysql is only accurate to seconds.
Note:
% W name of the Week (Sunday ...... Saturday)
% D indicates the date of the month with an English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4 digits
% Y year, number, 2 digits
% A abbreviated name of the Week (Sun ...... Sat)
% D number of days in the month (00 ...... 31)
% E number of days in the month (0 ...... 31)
% M month, number (01 ...... 12)
% C month, number (1 ...... 12)
% B abbreviated month name (Jan ...... Dec)
% J days in a year (001 ...... 366)
% H hour (00 ...... 23)
% K hour (0 ...... 23)
% H hour (01 ...... 12)
% I hour (01 ...... 12)
% L hour (1 ...... 12)
% I minute, number (00 ...... 59)
% R time, 12 hours (hh: mm: ss [AP] M)
% T time, 24 hours (hh: mm: ss)
% S seconds (00 ...... 59)
% S seconds (00 ...... 59)
% P AM or PM
% W days in a week (0 = Sunday ...... 6 = Saturday)
% U Week (0 ...... 52). Sunday is the first day of the week.
% U Week (0 ...... 52) Monday is the first day of the week.
% A text "% ".