Smartys wrote:
There's a MySQL function FROM_UNIXTIME that should do it
Heh. Yeah, just saw that. Thanks.
2 2007-03-27 20:06
Re: Selecting dates in SQL (7 replies, posted in PunBB 1.2 troubleshooting)
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
...3 2007-03-27 16:14
Re: Selecting dates in SQL (7 replies, posted in PunBB 1.2 troubleshooting)
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?
4 2007-03-26 17:19
Topic: Selecting dates in SQL (7 replies, posted in PunBB 1.2 troubleshooting)
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.