Two Methods for converting a MySQL timestamp into a common readable time format

Use execl to convert TimestampThe formula is:

Code :

= (Xxxxxxxxxx + 8*3600)/86400 + 70*365 + 19


Use a MySQL statement to explain the timestampSyntax example:

Code:

Select from_unixtime (1234567890, '% Y-% m-% d % H: % I: % s ')


Appendix:
In MySQL, the storage type of a time field is int (11). How can it be converted to the character type? For example, if the storage is 13270655222, it must be converted to the yyyy-mm-dd format.
Use the from_unixtime function as follows:

Code:

From_unixtime (unix_timestamp, Format)

Returns a string indicating a Unix time mark, formatted according to the format string. Format can contain the same modifier as the entries listed by the date_format () function.
Format the date value based on the format string. The following modifier can be used in the format string: % m month name (January ...... December)
% W name of the Week (Sunday ...... Saturday)
% d Date of the month with the English prefix (1st, 2nd, 3rd, and so on .)
% Y year, number, 4-digit
% Y year, number, 2-digit
% A abbreviation of the week name (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 "% ".