What does <=> in MySQL mean and do?

The manual says it all:

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

mysql> select NULL <=> NULL;
+---------------+
| NULL <=> NULL |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> select NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

mysql> select NULL <=> 1;
+------------+
| NULL <=> 1 |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

mysql> select NULL = 1;
+----------+
| NULL = 1 |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

mysql> 

I have an application in Zend Framework that has to run for a different time zone than the server. Is there an option to set the database server (mysql in this case) time zone in application.ini?

My current options are:
resources.db.adapter = "Pdo_Mysql"
resources.db.params.charset = "utf8"
resources.db.params.driver_options.1002 = "SET NAMES utf8"
resources.db.params.host = "localhost"
resources.db.params.username = "usernam"
resources.db.params.password = "password"
resources.db.params.dbname = "databasename"

I know I can do something like SET timezone = 'Europe/London', but I really need to do it in the config file.

EDIT

Googling around I found that resources.db.params.driver_options.1002 should be setting the value for PDO::MYSQL_ATTR_INIT_COMMAND.

Therefore resources.db.params.driver_options.1002 = "SET NAMES utf8, time_zone = 'Europe/London'" should do the trick. But no luck so far.

FINAL EDIT

Found it. After a lot of searching and debugging I found the following code at Zend\Db\Adapter\Pdo\Mysql.php

if (!empty($this->_config['charset'])) {
    $initCommand = "SET NAMES '" . $this->_config['charset'] . "'";
    $this->_config['driver_options'][1002] = $initCommand; // 1002 = PDO::MYSQL_ATTR_INIT_COMMAND
}

As I have resources.db.params.charset = "utf8" in my application.ini, it was overwriting PDO::MYSQL_ATTR_INIT_COMMAND.

Deleting that line solved it.

I'm answering my own question just to close this, as the response is already in one of the edits.

resources.db.params.charset

and

resources.db.params.driver_options.1002

as I had in my application.ini cannot be used together, as resources.db.params.charset ovewirites driver_options.1002.

If you need to set the timezone, remove resources.db.params.charset and pass the timezone in the charset in driver_options. E.g.:

resources.db.params.driver_options.1002 = "SET NAMES utf8, time_zone = 'Europe/London'"

Hello all, I have a database, which I query, and I'm unsure of where to perform the sorting of the results, so far I've have the following options.

• At the MySQL query.
• At list level(Using a LinkedList)
• Sorting an unsorted list using comparators before showing the results (basically in the jsp)

The List is composed by ObjectDTO so where would it be more efficient. Any ideas?

You should do the sorting in the database if at all possible.

• The database can use indexes. If there is a suitable index available then the results can be read from disk already in sorted order, resulting in a performance increase - no extra O(n log(n)) sorting step is required.
• If you only need the first x results you also minimize data transfer (both reduced network transfer, and also reduced disk access if there is a suitable index).

The setup...

Trying to set up a clean Mac os X 10.6 install to develop python/django and I didn't remember running into this on 10.5.

After installing MySQL from the installer on mysql-5.5.8-osx10.6-x86_64.dmg I ran

$ sudo pip install MySQL-python

and it seemed to go smoothly (output below)

Downloading/unpacking MySQL-python
  Downloading MySQL-python-1.2.3.tar.gz (70Kb): 70Kb downloaded
  Running setup.py egg_info for package MySQL-python
    warning: no files found matching 'MANIFEST'
    warning: no files found matching 'ChangeLog'
    warning: no files found matching 'GPL'
