How to add an hour to datetime field

[mfarees...@gmail.com]

I have a table that I have created (in models) using 

    db.define_table('myTable',
                    Field('start', type='datetime', requires=NE)
    )

I have some records in this table, in this format: '2015-12-31 00:00:00' (this is on id = 1)

I want to run a query that will add one hour to this time, for each row.

db(db.myTable.id == 1).update(start = db.myTable.start + timedelta(hours=1))

But when I execute the above query, it changes the start value to '2015-12-31 00:00:01'. And if I run the same query again it changes it to '0000-00-00 00:00:00'. What am I doing wrong here and how can I fix this?

[Annet]

It should be:

datetime.timedelta(hours=1)

So:

db(db.myTable.id == 1).update(start = db.myTable.start + datetime.timedelta(hours=1))

Hope this helps.

Kind regards,

Annet

[Niphlod]

you're trying to tell the database to execute a piece of python code.

What you should do is either craft a backend-specific query, OR fetch the "start" value, compute + timedelta(hours=1), update "start".

[Annet]

I had this from the web2py book:

db(db.person.id > 3).update(name='Ken')

and adapted it to update records in an event list:

db(db.cal_event_list.id > 0).update(createdOn = db.cal_event_list.createdOn + datetime.timedelta(hours=1))

This code does work it adds 1 hour to every createdOn datetime. From your reply I learn that it doesn't work:

you're trying to tell the database to execute a piece of python code.

 
Could you please tbe a bit more specific as to why you think my code is wrong.


Kind regards,

Annet

[Niphlod]

ok, let's elaborate.

db(db.person.id > 3).update(name='Ken')

and

db(db.person.id > 3).update(name=db.person.name+'Ken')

are actually REALLY different (from the database perspective).

The first passes a FIXED value to the database, which discards any value contained in the "name" column and replaces it with a FIXED value that you pass to it ('Ken'). Given, e.g., 4 records that have "id" > 3, ALL 4 records will end up having "Ken" as the value hold in the "name" column.

It's an indempotent function: no matter how many times you execute it, the result is always the same.

The second instead asks the database to "append" the "Ken" fixed value to whatever the column is holding already. It's NOT idempotent. Each time you execute it, you end up having different results.

Now, back to the pyDAL. 

When you ask pydal to do an update of the "latter" case, you pass "down the pipe" that assignment

to the database. You're trying to leverage the backend's (and not python's) capabilities. This is labelled as an "expression" (see here for the book docs about it)

The first statement translates roughly to

UPDATE person

SET name = 'Ken'

WHERE id > 3

The second one, instead, translates to

UPDATE person

SET name = name + 'Ken'

WHERE id > 3

The funny thing here can not be really obvious, but the second query (the "expression") actually calls a function in the backend (in bold with the operator in red), that is, in this case, a simple "append" (NB: different backend have different syntax for this simple "append" function)

Now, backend are generally MORE limited than python when dealing with functions, and pyDAL can "translate" only a fraction of functions.

Strings "append" and "prepend" are some of them, and also "addition" and "subtractions" of numeric values, as in

db(db.vote.id == 3).update(value=db.vote.value+1)

Date operations, on the other end, are REALLY easy in python and REALLY complicated in backend (every dialect has its own intricacies)

pyDAL can't intercept and translate correctly all of them, so you need to test CAREFULLY complicate function in python when passed to an update to pyDAL.

For a 

db(db.appointments.id == 3).update(start_date=db.appointments.start_date + 1)

that under the hood translate to a

UPDATE appointments

SET start_date = start_date + 1

WHERE id = 3

some backend may implicitly add a second, or a day, or a month. Or throw an exception.

What you're doing adds yet another layer of intricacies: the backend doesn't know the first thing about "timedelta(hours=1)" bit. pyDAL knows it, and tries to stringify it.

It's easy to know what query pyDAL is actually passing down the pipe: you can see any update() issued to the backend in its dialect using _update() (see the underscore).

tl;dr: if you need to update a set of records to a FIXED value (first case scenario), no problems on passing a FIXED value down the pipe.

if you need to update a set of records to a CALCULATED value, which is the result of a function (i.e. when you have db.table.columnname on the right end of the assignment) BEWARE that only SIMPLE operations are "transparently" passed to the backend. 

Use _update() to inspect the actual query and TEST your code at every step.

Not EVERY python function (which is, at all effects, passed down the pipe and DEMANDED to the backend) has a proper way to be passed to the backend dialect to have the same result you'd have in python.

To overcome it and be sure the function is calculated in python, you need to fetch the value first, and then update() passing a FIXED value.

This "form" is slower (need to fetch the values first, at the very least) but ensures python doing the bits, and not the database

rows = db(db.person.id > 3).select()

for row in rows:

    row.update_record(name=row.name + 'Ken')

or, for the timedelta example

rows = db(db.mytable.id == 1).select()

for row in rows:

     row.update_record(start=row.start + timedelta(hours=1))

Please mind that the difference ISN'T the fact that it uses update_record() instead of update(). 

It could be very well rewritten as

db(db.mytable.id == row.id).update(start=row.start + timedelta(hours=1))

The key difference is that on the right end of the assignment there isn't db.tablename.columnname (which will trigger the "expressions" behaviour, relinquishing control to the backend). With no expressions, the calculation is computed in python (up to a fixed value) BEFORE passing it the statement to the backend.

[Anthony]

On Sunday, November 22, 2015 at 4:29:00 AM UTC-5, Annet wrote:

I had this from the web2py book:

db(db.person.id > 3).update(name='Ken')

and adapted it to update records in an event list:

db(db.cal_event_list.id > 0).update(createdOn = db.cal_event_list.createdOn + datetime.timedelta(hours=1))

Which database do you use? With SQLite, that would generate the following SQL:

SET createdOn=(CAST(call_event_list.createdOn AS TEXT) || '1:00:00')

which I don't think is what you want.

Anthony

[mfarees...@gmail.com]

I've already tried this, but it didn't work.

I get this error. 

<type 'exceptions.AttributeError'> type object 'datetime.datetime' has no attribute 'timedelta'

[Annet]

Hi Simone,

Thank you for taking the time to add more detail to your first reply.
I now understand why my code works but should not be used.

Kind regards,

Annet

[Anthony]

It would be:

import datetime
datetime.timedelta

Not:

datetime.datetime.timedelta

Anthony