How to use the mysql timestamp format function from_unixtime

In order to improve query efficiency, we usually use int(11) type timestamp to store time. But this has a disadvantage. The displayed timestamp makes it difficult to know the real date and time.

mysql provides a timestamp formatting function from_unixtime to convert the format

from_unxitime syntax description:

from_unixtime(unix_timestamp, format)

Returns a string of Unix time stamp , formatted according to format. If format is not set, the format %Y-%m-%d %H:%i:%s

is used by default. For example:

mysql> select from_unixtime(1459338786);
+---------------------------+
| from_unixtime(1459338786) |
+---------------------------+
| 2016-03-30 19:53:06       |
+---------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1459338786, '%Y-%m-%d %H:%i:%s');
+------------------------------------------------+
| from_unixtime(1459338786, '%Y-%m-%d %H:%i:%s') |
+------------------------------------------------+
| 2016-03-30 19:53:06                            |
+------------------------------------------------+
1 row in set (0.00 sec)

format Format description:

• %M Month name (January～December)

• ##%W Week name (Sunday～Saturday)

• %D Day of the month with English prefix (1st, 2nd, 3rd, etc.)

• %Y Year, number, 4 digits

• %y Year, number, 2 digits

• %a Abbreviated name of the week (Sun～Sat)

• %d Number of days in the month, number (00～31)

• %e Number of days in the month, number (0～31)

• %m month, number (01～12)

• %c month, number (1～12)

• % b Abbreviated month name (Jan～Dec)

##%j Number of days in a year (001～366)
%H hours ( 00～23)
%k hours (0～23)
%h hours (01～12)
%I hours (01～12)
%l hours (1～12)
%i minutes, Number (00～59)
%r time, 12 hours (hh:mm:ss [AP]M)
%T time ,24 hours (hh:mm:ss)
%S seconds (00～59)
%s seconds (00～59)
%p AM or PM
%w The number of days in a week (0=Sunday ~ 6=Saturday)
%U Week (0～52), here Sunday is the first day of the week

• ##%u Week (0～52), here Monday is the day of the week The first day of

• %% a text%
Example: Count by hour

mysql> select from_unixtime(addtime,'%Y-%m-%d %H') as date,count(*) from `table` group by from_unixtime(addtime,'%Y-%m-%d %H');+---------------+----------+| date | count(*) |+---------------+----------+| 2016-03-30 19 | 409 || 2016-03-30 20 | 161 |+---------------+----------+2 rows in set (0.00 sec)

The above is the detailed content of How to use the mysql timestamp format function from_unixtime. For more information, please follow other related articles on the PHP Chinese website!