Instructions for using the mysql timestamp formatting function from_unixtime

We generally use the field type int(11) timestamp to save the time, which facilitates query efficiency. 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 stamps , formatted according to format. If format is empty, the format %Y-%m-%d %H:%i:%s will be 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 date 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 in the month Number of days, 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, numbers (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 The week (0～52), where Sunday is the first day of the week Day
%u Week (0～52), here Monday is the first day of the week
%% 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)

This article explains the usage instructions of the MySQL timestamp formatting function from_unixtime. For more related content, please pay attention to the PHP Chinese website.

Related recommendations:

About the usage instructions of mysql functions concat and group_concat

##About mysql innodb failed to start and cannot be restarted Explanation of processing methods

#Explanation of php to obtain relevant content of the specified date

The above is the detailed content of Instructions for using the mysql timestamp formatting function from_unixtime. For more information, please follow other related articles on the PHP Chinese website!