RE: [PHP-DB] It counts everything not a specific day..
> OK, > > Looks like there are two options > > I can either convert the unix timestamp colomn to a mysql > time one *OR* > someone can tell me how to use unix timestamps in this query... > Try something like this: SELECT CONCAT(extract(year FROM FROM_UNIXTIME(time)), extract(month FROM FROM_UNIXTIME(time))) MAX(COUNT(*)) as max_monthly_views, time FROM tececo_stats group by CONCAT(extract(year FROM FROM_UNIXTIME(time)), extract(month FROM FROM_UNIXTIME(time))) Regards Joakim -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] It counts everything not a specific day..
OK, Looks like there are two options I can either convert the unix timestamp colomn to a mysql time one *OR* someone can tell me how to use unix timestamps in this query... -- JJ Harrison [EMAIL PROTECTED] "John W. Holmes" <[EMAIL PROTECTED]> wrote in message news:001101c2777a$dcb96c10$7c02a8c0@;coconut... > Well, for one thing, EXTRACT() is for MySQL timestamps, not unix > timestamps. > > ---John Holmes... > > > -Original Message- > > From: Noodle Snacks [mailto:webmaster@;tececo.com] > > Sent: Saturday, October 19, 2002 7:36 AM > > To: [EMAIL PROTECTED] > > Subject: [PHP-DB] It counts everything not a specific day.. > > > > Although my query returns the date of the day that had the highest > number > > of > > records inserted. > > > > It outputs the total number of records in the table.. > > > > Can someone with more experience in SQL help me only count the number > of > > records inserted on the day with the highest number of records? > > > > BTW the column time is a UNIX timestamp that is set upon insert. > > > > here is my query: > > > > $query = " > > SELECT > > extract(year FROM time), > > extract(month FROM time), > > > > count(*) as monthly_views, > > time > > FROM > > tececo_stats > > group by > >extract(year FROM time), > >extract(month FROM time) > > order by > >monthly_views desc > > limit 1 > > "; > > > > > > -- > > JJ Harrison > > [EMAIL PROTECTED] > > > > > > > > -- > > PHP Database Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] It counts everything not a specific day..
Well, for one thing, EXTRACT() is for MySQL timestamps, not unix timestamps. ---John Holmes... > -Original Message- > From: Noodle Snacks [mailto:webmaster@;tececo.com] > Sent: Saturday, October 19, 2002 7:36 AM > To: [EMAIL PROTECTED] > Subject: [PHP-DB] It counts everything not a specific day.. > > Although my query returns the date of the day that had the highest number > of > records inserted. > > It outputs the total number of records in the table.. > > Can someone with more experience in SQL help me only count the number of > records inserted on the day with the highest number of records? > > BTW the column time is a UNIX timestamp that is set upon insert. > > here is my query: > > $query = " > SELECT > extract(year FROM time), > extract(month FROM time), > > count(*) as monthly_views, > time > FROM > tececo_stats > group by >extract(year FROM time), >extract(month FROM time) > order by >monthly_views desc > limit 1 > "; > > > -- > JJ Harrison > [EMAIL PROTECTED] > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] It counts everything not a specific day..
Although my query returns the date of the day that had the highest number of records inserted. It outputs the total number of records in the table.. Can someone with more experience in SQL help me only count the number of records inserted on the day with the highest number of records? BTW the column time is a UNIX timestamp that is set upon insert. here is my query: $query = " SELECT extract(year FROM time), extract(month FROM time), count(*) as monthly_views, time FROM tececo_stats group by extract(year FROM time), extract(month FROM time) order by monthly_views desc limit 1 "; -- JJ Harrison [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
