You are not logged in. Please login or register.
PunBB Forums → PunBB 1.2 troubleshooting → Selecting dates in SQL
Okay, I'm stumped. I've searched for this answer but haven't been able to figure it out. How do i do some date magic using SQL when the dates are stored as ints? I'm trying to pull out a report (using phpMyAdmin) that shows me the average number of posts a day. I've tried all kinds of conversions but can't seem to figure it out. Any help would be appreciated. Thanks.
How would you write the query if you had a full datestamp?
I guess I'm trying to get output like this:
day #posts
1 12
2 23
3 14
...
SQL something like so:
SELECT DAY(posted), COUNT(id) FROM topics GROUP BY DAY(posted)None of the date functions work since posted is an integer and not a datetime. Do I need to do some conversion?
What does the table look like?
There's a MySQL function FROM_UNIXTIME that should do it 
I'm selecting from the topics table from punbb. I just wasn't sure what I needed to do to get those int(10) columns to behave like dates. I think I figured out what I needed to do by using the FROM_UNIXTIME() function.
SELECT DAYOFYEAR(FROM_UNIXTIME(posted)), COUNT(id)
FROM topics
GROUP BY (DAYOFYEAR(FROM_UNIXTIME(posted)))That yields me something like:
day numposts
2 2
3 1
8 1
12 2
15 1
16 1
17 2
...Yeah, that's probably the easiest way to adapt what you had for PunBB. There probably is another way to do it with division and rounding of the timestamps, but your way looks nicer 
There's a MySQL function FROM_UNIXTIME that should do it
Heh. Yeah, just saw that. Thanks.
PunBB Forums → PunBB 1.2 troubleshooting → Selecting dates in SQL
Powered by PunBB, supported by Informer Technologies, Inc.