MySQL Date and Unix timestamp are converted to each other

Sample code:

1 2	#先把当前时间转化成UNIX时间戳，然后在格式化成日期 SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()),  '%Y-%m-%d|%H:%i:%s|%W|%w|%j|%T|%p|%r|%U|%u|%%' )  AS '把UNIX时间戳转化成日期' ;

Second, convert the date into a Unix timestamp.

Function: Unix_timestamp.

Syntax: Unix_timestamp (date);//data is not a required parameter, the default is the current time, a UNIX timestamp (unsigned integer) that returns a date tag.

Sample code:

1 2	#不加 date 参数 SELECT UNIX_TIMESTAMP()  AS '当前时间UNIX时间戳' ;

1 2	#加 date 参数 SELECT UNIX_TIMESTAMP( '2014-04-17 10:33:35' )  AS '把日期转化成UNIX时间戳' ;

Unix timestamp converted to date with function From_unixtime ()

Select From_unixtime (1156219870);

Date converted to Unix timestamp with function unix_timestamp ()

Select unix_timestamp (' 2006-11-04 12:23:00′);

Example: MySQL Query the number of records of the day:

$sql = "SELECT * FROM Message Where date_format (From_unixtime (chattime), '%y-%m-%d ') = Date_format (now (), '%y-%m-%d ') ORDER BY id DESC ";

Let's look at some examples.

S

First, turn the Unix timestamp into a date.

Function: From_unixtime.

Syntax: From_unixtime (unix_timestamp, format);//Returns a date string (is a string type, not a date type) for the Unix timestamp token.

The format modifiers are as follows:

The year of the

1, year
%y:4 bit (2014).
The year of the%y:2 bit (14).
2, Month
%m: English full spelling (January ...) December).
%b: English abbreviation (...) DEC).
%m: Number (01 ...). 12).
%c: Number (1 ...). 12).
3, Day
%d: with English suffix (1st, 2nd, 3rd ...).
%d: Number (00 ...). 31).
%e: Number (0 ...). 31).
4, when
%H: Number (00 ...). 23).
%k: Number (0 ...). 23).
%h: Number (01 ...). 12).
%I: Number (01 ...). 12).
5, Minutes
%i: Number (00 ...). 59).
6, Sec
%s: Number (00 ...). 59).
%s: Number (00 ...). 59).
7, Week
%w: English full spelling (Sunday ...) Saturday).
%a: English abbreviation (Sun ...) Sat).
%w: Number (0=sunday ...) 6=saturday).
8, day of the year
%j: Number (001 ...). 366).
9, other
%p:am or PM
%T: Time, 24 hours (HH:MM:SS).
%r: Time, 12 hours (Hh:mm:ss [ap]m).
%u: The first few weeks of the year (0 ...). 52), here Sunday is the first day of the week.
%u: The first few weeks of the year (0 ...). 52), here Monday is the first day of the week.
percent%: a literal "%".