Home > Article > Backend Development > FROM_UNIXTIME in Mysql implements query by day
Recently I have made a record system. The record needs to be checked on a daily basis. The number of points won and the number of games played every day must be listed. But, how to use sql to group by day? First, we need to confirm that there is a timestamp (int(10)) in our table. I use create_time here.
Next, start editing the statement
FROM_UNIXTIME function supports converting timestamps into strings. We convert timestamps into dates
FROM_UNIXTIME( create_time, '%Y年%m月%d日' )
Then, we can group according to this date Okay, here is the complete SQL statement
select FROM_UNIXTIME( create_time, '%Y年%m月%d日' ) as day,count(*) as count,sum(score) as score from t_everygames where user_id =$userid group by FROM_UNIXTIME( create_time, '%Y年%m月%d日' ) order by 1 limit 0,6
Similarly, you can also group by year, month, day, hour, and minute. Isn’t it very convenient?
If the database stores a string, such as 2018-1-30, the date_format() function is used.
Related recommendations:
Detailed explanation of the problem of index and FROM_UNIXTIME in mysql
Detailed explanation of the problem of index and FROM_UNIXTIME in mysql
FROM_UNIXTIME format MYSQL timestamp function
The above is the detailed content of FROM_UNIXTIME in Mysql implements query by day. For more information, please follow other related articles on the PHP Chinese website!