The formula for converting the timestamp using execl is:
Code:
= (xxxxxxxxxx + 8 * 3600) / 86400 + 70 * 365 + 19
Explain the timestamp syntax using the mysql tutorial statement Example:
Code:
select from_unixtime (1234567890, '% y-% m-% d% h:% i:% s')
Attachment:
In mysql, a time field storage type is int (11), how to convert into a character type, for example, stored as 13270655222, need to be converted into yyyy-mm-dd form from_unixtime function, as follows:
Code:
from_unixtime (unix_timestamp, format)
Returns a string representing the unix timestamp, formatted according to the format string. format can contain the same modifiers as the entries listed by the date_format () function.
Format the date value according to the format string. The following modifiers can be used in the format string:% m month name (january ... december)
% w week name (sunday ... saturday)
% d Date of the month with English prefix (1st, 2nd, 3rd, etc.)
% y year, number, 4 digits
% y year, number, 2 digits
% a abbreviated week name (sun ... sat)
% d The number of days in the month, the number (00 ... 31)
% e Number of days in the month, digits (0 ... 31)
% m month, number (01 ... 12)
% c month, number (1 ... 12)
% b abbreviated month name (jan ...... dec)
% j Number of days in a year (001 ... 366)
% h hours (00 ... 23)
% k hours (0 ... 23)
% h hours (01 ... 12)
% i hours (01 ... 12)
% l hour (1 ... 12)
% i minutes, numbers (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), where Sunday is the first day of the week
% u week (0 ... 52), where 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 weekday index of date (1 = sunday, 2 = monday, ... 7 = saturday). Index value in line with odbc standards.
mysql> select dayofweek ('1998-02-03');
-> 3
weekday (date)
Returns the index of the day of the week (0 = monday, 1 = tuesday, ... 6 = sunday):
mysql> select weekday ('1998-02-03 22:23:00');
-> 1
mysql> select weekday ('1997-11-05');
-> 2
dayofmonth (date)
The return date is the first few days of January, ranging from 1 to 31:
mysql> select dayofmonth ('1998-02-03');
-> 3
dayofyear (date)
Return date is the first few days of the year, the range of 1 to 366:
mysql> select dayofyear ('1998-02-03');
-> 34
month (date)
Returns the month in the date range from 1 to 12:
mysql> select month ('1998-02-03');
-> 2
dayname (date)
The name of the week that returns date
mysql> select dayname ("1998-02-05 ');
-> 'thursday'
monthname (date)
Returns the name of the month of the date:
mysql> select monthname ("1998-02-05 ');
-> 'february'
quarter (date)
Returns the date in the quarter of the year in the range of 1 to 4:
mysql> select quarter ('98 -04-01 ');
-> 2
week (date)
week (date, first)