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.
