MySQL formatted date

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')  DATE _format (now (),'%d%b%Y%t:%f'

Output Result:

Dec  in  -  One: $PM A- in- -   in Dec  ,   in Dec  -  -: -: $ 

2. MySQL Format Function From_unixtime ()

SELECT ' %y-%c-%d%h:%i:%s '  as post_date,    ' %y-%c-%d%h:%i:%s '  as post_date_gmt     from ' article '  where='Y'

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> SelectFrom_unixtime (1344887103); +---------------------------+  |From_unixtime (1344887103)|  +---------------------------+  |  -- ,- - Geneva: $:Geneva       |  +---------------------------+  Rowinch Set(0.00Sec

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> SelectFrom_unixtime (1344887103,'%y-%m-%d%h:%i:%s'); +-----------------------------------------------+  |From_unixtime (1344887103,'%y-%m-%d%h:%i:%s')|  +-----------------------------------------------+  |  --August-14thGeneva: $:Geneva                     |  +-----------------------------------------------+  Rowinch Set(0.00sec) MySQL> SelectFrom_unixtime (1344887103,'%y-%m-%d%h:%i:%s'); +-----------------------------------------------+  |From_unixtime (1344887103,'%y-%m-%d%h:%i:%s')|  +-----------------------------------------------+  |  -- ,-14thGeneva: $:Geneva                         |  +-----------------------------------------------+  Rowinch Set(0.00Sec

3, judging is not the same day:

SELECTTbl_gamedata. Gamemapname,tbl_playerdata. Gamemode, Tbl_gamedata. Matchmode, Tbl_playerdata. Gameresult,SUM(Tbl_playerdata. Gameiswin) asTday_wincount,SUM(Tbl_playerdata. Assistcount) asTday_assistcount,SUM(Tbl_playerdata. Killcount) asTday_killcount,SUM(Tbl_player_title. Threekill) asTday_threekill,SUM(Tbl_player_title. Fourkill) asTday_fourkill,SUM(Tbl_player_title. Fivekill) asTday_fivekill fromTbl_playerdata Left JOINTbl_gamedata onTbl_playerdata. GameID=Tbl_gamedata. GameID Left JOINTbl_player_title onTbl_player_title. GameID=Tbl_playerdata. GameID andTbl_player_title. playerID=Tbl_playerdata. playerIDWHERETbl_playerdata. playerID= user_id  and(Tbl_playerdata. Gameresult= 2 ORTbl_playerdata. Gameresult= 3) andTo_days (From_unixtime (tbl_playerdata. Gamestarttime))=To_days (now ())GROUP  byTbl_gamedata. Gamemapname, Tbl_playerdata. Gamemode,tbl_gamedata. Matchmode,tbl_playerdata. Gameresult;

MySQL formatted date