MySQL DATE_ADD function
Bryan
DATE_ADD(datecol, INTERVAL(1 - DAYOFWEEK(datecol)) DAY)
This is as far as I can get, which is basically nowhere. The second
argument to date_add requires literal strings "INTERVAL" and "DAY",
but I also need to insert a function in the middle there. Any help is
appreciated.
func.date_add(Class.dateAttr, INTERVAL(1 - DAYOFWEEK(Class.dateAttr))
DAY)
Chris Withers
Are you looking for something database agnostic or something that just
works for MySQL?
If the latter, look at text:
http://www.sqlalchemy.org/docs/core/tutorial.html#using-text
If the former, then you'll want a database agnostic implementation. So,
what's the above sql actually trying to achieve?
Chris
Bryan Vicknair
> Are you looking for something database agnostic or something that just works
> for MySQL?
>
> If the latter, look at text:
> http://www.sqlalchemy.org/docs/core/tutorial.html#using-text
>
> If the former, then you'll want a database agnostic implementation. So,
> what's the above sql actually trying to achieve?
>
> Chris
>
I'm fine with a MySQL-only solution. The text construct is always the fallback,
but I'm wondering if there is a way that I can use the attributes of my class
for the column name, instead of just a string. My column names are going to
change soon, but my object model will stay the same, so I am trying not to
explicitly use the column names in my code.
Can I do something like this?
'DATE_ADD(' + Class.dateAttr + ', INTERVAL(1 - ' \
+ 'DAYOFWEEK(' + Class.dateAttr + ')) DAY)'
If I can't use my class's attributes, is there a way I can at least use the
table object's columns like this:
'DATE_ADD(' + table.c.date_col.name + ', INTERVAL(1 - ' \
+ 'DAYOFWEEK(' + table.c.date_col.name + ')) DAY)'
I prefer one of these to a string because I will get an error during testing
when the statement is encountered. With a string, I will only get an error if
the statement actually runs in the DB.
Sven A. Schmidt
the only tricky bit in your SQL is the dangling 'DAY', because there's
no operator to tie it to the rest. Otherwise you should be able to
write (schema.AppDcRpe2 is just a Table object I'm using as an
example):
>>> q = session.query(schema.AppDcRpe2).filter(func.date_add(schema.AppDcRpe2.asof, func.interval(1 - func.dayofweek(schema.AppDcRpe2.asof))) < func.sysdate)
>>> print q
SELECT kdb_app_dc_rpe2.id AS kdb_app_dc_rpe2_id, kdb_app_dc_rpe2.asof
AS kdb_app_dc_rpe2_asof, kdb_app_dc_rpe2.instance_name AS
kdb_app_dc_rpe2_instance_name, kdb_app_dc_rpe2.dc AS
kdb_app_dc_rpe2_dc, kdb_app_dc_rpe2.rpe2_total AS
kdb_app_dc_rpe2_rpe2_total, kdb_app_dc_rpe2.rpe2_used AS
kdb_app_dc_rpe2_rpe2_used, kdb_app_dc_rpe2.rpe2_unused AS
kdb_app_dc_rpe2_rpe2_unused
FROM kdb_app_dc_rpe2
WHERE date_add(kdb_app_dc_rpe2.asof, interval(:dayofweek_1 -
dayofweek(kdb_app_dc_rpe2.asof))) < :date_add_1
which is *almost* what you need. Can MySQL 'INTERVAL' perhaps be
written in 'function form', i.e. something like interval(x, 'DAY')? In
that case you should be able to translate it fully.
Or maybe there's a way to 'abuse' the alias method, like so:
>>> q = session.query(schema.AppDcRpe2).filter(func.date_add(schema.AppDcRpe2.asof, func.interval(1 - func.dayofweek(schema.AppDcRpe2.asof)).alias('DAY')) < func.sysdate)
>>> print q SELECT kdb_app_dc_rpe2.id AS kdb_app_dc_rpe2_id, kdb_app_dc_rpe2.asof AS kdb_app_dc_rpe2_asof, kdb_app_dc_rpe2.instance_name AS kdb_app_dc_rpe2_instance_name, kdb_app_dc_rpe2.dc AS kdb_app_dc_rpe2_dc, kdb_app_dc_rpe2.rpe2_total AS kdb_app_dc_rpe2_rpe2_total, kdb_app_dc_rpe2.rpe2_used AS kdb_app_dc_rpe2_rpe2_used, kdb_app_dc_rpe2.rpe2_unused AS kdb_app_dc_rpe2_rpe2_unused
FROM kdb_app_dc_rpe2, interval(:dayofweek_1 -
dayofweek(kdb_app_dc_rpe2.asof)) "DAY"
WHERE date_add(kdb_app_dc_rpe2.asof, interval(:dayofweek_1 -
dayofweek(kdb_app_dc_rpe2.asof))) < :date_add_1
Except for the double quotes that looks to be pretty close to what you
want. But then again rather than massaging that into place you may as
well build a text SQL from your bits, I guess. The above would also be
MySQL specific, I believe. (BTW I have not tried to run any of this,
this is just the output of the parsed statements.)
-sas
Bryan
> > I'm fine with a MySQL-only solution. The text construct is always the fallback,
> > but I'm wondering if there is a way that I can use the attributes of my class
> > for the column name, instead of just a string. My column names are going to
> > change soon, but my object model will stay the same, so I am trying not to
> > explicitly use the column names in my code.
function, one
that actually accepts arguments seperated by commas.
func.timestampadd(text('day'),
func.if_(func.dayofweek(EmpTime.day) == 1,
0,
8 - func.dayofweek(EmpTime.day)),
EmpTime.day)