MySQL formatted the DATE function

MySQL Query record if there is a timestamp field, the view results inconvenient, can not immediately see the meaning of the timestamp represents, now provides the MySQL format change time function, you can easily see the time after the format.

1. The Date_format () function is used to display date/time data in different formats.

The format parameter is formatted with

%a	Abbreviated Week name
%b	Abbreviated month name
%c	Month, value
%d	Day of the month with English prefixes
%d	Day of the month, value (00-31)
%e	Day of the month, value (0-31)
%f	Microseconds
%H	Hours (00-23)
%h	Hours (01-12)
%I	Hours (01-12)
%i	Minutes, value (00-59)
%j	Days of the Year (001-366)
%k	Hours (0-23)
%l	Hours (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, with%x
%v	Week (01-53) Monday is the first day of the week, with%x
%W	Week name
%w	Day of the week (0= Sunday, 6 = Saturday)
%x	year, of which Sunday was the first day of the week, 4-bit, with%V used
%x	year, of which Monday was the first day of the week, 4-bit, with%v used
%Y	Year, 4 guests
%y	Year, 2 guests

Example:

Date_format (now (), '%b%d%Y%h:%i%p ')  Date_format (now (), '%m-%d-%y ')  Date_format (now (), '%d%b%Y ')  

Output Result:

Dec 11:45 PM  12-29-2008  08  

2. MySQL Format 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 '  

1, From_unixtime (Unix_timestamp)

Parameters: typically 10-bit timestamps, such as: 1417363200

Return value: There are two types of strings that may be similar to ' yyyy-mm-dd HH:MM:SS ', or a number similar to Yyyymmddhhmmss.uuuuuu, depending on what form the function is called.

Mysql> Select From_unixtime (1344887103);  +---------------------------+  | From_unixtime (1344887103) |  +---------------------------+  | 2012-08-14 03:45:03       |  +---------------------------+  

2, From_unixtime (unix_timestamp, format)
Parameter unix_timestamp: Same as the parameter meaning in method From_unixtime (Unix_timestamp);
Parameter format: The formatting of the time string displayed after the conversion;
Return value: The 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                         |  +-----------------------------------------------+    

3, judging is not the same day:

 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 joins 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; 

Among them To_days (From_unixtime (tbl_playerdata. Gamestarttime) = To_days (now ()) is the judgment we need

MySQL formatted the DATE function