Noufal wrote:
>
> Hello everyone,
> I'm having some trouble with a query involving a timedelta
> object with a mySQL backend (MyISAM). I have a table called Run
> that has two fields like so.
>
> sa.Column('starttime', sa.TIMESTAMP),
> sa.Column('endtime', sa.TIMESTAMP)
>
> I'm trying to find all rows that have endtime - starttime equal
> to some timedelta obtained from a user. The query is constructed
> like this
>
>>>> l = datetime.timedelta(seconds=907)
>>>> cc = ((Run.c.endtime - Run.c.starttime) == l)
>>>> t=q.select(cc)
> 2007-10-01 14:10:12,572 INFO sqlalchemy.engine.base.Engine.0x..4c
> SELECT runs.status AS runs_status, runs.workdir AS runs_workdir,
> runs.machinetype AS runs_machinetype, runs.endtime AS
> runs_endtime, runs.hostname AS runs_hostname, runs.cmdline AS
> runs_cmdline, runs.incremental AS runs_incremental, runs.`user`
> AS runs_user, runs.starttime AS runs_starttime, runs.rid AS
> runs_rid
> FROM runs
> WHERE (runs.endtime - runs.starttime) = %s ORDER BY runs.rid
> 2007-10-01 14:10:12,572 INFO sqlalchemy.engine.base.Engine.0x..4c
> [datetime.timedelta(0, 907)]
>
> The %s looks funny there.
>
> This returns the wrong row and when I do this kind of query via my
> script (which is web based), I get messages in the server error
> log that look like this
> /usr/lib/python2.5/site-packages/sqlalchemy/databases/mysql.py:31
> 3: Warning: Truncated incorrect DOUBLE value: '00:08:40'
> cursor.execute(statement, parameters)
>
> I expect the users to input values like HH:MM:SS which I split
> and use to create the timedelta object.
>
> I'd appreciate any help.
MySQL doesn't support first-class intervals or arithmetic operators
for date types that I'm aware of. The date math functions can be
used for this sort of calculation, such as:
cc = (func.timestampdiff(text('second'), Run.c.starttime,
Run.c.endtime) == 907)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
