Home  >  Article  >  Backend Development  >  FROM_UNIXTIME in Mysql implements query by day

FROM_UNIXTIME in Mysql implements query by day

小云云
小云云Original
2018-03-22 11:04:142419browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn