Detailed description of formatting dates in mysql and detailed description of mysql dates
1. the DATE_FORMAT () function is used to display date/time data in different formats.
DATE_FORMAT(date,format)
The format of the format parameter is
% |
Abbreviated name of a week |
% B |
Abbreviated month name |
% C |
Month, Value |
% D |
Day of the month with an English prefix |
% D |
Day of the month, numeric value (00-31) |
% E |
Day of the month, value (0-31) |
% F |
Microseconds |
% H |
Hour (00-23) |
% H |
Hour (01-12) |
% I |
Hour (01-12) |
% I |
Minute, value (00-59) |
% J |
Day of the year (001-366) |
% K |
Hour (0-23) |
% L |
Hour (1-12) |
% M |
Month name |
% M |
Month, value (00-12) |
% P |
AM or PM |
% R |
Time, 12-hour (hh: mm: ss AM or PM) |
% S |
Seconds (00-59) |
% S |
Seconds (00-59) |
% T |
Time, 24-hour (hh: mm: ss) |
% U |
Week (00-53) Sunday is the first day of the week |
% U |
Week (00-53) Monday is the first day of the week |
% V |
Week (01-53) Sunday is the first day of the week, used with % X |
% V |
Week (01-53) Monday is the first day of the week, used with % x |
% W |
Week name |
% W |
Day of the week (0 = Sunday, 6 = Saturday) |
% X |
Year, where Sunday is the first day of the week, four digits, used with % V |
% X |
Year, where Monday is the first day of the week, 4 digits, used with % v |
% Y |
Year, 4 digits |
% Y |
Year, two places |
Example:
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') DATE_FORMAT(NOW(),'%m-%d-%Y') DATE_FORMAT(NOW(),'%d %b %y') DATE_FORMAT(NOW(),'%d %b %Y %T:%f')
Output result:
Dec 29 2008 11:45 PM 12-29-2008 29 Dec 08 29 Dec 2008 16:25:46
2. MySQL Formatting Function FROM_UNIXTIME ()
SELECT FROM_UNIXTIME(date, '%Y-%c-%d %h:%i:%s' ) as post_date , date_format(NOW(), '%Y-%c-%d %h:%i:%s' ) as post_date_gmt FROM `article` where outkey = 'Y'
1. FROM_UNIXTIME (unix_timestamp)
Parameter: usually 10-bit timestamp, for example: 1417363200
Returned value: there are two types, which may be strings like 'yyyy-MM-DD HH: MM: ss' or similarYYYYMMDDHHMMSS.uuuuuu
The specific returned result of such a number depends on the form of the function being called.
mysql> select FROM_UNIXTIME(1344887103); +---------------------------+ | FROM_UNIXTIME(1344887103) | +---------------------------+ | 2012-08-14 03:45:03 | +---------------------------+ 1 row in set (0.00 sec)
2. FROM_UNIXTIME (unix_timestamp, format)
Unix_timestamp: And MethodFROM_UNIXTIME( unix_timestamp )
The parameter meanings are the same;
Format: the format of the converted time string;
Returned value: A string displayed in the specified time format;
mysql> select FROM_UNIXTIME(1344887103,'%Y-%M-%D %h:%i:%s'); +-----------------------------------------------+ | FROM_UNIXTIME(1344887103,'%Y-%M-%D %h:%i:%s') | +-----------------------------------------------+ | 2012-August-14th 03:45:03 | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql> select FROM_UNIXTIME(1344887103,'%Y-%m-%D %h:%i:%s'); +-----------------------------------------------+ | FROM_UNIXTIME(1344887103,'%Y-%m-%D %h:%i:%s') | +-----------------------------------------------+ | 2012-08-14th 03:45:03 | +-----------------------------------------------+ 1 row in set (0.00 sec)
3. Determine whether the day is the same:
SELECT tbl_gamedata.GameMapName,tbl_playerdata.GameMode, tbl_gamedata.MatchMode, tbl_playerdata.GameResult, SUM(tbl_playerdata.GameIsWin) AS tday_winCount, SUM(tbl_playerdata.AssistCount) AS tday_assistCount,SUM(tbl_playerdata.KillCount) AS tday_killCount, SUM(tbl_player_title.ThreeKill) AS tday_threeKill,SUM(tbl_player_title.FourKill) AS tday_fourKill,SUM(tbl_player_title.FiveKill) AS tday_fiveKill FROM tbl_playerdata LEFT JOIN tbl_gamedata ON tbl_playerdata.GameID = tbl_gamedata.GameID LEFT JOIN tbl_player_title ON tbl_player_title.GameID = tbl_playerdata.GameID AND tbl_player_title.PlayerID = tbl_playerdata.PlayerID WHERE tbl_playerdata.PlayerID = user_id AND (tbl_playerdata.GameResult = 2 OR tbl_playerdata.GameResult = 3) AND TO_DAYS(FROM_UNIXTIME(tbl_playerdata.GameStartTime)) = TO_DAYS(NOW()) GROUP BY tbl_gamedata.GameMapName, tbl_playerdata.GameMode,tbl_gamedata.MatchMode,tbl_playerdata.GameResult;
WhereTO_DAYS(FROM_UNIXTIME(tbl_playerdata.GameStartTime)) = TO_DAYS(NOW())
Is the judgment we need
Summary
The above is all about this article. I hope this article will help you in your study or work. If you have any questions, please leave a message.