unix timestamps and group by
OK, i'm having trouble with a query that I hope someone can help me with...
I have a table that stores signup dates in unix timestamp format and I'm having problems trying to output data in this way:
date | signups
02-01-2008 | 5
02-02-2008 | 50
02-03-2008 | 10
02-04-2008 | 20
02-05-2008 | 25
SELECT sup_date, COUNT(id) FROM users WHERE sup_date > "1199145600" AND sup_date < "1202774400" GROUP BY sup_date
is there a way to convert the timestamps to output dates???
my goal is to be able to query the amount of signups for each day for the past 30 days - maybe i'm going about it wrong..
i figured it out, here's what i did if this is useful to anyone else
SELECT DATE_FORMAT(FROM_UNIXTIME(sup_date),"%m-%d-%Y") as sdate, COUNT(id) as scount FROM users WHERE sup_date > "1199145600" AND sup_date < "1202774400" GROUP BY sdate
but if anyone want to give me pointers on how I can change this query to automatically set 30 days from today - that would be super :)
I had to change it a lil bit to:
WHERE sup_date <= UNIX_TIMESTAMP(NOW())
AND sup_date >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))
but it works great Salathe. much thanks. you've been very helpful
|All times are GMT. The time now is 04:34 PM.|
Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0