CODEWITHSUNDEEP
mysqlsql
James Dawson
James Dawson

Reputation: 5409

Grouping timestamp field by date

I am trying to write an SQL query to return how many links were submitted to my website over the last 7 day period. So far I have this:

SELECT COUNT(`id`) AS `count`
FROM `links`
WHERE `created` > NOW() - 86400
AND `created` < NOW()

this works for one day, it returns one row called count with the number of links submitted in the last 24 hours. I need to change it to return 2 columns called date and count, with 7 rows (one for each day).

The tricky part that I can't get my head around is that created is a timestamp column, and I don't have access to change it so I have to work with it.

Edit: work in progress for the query:

SELECT DAY(FROM_UNIXTIME(created)) AS day, COUNT(id) count
FROM links
GROUP BY DAY(FROM_UNIXTIME(created))
LIMIT 7

Upvotes: 0

Views: 64

Answers (1)

Kermit
Kermit

Reputation: 34054

NOW() actually shouldn't be working as it returns a datetime. Also, if you want to fetch 7 days worth of data, you want to subtract 604800 from UNIX_TIMESTAMP(). You can use then date and time functions with FROM_UNIXTIME. This will make grouping easier. Optimally, your column should be of datetime type.

It would go something like:

SELECT DAY(FROM_UNIXTIME(created)) day, COUNT(id) count
FROM links
WHERE created > UNIX_TIMESTAMP() - 604800 AND created < UNIX_TIMESTAMP()
GROUP BY DAY(FROM_UNIXTIME(created))

You can alternatively use the BETWEEN operator:

WHERE created BETWEEN UNIX_TIMESTAMP() - 604800 AND UNIX_TIMESTAMP()

See the demo

Upvotes: 2

Related Questions