extract the WEEK off a timestamp across multiple database backends

[Jonathan Vanasco]

I need to extract the week of the year across multiple databases (this needs to work on at-least sqlite and postgres, though mysql would be good too.)

the problem?  this operation isn't standard:

sqlite:

    select strftime('%W', cast(timestamp_event as text)) from table_a;

postgres:

    select extract(week from timestamp_event) from table_a; 

mysql: i think extract will work on a timestamp, but it might need to be cast into a date first.  there is also a WEEK function.

how have others handled a need like this?

[Jonathan Vanasco]

I forgot to add... in the interim I checked the session.connection().dialect and used a different filter for sqlite

[Mike Bayer]

I stick with the @compiles approach for that kind of thing, e.g.
http://docs.sqlalchemy.org/en/rel_1_0/core/compiler.html#utc-timestamp-function


>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Jonathan Vanasco]

Thanks!  This works like a charm. 

For the next person...

`year_week(column)` turns a timestamp into "{Year}.{week of year}".  I should probably 0pad the week , but not now.

----------

from sqlalchemy.sql import expression

from sqlalchemy.ext.compiler import compiles

import sqlalchemy.types

class year_week(expression.FunctionElement):

    type = sqlalchemy.types.String()

    name = 'year_week'

@compiles(year_week)

def year_week__default(element, compiler, **kw):

    # return compiler.visit_function(element)

    """

    ## select extract(week from timestamp_event) from table_a; 

    week_num = sqlalchemy.sql.expression.extract('WEEK', LetsencryptServerCertificate.timestamp_signed)

    """    

    args = list(element.clauses)

    return "concat(extract(year from %s), '.', extract(week from %s)) " % (

        compiler.process(args[0]),

        compiler.process(args[0]),

    )

    

    

@compiles(year_week, 'postgresql')

def year_week__postgresql(element, compiler, **kw):

    """

    # select to_char(timestamp_event, 'YYYY.WW')  from table_a;

    week_num = sqlalchemy.func.to_char(LetsencryptServerCertificate.timestamp_signed, 'YYYY.WW')

    """

    args = list(element.clauses)

    return "to_char(%s, 'YYYY.WW')" % (

        compiler.process(args[0]),

    )

@compiles(year_week, 'sqlite')

def year_week__sqlite(element, compiler, **kw):

    """

    # strftime('%Y.%W', cast(LetsencryptServerCertificate.timestamp_signed) as text)

    week_num = sqlalchemy.func.strftime('%Y.%W',

                                        sqlalchemy.cast(TABLE.COLUMN,

                                                        sqlalchemy.Unicode

                                                        )

                                        ) 

    """

    args = list(element.clauses)

    return "strftime('%%Y.%%W', %s)" % (

        compiler.process(args[0]),

    )