The SQL statement includes date() function which doesn't exist on MySQL 4.0. So I am unable to see logs by day.

Original SQL:

  $sql_count = 'SELECT COUNT(DISTINCT(date(FROM_UNIXTIME(timestamp)))) FROM {adsense_clicks}';

  $sql = 'SELECT date(FROM_UNIXTIME(timestamp)) AS day, COUNT(*) AS count FROM {adsense_clicks} GROUP BY day' . 
    tablesort_sql($header);

My recommendation is:

  $sql_count = 'SELECT COUNT(DISTINCT(FROM_UNIXTIME(timestamp, \'%y-%m-%d\'))) FROM {adsense_clicks}';

  $sql = 'SELECT FROM_UNIXTIME(timestamp, \'%Y-%m-%d\') AS day, COUNT(*) AS count FROM {adsense_clicks} GROUP BY day' . 
    tablesort_sql($header);

But MySQL 4.0 has a bug I think.

FROM_UNIXTIME(timestamp, \'%Y-%m-%d\') should return something like 2006-07-21 but it returns 2006-07-0. Maybe %d must be something different.

I am using the SQL statement below temporarily.

  $sql_count = 'SELECT COUNT(DISTINCT( CONCAT( SUBSTRING(FROM_UNIXTIME(timestamp), 1, 4), \'-\', SUBSTRING(FROM_UNIXTIME(timestamp), 6, 2), \'-\', SUBSTRING(FROM_UNIXTIME(timestamp), 9, 2) ))) FROM {adsense_clicks}';

  $sql = 'SELECT  CONCAT( SUBSTRING(FROM_UNIXTIME(timestamp), 1, 4), \'-\', SUBSTRING(FROM_UNIXTIME(timestamp), 6, 2), \'-\', SUBSTRING(FROM_UNIXTIME(timestamp), 9, 2) ) AS day, COUNT(*) AS count FROM {adsense_clicks} GROUP BY day' . 
    tablesort_sql($header);

I tried lots of shorter and simpler statements but MySQL kept complaining. Only this one works.

CommentFileSizeAuthor
#7 adsense-sql.patch1.51 KBdarren oh

Comments

kbahey’s picture

kbahey commented

Gunaydin Erdem ...

I tested the second version, and it works fine, and as you said it should be 4.0 and 4.1 compatible.

I am not sure why you have a bug in 4.0 though.

My version has different quoting though:

  $sql_count = "SELECT COUNT(DISTINCT(FROM_UNIXTIME(timestamp, '%y-%m-%d'))) FROM {adsense_clicks}";

  $sql = "SELECT FROM_UNIXTIME(timestamp, '%Y-%m-%d') AS day, COUNT(*) AS count FROM {adsense_clicks} GROUP BY day" .
    tablesort_sql($header);

Should I go ahead and commit this instead of using DAY()?

Can anyone else test the above on 4.0?

kbahey’s picture

kbahey commented

This is very strange. I am seeing the same problem as you are seeing: the day comes as 0.

When I copy and paste the query in mysql command line, everything is fine. I get 7 rows.

When I run the query from the module, it says 2006-07-0 ...

Not sure what the issue is here ...

erdemkose’s picture

erdemkose commented

I think db_query() function tries to replace %d with an integer value. So is there any possibility to avoid this?

erdemkose’s picture

erdemkose commented

We need to use double '%'.


  $sql_count = "SELECT COUNT(DISTINCT(FROM_UNIXTIME(timestamp, '%%y-%%m-%%d'))) FROM {adsense_clicks}";

  $sql = "SELECT FROM_UNIXTIME(timestamp, '%%Y-%%m-%%d') AS day, COUNT(*) AS count FROM {adsense_clicks} GROUP BY day" .
    tablesort_sql($header);
erdemkose’s picture

erdemkose commented

PostgreSQL doesn't support FROM_UNIXTIME function.

We can use SUBSTRING or EXTRACT.

kbahey’s picture

kbahey commented
Title: MySQL 4.0 incompatibility at "adsense clicks by day" » Need PostgreSQL compatible query instead of FROM_UNIXTIME

I committed a fix for the % thing, and it works fine now, so should be compatible with MySQL 4.0.

If someone wants to submit a patch for PostgreSQL, then please do so.

darren oh’s picture

darren oh
Primary language English
Location Lakeland, Florida
commented
Assigned: Unassigned » darren oh
Status: Active » Needs review
StatusFileSize
new adsense-sql.patch1.51 KB

I couldn't come up with a query that worked in both MySQL and PostgreSQL, so I created separate queries for each. An alternative would be not to use queries to group results.

kbahey’s picture

kbahey commented
Status: Needs review » Reviewed & tested by the community

Seems like a good workaround to me.

Marking as RTBC.

I don't run postgresql, so can't test it.

darren oh’s picture

darren oh
Primary language English
Location Lakeland, Florida
commented
Status: Reviewed & tested by the community » Fixed

Fixed in CVS commit 47801. After studying the paging functions, I see that there is no alternative to using a query to group results.

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)