MySQL Date string timestamp mutual transfer

Usually more commonly used time, string, time stamp between the conversion, although commonly used but almost every time you like to search for usage; This article will take a note of the conversion between the three (that is: date to string, date to timestamp, string to date, string to timestamp, Timestamp to date, timestamp to string) usage, convenient for later viewing;

The functions involved

Date_format (date, format) function, MySQL date format function date_format ()

Unix_timestamp () function

Str_to_date (str, format) function

From_unixtime (unix_timestamp, format) function, MySQL timestamp format function from_unixtime

Time-to-round string [SQL]previewing replication

1. Select Date_format (now (), '%y-%m-%d ');
3. #结果: 2016-01-05

Time-to-time stamping [SQL]previewing replication

1. Select Unix_timestamp (now ());
3. #结果: 1452001082

String Turn time [SQL]previewing replication

1. Select Str_to_date (' 2016-01-02 ', '%y-%m-%d%H ');
3. #结果: 2016-01-02 00:00:00

String Turn timestamp [SQL]previewing replication

1. Select Unix_timestamp (' 2016-01-02 ');
3. #结果: 1451664000

Time Stamp Turn time [SQL]previewing replication

1. Select From_unixtime (1451997924);
3. #结果: 2016-01-05 20:45:24

Timestamp to String [SQL]previewing replication

1. Select From_unixtime (1451997924,'%y-%d ');
3. Result: 2016-01-05 20:45:24

Schedule

MySQL date formatting (format) range of values.

	value	meaning
Seconds	%s,%s	Two digits in the form of seconds (00, 01, ..., 59)
Score of	%I,%I	Two digits in the form of points (00, 01, ..., 59)
Hours	%H	24-hour system, two-digit hour (00,01, ..., 23)
	%h	12-hour system, two-digit hour (00,01, ..., 12)
	%k	24-hour system, number of forms hours (0,1, ..., 23)
	%l	12-hour system, number of forms hours (0,1, ..., 12)
	%T	24-hour system, time form (HH:MM:SS)
	%r	12-hour, time form (Hh:mm:ss AM or PM)
	%p	Am Morning or PM afternoon
Week	%W	The name of each day of the week (Sunday,monday, ..., Saturday)
	%a	Abbreviation for the name of each day of the week (Sun,mon, ..., Sat)
	%w	Identify weeks (0=sunday,1=monday, ..., 6=saturday) in digital form
	%u	The number represents the week, and Sunday is the first day of the week
	%u	The number represents the week, and Monday is the first day of the week
Days	%d	Two-digit number of days in the month (01,02, ..., 31)
	%e	Number indicates the number of days in the month (1, 2, ..., 31)
	%d	The English suffix indicates the number of days in the month (1st,2nd,3rd ... ）
	%j	Three-digit number of days in the year (001,002, ..., 366)
Month	%M	English month name (January,february, ..., December)
	%b	English abbreviation month name (Jan,feb, ..., DEC)
	%m	Two digits indicate month (01,02, ..., 12)
	%c	Numbers indicate the month (1, 2, ..., 12)
Years	%Y	Four-digit year (2015,2016 ...)
	%y	Two-digit year (15,16 ...)
Text output	% text	Direct output of text content

MySQL date string timestamp cross-transfer