How to fetch last 24 hour record using unix timestamps

Question

I have following two tables in MySQL and I want to get name and count of reviews. How can I do this?

Here is my table un_users:

id      wallet_address          username    
1       xxxxxxxxxxxxxx          abc
2       xxxxxxxxxxxxxx          xyz
3       xxxxxxxxxxxxxx          def
4       xxxxxxxxxxxxxx          jkl

Here is my table coin_review:

id      wallet_address      review                  review_creation_time
1       xxxxxxxxxxxxx       Lorem Ipsum1            1632812650  
2       xxxxxxxxxxxxx       Lorem Ipsum2            1632812211
3       xxxxxxxxxxxxx       Lorem Ipsum3            1632812650
4       xxxxxxxxxxxxx       Lorem Ipsum4            1632814574

I tried with following code but not working:

SELECT u.username, COUNT(c.Review) AS totalreview
FROM un_users u
LEFT JOIN coin_review c ON c.wallet_address = u.wallet_address
GROUP BY u.username
ORDER BY u.username

I want to know how can I get "last 24 hours" records?

Sample data is great, but you also need to specify the expected result. — jarlh, Sep 28 '21 at 10:51
The error message is quite clear. You either need to add u.id and u.username to the GROUP BY, _or_ have these columns as arguments to set functions. — jarlh, Sep 28 '21 at 10:53
@amit do you want 0 counts too (i.e. users with 0 revires in 24 hours)? — Salman A, Sep 28 '21 at 11:04

Salman A · Accepted Answer · 2021-09-28T11:14:09.523

1

Assuming that review_creation_time is a unix timestamp, you can use UNIX_TIMESTAMP() function to compare apples to apples:

SELECT
    u.id, u.wallet_address, u.username,
    COUNT(c.id) AS review_count
FROM un_users u
JOIN coin_review c ON c.wallet_address = u.wallet_address
WHERE c.review_creation_time >  UNIX_TIMESTAMP(CURRENT_TIMESTAMP - INTERVAL 24 HOUR)
AND   c.review_creation_time <= UNIX_TIMESTAMP()
GROUP BY u.id

edited Sep 28 '21 at 11:14

answered Sep 28 '21 at 10:59

Salman A

262,204
82
430
521

i updated my question , please check – amit Sep 28 '21 at 11:01
giving me empty result – amit Sep 28 '21 at 11:05
its working now,Thanks :) – amit Sep 28 '21 at 11:12

How to fetch last 24 hour record using unix timestamps

1 Answers1