Installing collected packages: MySQL-python
  Running setup.py install for MySQL-python
    building '_mysql' extension
    gcc-4.2 -fno-strict-aliasing -fno-common -dynamic -DNDEBUG -g -fwrapv -Os -Wall -Wstrict-prototypes -DENABLE_DTRACE -pipe -Dversion_info=(1,2,3,'final',0) -D__version__=1.2.3 -I/usr/local/mysql/include -I/System/Library/Frameworks/Python.framework/Versions/2.6/include/python2.6 -c _mysql.c -o build/temp.macosx-10.6-universal-2.6/_mysql.o -Os -g -fno-common -fno-strict-aliasing -arch x86_64
    In file included from _mysql.c:36:
    /usr/local/mysql/include/my_config.h:325:1: warning: "SIZEOF_SIZE_T" redefined
    In file included from /System/Library/Frameworks/Python.framework/Versions/2.6/include/python2.6/Python.h:9,
                     from pymemcompat.h:10,
                     from _mysql.c:29:
    /System/Library/Frameworks/Python.framework/Versions/2.6/include/python2.6/pymacconfig.h:33:1: warning: this is the location of the previous definition
    In file included from _mysql.c:36:
    /usr/local/mysql/include/my_config.h:419:1: warning: "HAVE_WCSCOLL" redefined
    In file included from /System/Library/Frameworks/Python.framework/Versions/2.6/include/python2.6/Python.h:8,
                     from pymemcompat.h:10,
                     from _mysql.c:29:
    /System/Library/Frameworks/Python.framework/Versions/2.6/include/python2.6/pyconfig.h:803:1: warning: this is the location of the previous definition
    gcc-4.2 -Wl,-F. -bundle -undefined dynamic_lookup build/temp.macosx-10.6-universal-2.6/_mysql.o -L/usr/local/mysql/lib -lmysqlclient_r -lpthread -o build/lib.macosx-10.6-universal-2.6/_mysql.so -arch x86_64
    warning: no files found matching 'MANIFEST'
    warning: no files found matching 'ChangeLog'
    warning: no files found matching 'GPL'
Successfully installed MySQL-python
Cleaning up...

after this I tried:

$ python -c "import MySQLdb"

and it crapped out on me with:

Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/Library/Python/2.6/site-packages/MySQLdb/__init__.py", line 19, in <module>
    import _mysql
ImportError: dlopen(/Library/Python/2.6/site-packages/_mysql.so, 2): Library not loaded: libmysqlclient.16.dylib
  Referenced from: /Library/Python/2.6/site-packages/_mysql.so
  Reason: image not found

So on to my question...

What did I do wrong?/What else do I need to do?

Googling (and searching here) for this returns a lot of results getting this error message with Ruby not too many with Python tho.

_mysql.so refers to libmysqlclient.16.dylib. That is, the shared library that serves as the bridge between Python and the MySQL client library, _mysql.so, refers to the dynamic library for the MySQL client library, and that library cannot be loaded for some reason.

Questions you need to answer:

• Is there a libmysqlclient.16.dylib anywhere on your system? If not, you need to install the MySQL client software.
• If so, is the directory containing that library in your DYLD_LIBRARY_PATH setting? If not, try adding it.
• If so, you'll have to ensure that the libmysqlclient.16.dylib file is not corrupt. My copy, installed in /opt/local/lib/mysql5/mysql/libmysqlclient.16.dylib, courtesy of MacPorts, has MD5 signature c79ee91af08057dfc269ee212915801a and is 1,462,376 bytes in size. What does your copy look like?

I have a web application that I am currently working on that uses a MySQL database for the back-end, and I need to know what is better for my situation before I continue any further.

Simply put, in this application users will be able to construct their own forms with any number fields (they decide) and right now I have it all stored in a couple tables linked by foreign keys. A friend of mine suggests that to keep things "easy/fast" that I should convert each user's form to a flat table so that querying data from them stays fast (in case of large growth).

Should I keep the database normalized with everything pooled into relational tables with foreign keys (indexes, etc) or should I construct flat tables for every new form that a user creates?

Obviously some positives of creating flat tables is data separation (security) and query speeds would be cut down. But seriously how much gain would I get from this? I really don't want 10000 tables and to be dropping, altering, and adding all of the time, but if it will be better than I will do it... I just need some input.

Thank you

Rule of thumb. It's easier to go from normalized to denormalized than the other way around.

Start with a reasonable level of database normalization (by reasonable I mean readable, maintainable, and efficient but not prematurely optimized), then if you hit performance issues as you grow, you have the option of looking into ways in which denormalization may increase performance.

I can't for the life of me remember what a page is, in the context of a MySQL database. When I see something like 8KB/page, does that mean 8KB per row or ...?

Database pages are the internal basic structure to organize the data in the database files. Here some informations about the InnoDB model.

From 13.2.11.2. File Space Management:

The data files that you define in the configuration file form the InnoDB tablespace. The files are logically concatenated to form the tablespace. [...] The tablespace consists of database pages with a default size of 16KB. The pages are grouped into extents of size 1MB (64 consecutive pages). The “files” inside a tablespace are called segments in InnoDB.

And from 13.2.14. Restrictions on InnoDB Tables

