current_year and current_week

[Dave Fowler]

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.

Thanks!

[Conor]

Every DB has its own method for extracting parts out of a datetime. Here are a few:

• PostgreSQL [1]: extract('ISOYEAR', some_timestamp_expr), extract('WEEK', some_timestamp_expr)
  
• MySQL [2]: func.year(some_timestamp_expr), func.week(some_timestamp_expr), func.yearweek(some_timestamp_expr)
• SQLite [3]: func.strftime('%Y-%W', some_timestamp_expr). I do not believe you get correct ISO year-week behavior here, so beware.
  

-Conor

[1] http://www.postgresql.org/docs/8.4/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
[2] http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
[3] http://www.sqlite.org/lang_datefunc.html

[Michael Bayer]

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.