On Sep 21, 2010, at 3:00 PM, Dave Fowler wrote: > Hi, > > I'm new to SQLAlchemy but I've read the entire oreilly book and done a > lot of googling and i've found that there is support for the generic > functions > > current_date, current_time and current_timestamp > > but i can find nothing for finding the year, or week of a date > column. > > I need them in order to group results by week or year depending on the > user input. > > > Does current_year and current_week exist somewhere? And if not is > there a way to create your own custom functions based on Column > types? I've yet to run into any documentation on that processes > either.
The collection of functions that are truly "generic" is quite small right now. Date arithmetic and calendar functions in particular vary dramatically in their implementation across backends, and a comprehensive abstraction layer for them would be a large undertaking with a lot of controversial points. So for this use case the most straight ahead approach to building up a library of date/calendar functions that you need is using the compiler extension, which is at http://www.sqlalchemy.org/docs/core/compiler.html . For example, my current app has to do some simple date math across Postgresql and MS-SQL. So a function that calculates the length in days between two dates looks like: from sqlalchemy.sql import expression from sqlalchemy.ext.compiler import compiles from sqlalchemy.types import Integer class date_delta(expression.FunctionElement): """Calculate the timedelta in days between two dates.""" type = Integer() name = 'timedelta' @compiles(date_delta, 'postgresql') def _pg_date_delta(element, compiler, **kw): return "(%s::date - %s::date)" % ( compiler.process(element.clauses.clauses[1]), compiler.process(element.clauses.clauses[0]), ) @compiles(date_delta, 'mssql') def _ms_date_delta(element, compiler, **kw): return "DATEDIFF(day, %s, %s)" % ( compiler.process(element.clauses.clauses[0], **kw), compiler.process(element.clauses.clauses[1], **kw), ) Where above, some inspection of the function object's ".clauses" member, called a ClauseList, has the actual two arguments inside of a list called "clauses". Some minimal source perusal of expression.py may be needed for more involved constructs but functions are pretty much in the above form. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