The default database page size in InnoDB is 16KB. By recompiling the code, you can set it to values ranging from 8KB to 64KB.

Further, to put rows in relation to pages:

The maximum row length, except for variable-length columns (VARBINARY, VARCHAR, BLOB and TEXT), is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including BLOB and TEXT columns, must be less than 4GB.

What is the difference between mysql-5.5.8.zip and mysql-5.5.8-win32.zip in the http://dev.mysql.com/downloads/mysql/ download page. I have a Win 2K environment, what binary should I use and why does it have a huge size difference between the above?

The smaller package does not contain test suite and embedded server.

Anyway, I suggest you use the MSI installer instead - installation and configuration are way more straightforward.

The MySQL documentation implies that you can assign one or more of a table's indexes to a named key buffer (and preload them). The syntax definition in the manual is:

CACHE INDEX
  tbl_index_list [, tbl_index_list] ...
  IN key_cache_name

tbl_index_list:
  tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]

which seems to say that you could assign just one of a table's indexes to the named key buffer. For example:

SET GLOBAL my_keys.key_buffer_size=512*1048576;
CACHE INDEX my_table KEY (PRIMARY) INTO my_keys;
LOAD INDEX INTO CACHE my_table KEY (PRIMARY);

would load only the PRIMARY index of my_table.

But from what I can tell, it doesn't work like that, at least, not in 5.0.87. Instead, the server appears to load all the table's indexes, effectively ignoring the index list part in parenthesis.

For example, I have a big dictionary table:

CREATE TABLE dict (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(330) NOT NULL,
  UNIQUE KEY (name) );

Now, if I attempt to load just the PRIMARY index, the mysqld's resident size in memory increases by the size of dict.MYI (733 MB in my example) which is buch bigger than the size of the PRIMARY index alone (103 MB).

---

UPDATE 2011-01-08: The documentation for CACHE INDEX actually provides the answer:

The syntax of CACHE INDEX enables you to specify that only particular indexes from a table should be assigned to the cache. The current implementation assigns all the table's indexes to the cache, so there is no reason to specify anything other than the table name.

If I would have properly read the very documentation I referenced in the OP, none of this would ever have happened.

fsb's answer to his own question for those who missed the update.

The syntax of CACHE INDEX enables you to specify that only particular indexes from a table should be assigned to the cache. The current implementation assigns all the table's indexes to the cache, so there is no reason to specify anything other than the table name.

Hi,

I am working on a web application which involves create list of Restaurants in various lists like "Joe's must visit places". Now for each Restaurant and list, I have display on website which calculates

• Calculating popularity of a Restaurant
• Popularity of a list
• Number of lists a Restaurant is present in

Currently I am using MySQL statements in PHP for this but planning to switch to MySQL VIEWS and do a simple select statement in PHP...

my question is, What is Advantage/Disadvantage of using VIEWS over writing sql queries in PHP?

Using views adds a level of abstraction : you may later change the structure of your tables, and you will not have to change the code that displays the information about the lists, because you will still be querying the view (the view definition may change, though).

The main difference is that views are updated after each insertion, such that the data is "ready" whenever you query the view, whereas using your custom query will have MySQL compute everything each time (there is some caching, of course).

The bottom line is that if your lists are updated less frenquently than they are viewed, you will see some gains in performance in using views.

Every now and then I see these being used, but it never seems to be anything that can't be performed as equally well, if not better, by using a normal join or subquery.

I see them as being misleading (they're arguably harder to accurately visualize compared to conventional joins and subqueries), often misunderstood (e.g. using SELECT * will behave the same as SELECT 1 in the EXISTS/NOT EXISTS subquery), and from my limited experience, slower to execute.

Can someone describe and/or provide me an example where they are best suited or where there is no option other than to use them? Note that since their execution and performance are likely platform dependent, I'm particularly interested in their use in MySQL.

Every now and then I see these being used, but it never seems to be anything that can't be performed as equally well, if not better, by using a normal join or subquery.

This article (though SQL Server related):

• IN vs. JOIN vs. EXISTS

may be of interest to you.

In a nutshell, JOIN is a set operation, while EXISTS is a predicate.

In other words, these queries:

SELECT  *
FROM    a
JOIN    b
ON      some_condition(a, b)

vs.

SELECT  *
FROM    a
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    b
        WHERE   some_condition(a, b)
        )

are not the same: the former can return more than one record from a, while the latter cannot.

