unix timestamps and group by

webosb · 02-13-2008, 04:44 AM

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..

webosb · 02-13-2008, 05:14 AM

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 :)

Salathe · 02-13-2008, 12:30 PM

SQL Code:

SELECT
    DATE_FORMAT(FROM_UNIXTIME(sup_date), '%m-%d-%Y') AS sdate,
    COUNT(id) AS scount
FROM
    users
WHERE
    -- post date must be between NOW and 30 DAYS ago
    sup_date <= NOW()
    AND sup_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY
    sdate

webosb · 02-13-2008, 02:41 PM

Quote:

Originally Posted by Salathe

SQL Code:

SELECT
    DATE_FORMAT(FROM_UNIXTIME(sup_date), '%m-%d-%Y') AS sdate,
    COUNT(id) AS scount
FROM
    users
WHERE
    -- post date must be between NOW and 30 DAYS ago
    sup_date <= NOW()
    AND sup_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY
    sdate

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