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!