Their counterparts, NOT EXISTS vs. LEFT JOIN / IS NULL, are the same logically but not performance-wise.

In fact, the former may be more efficient in SQL Server:

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server

Hi,

I have been experimenting with Redis and MongoDB lately and it would seem that there are often cases where you would store an array of id's in either MongoDB or Redis. I'll stick with Redis for this question since I am asking about the MySQL IN operator.

I was wondering how performant it is to do a large amount (300-3000) of id's inside the IN operator, which would look something like this:

SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 3000)

Imagine something as simple as a products and categories table which you might normally JOIN together to get the products from a certain category. In the example above you can see that under a given category in Redis ( category:4:product_ids ) I return all the product ids from the category with id 4, and place them in the above SELECT query inside the IN operator.

How performant is this?

Is this an "it depends" situation? Or is there a concreet "this is (un)acceptable" or "fast" or "slow" or should I add a LIMIT 25, or doesn't that help?

SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 3000)
LIMIT 25

Or should I trim the array of product id's returned by Redis to limit it to 25 and only add 25 id's to the query rather than 3000 and LIMIT-ing it to 25 from inside the query?

SELECT id, name, price
FROM products
WHERE id IN (1, 2, 3, 4, ...... 25)

Any suggestions/feedback is much appreciated!

Generally speaking, if the IN list gets too large (for some ill-defined value of 'too large' that is usually in the region of 100 or smaller), it becomes more efficient to use a join, creating a temporary table if need so be to hold the numbers.

If the numbers are a dense set (no gaps - which the sample data suggests), then you can do even better with WHERE id BETWEEN 300 AND 3000. However, presumably there are gaps in the set, at which point it may be better to go with the list of valid values after all (unless the gaps are relatively few in number, in which case you could use: WHERE id BETWEEN 300 AND 3000 AND id NOT BETWEEN 742 AND 836 or whatever the gaps are.

I'm planning to store working hours for shops. I'm wondering what might be the best modeling for the working hours field so that I can get a list of open/closed shops at the current moment in very efficient way.

To store normal operation hours, you would need to store a number of records containing:

• Shop - INTEGER
• DayOfWeek - INTEGER (0-6)
• OpenTime - TIME
• CloseTime - TIME

I assume for example that each shop has reduced hours during national holidays, or has plant shutdowns, so you would also need to store some override records:

• Shop - INTEGER
• OverrideStartDate - DATE
• OverrideEndDate - DATE
• DayOfWeek - INTEGER (0-6)
• AltOpenTime - TIME
• AltCloseTime - TIME
• Closed - INTEGER (0, 1)

To find open shops is trivial, but you also need to check if there are override hours:

SELECT Shop
FROM OverrideHours
WHERE OverrideStartDate <= NOW()
AND OverrideEndDate >= NOW()
AND DayOfWeek = WEEKDAY(NOW())

If there are any record returned, those shops have alternate hours or are closed.

There may be some nice SQL-fu you can do here, but this gives you the basics.

EDIT

I haven't tested this, but this should get you close:

SELECT Normal.Shop
FROM Normal
LEFT JOIN Override
ON Normal.Shop = Override.Shop
AND Normal.DayOfWeek = Override.DayOfWeek
AND NOW() BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate
WHERE Normal.DayOfWeek = WEEKDAY(NOW())
AND ((Override.Shop IS NULL AND TIME(NOW()) BETWEEN Normal.OpenTime AND Normal.CloseTime)
 OR  (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(NOW()) BETWEEN Override.AltOpenTime AND Override.AltCloseTime))

EDIT

As for efficiency, it is efficient in the sense that you only have to make one call to MySQL which is often a bottleneck if it is across a network. You'll have to test and see whether this performs to your specifications. If not, you may be to play with some indices.

EDIT

Testing. Not complete testing, but some.

mysql> select * from Normal;
+------+-----------+----------+-----------+
| Shop | DayOfWeek | OpenTime | CloseTime |
+------+-----------+----------+-----------+
|    1 |         1 | 09:00:00 | 17:00:00  | 
|    1 |         5 | 09:00:00 | 16:00:00  | 
|    2 |         1 | 09:00:00 | 17:00:00  | 
|    2 |         5 | 09:00:00 | 17:00:00  | 
+------+-----------+----------+-----------+
4 rows in set (0.01 sec)

mysql> select * from Override;
+------+-------------------+-----------------+-----------+-------------+--------------+--------+
| Shop | OverrideStartDate | OverrideEndDate | DayOfWeek | AltOpenTime | AltCloseTime | Closed |
+------+-------------------+-----------------+-----------+-------------+--------------+--------+
|    2 | 2010-12-01        | 2010-12-31      |         1 | 09:00:00    | 18:00:00     |      0 | 
|    2 | 2010-12-01        | 2010-12-31      |         5 | 09:00:00    | 18:00:00     |      0 | 
|    1 | 2010-12-01        | 2010-12-31      |         1 | 09:00:00    | 17:00:00     |      1 | 
+------+-------------------+-----------------+-----------+-------------+--------------+--------+
3 rows in set (0.00 sec)

mysql> SET @whenever = TIMESTAMP('2010-11-23 16:05');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT WEEKDAY(@whenever);
+--------------------+
| WEEKDAY(@whenever) |
+--------------------+
|                  1 | 
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime)  OR  (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime));
+------+
| Shop |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)

