Mysql date and time and the conversion timestamp Function

The formula for using execl to convert the timestamp is: Code :( xxxxxxxxxx + 8*3600) 86400 + 70*365 + 19 use statements to explain the timestamp syntax example: Code: selectfrom_unixtime (1234567890, % y-% m-% d % h: % I: % s) attached: in mysql, the storage type of a time field is int (11 ), how to convert to the character type? For example, if the storage is 13270655222, the formula for using execl to convert the timestamp is:

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

Example of Time Stamp syntax interpretation using statements:

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 representing the 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 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 "% ".

Related functions

Mysql> select something from tbl_name
Where to_days (now ()-to_days (date_col) <= 30;

Dayofweek (date)
Returns the week index of date (1 = Sunday, 2 = Monday,... 7 = Saturday ). The index value complies with odbc standards.
Mysql> select dayofweek ('2017-02-03 ′);
-> 3

Weekday (date)
Returns the week index of date (0 = Monday, 1 = Tuesday ,... 6 = Sunday ):
Mysql> select weekday ('2017-02-03 22:23:00 ′);
-> 1
Mysql> select weekday ('2017-1997 ′);
-> 2

Dayofmonth (date)
The return date is the day of January 1, January, ranging from 1 to 31:
Mysql> select dayofmonth ('2017-02-03 ′);
-> 3

Dayofyear (date)
Returns the day of the year from 1 to 366:
Mysql> select dayofyear ('2017-02-03 ′);
-> 34

Month (date)
Returns the month from date, ranging from 1 to 12:
Mysql> select month ('2017-02-03 ′);
-> 2

Dayname (date)
Returns the week name of date:
Mysql> select dayname ("1998-02-05 ′);
-> 'Thursday'

Monthname (date)
Returns the month name of date:
Mysql> select monthname ("1998-02-05 ′);
-> 'February'

Quarter (date)
Returns the quarter of a year in which the date value ranges from 1 to 4:
Mysql> select quarter ('98-04-01 ′);
-> 2

Week (date)
Week (date, first)