CODEWITHSUNDEEP
phpmysqlsql
iSun
iSun

Reputation: 1754

Query between two timestamp date

I'm trying to show my downloads query between today and one weeks ago, here is my query

    SELECT 
        COUNT(1) AS d_count, 
        d_timestamp 
    FROM dl_table 
    WHERE id = 'someid'
        AND d_timestamp BETWEEN 1389830400 AND 1389260583 
    GROUP BY d_timestamp 
    ORDER BY d_timestamp ASC LIMIT 7;

The above query didn't work correctly and return me a null column or return me 7 column with 1 value in d_count which is completely wrong!!! Where is the problem ?

Any ideas?

Notice:

1389830400 equal to today timestamp which I get it via strtotime("today");.

1389260583 equal to -1 week ago timestamp which I get it via strtotime("-7 day");.

d_timestamp is an integer column.

Upvotes: 0

Views: 131

Answers (2)

Krishna
Krishna

Reputation: 438

Try this query as it seems there some problem with d_timestamp column values
SELECT COUNT(1) AS d_count, d_timestamp FROM dl_table WHERE id = 'someid' AND CAST(d_timestamp AS UNSIGNED) BETWEEN 1389830400 AND 1389260583 GROUP BY d_timestamp ORDER BY d_timestamp ASC LIMIT 7;

Upvotes: 0

Michael Kunst
Michael Kunst

Reputation: 2988

SELECT
  COUNT(id) AS downloads,
  FROM_UNXTIME(d_timestamp, '%Y-%m-%d') AS day
FROM dl_table
WHERE id = 'someid'
AND d_timestamp BETWEEN 1389830400 AND 1389260583 
GROUP BY day
ORDER BY day

This should work. Formatting the timestamp is more readable, and it makes 100% sure you group by the day, even if the timestamp doesn't match 00:00 exactly (for whatever reason that could be).

Upvotes: 2

Related Questions