mysql> SET @whenever = TIMESTAMP('2010-11-23 17:05');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime)  OR  (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime));
Empty set (0.01 sec)

mysql> SET @whenever = TIMESTAMP('2010-12-25 16:05');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime)  OR  (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime));
+------+
| Shop |
+------+
|    2 | 
+------+
1 row in set (0.00 sec)

mysql> SET @whenever = TIMESTAMP('2010-11-23 17:05');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT WEEKDAY(@whenever);
+--------------------+
| WEEKDAY(@whenever) |
+--------------------+
|                  1 | 
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT Normal.Shop FROM Normal LEFT JOIN Override ON Normal.Shop = Override.Shop AND Normal.DayOfWeek = Override.DayOfWeek AND @whenever BETWEEN Override.OverrideStartDate AND Override.OverrideEndDate WHERE Normal.DayOfWeek = WEEKDAY(@whenever) AND ((Override.Shop IS NULL AND TIME(@whenever) BETWEEN Normal.OpenTime AND Normal.CloseTime)  OR  (Override.Shop IS NOT NULL AND Override.Closed <> 1 AND TIME(@whenever) BETWEEN Override.AltOpenTime AND Override.AltCloseTime));
Empty set (0.00 sec)

Why there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause?

CREATE TABLE `foo` (
  `ProductID` INT(10) UNSIGNED NOT NULL,
  `AddedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=INNODB;

The error that results:

Error Code : 1293

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Hello!

I also wondered that long time ago. I searched a bit in my history and I think that this post: http://lists.mysql.com/internals/34919 represents the semi-official position of MySQL (before Oracle's intervention ;))

In short:

this limitation stems only from the way in which this feature is currently implemented in the server and there are no other reasons for its existence.

So their explanation is "because it is implemented like this". Doesn't sound very scientific. I guess it all comes from some old code. This is suggested in the thread above: "carry-over from when only the first timestamp field was auto-set/update".

Cheers!

I'm trying to get the count of the number of times a player played each week like this:

player.game_objects.extra(
    select={'week': 'WEEK(`games_game`.`date`)'}
).aggregate(count=Count('week'))

But Django complains that

FieldError: Cannot resolve keyword 'week' into field. Choices are: <lists model fields>

I can do it in raw SQL like this

SELECT WEEK(date) as week, COUNT(WEEK(date)) as count FROM games_game
WHERE player_id = 3
GROUP BY week

Is there a good way to do this without executing raw SQL in Django?

You could use a custom aggregate function to produce your query:

WEEK_FUNC = 'STRFTIME("%%%%W", %s)' # use 'WEEK(%s)' for mysql

class WeekCountAggregate(models.sql.aggregates.Aggregate):
    is_ordinal = True
    sql_function = 'WEEK' # unused
    sql_template = "COUNT(%s)" % (WEEK_FUNC.replace('%%', '%%%%') % '%(field)s')

class WeekCount(models.aggregates.Aggregate):
    name = 'Week'
    def add_to_query(self, query, alias, col, source, is_summary):
        query.aggregates[alias] = WeekCountAggregate(col, source=source, 
            is_summary=is_summary, **self.extra)


>>> game_objects.extra(select={'week': WEEK_FUNC % '"games_game"."date"'}).values('week').annotate(count=WeekCount('pk'))

But as this API is undocumented and already requires bits of raw SQL, you might be better off using a raw query.