Re: [web2py] Re: Get date from datetime with DAL
I now it was posted a long time ago, but there are a simpler solution: dt = request.now.date() rows = db( (db.events.created_on.date()==dt) ).select() Em sábado, 16 de julho de 2011 09:25:57 UTC-3, AngeloC escreveu: > > > that "dirty hack" fails for the MSSQL adapter in 1.97.1: > > Well yes, I called it a dirty hack! > > I'm looking for a function to extract date and time from a datetime db > side and I think I found one for every database supported by the DAL, > I'm in the process of review my research. > > I'm intrugued in the process to add new date() and time() functions to > Filed class. > > rr=db(db.test).select(db.test.data) > print rr[0] > > > > > rr=db(db.test).select(db.test.data[:10]) > print rr[0] > > } > > Yes, I noticed that, a substring command returns a string, and not a > date object ... But for me it will suffice. > > Thank you! > > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [web2py] Re: Get date from datetime with DAL
> that "dirty hack" fails for the MSSQL adapter in 1.97.1: Well yes, I called it a dirty hack! I'm looking for a function to extract date and time from a datetime db side and I think I found one for every database supported by the DAL, I'm in the process of review my research. I'm intrugued in the process to add new date() and time() functions to Filed class. rr=db(db.test).select(db.test.data) print rr[0] > > rr=db(db.test).select(db.test.data[:10]) print rr[0] > } Yes, I noticed that, a substring command returns a string, and not a date object ... But for me it will suffice. Thank you!
[web2py] Re: Get date from datetime with DAL
Hi Angelo,
On Jul 15, 12:12 pm, Angelo Compagnucci
wrote:
> Hi Denes,
>
> thank you for your time!
>
> row.data.date() could be the solution to the problem, but it forces me
> to traverse the table returned by the query and build another table
> with dates instead of datetimes... Not the best solution in speed and
> elegance!
>
> I think that a query like the one I made it's really common. I have a
> timestamp and I want to count the occurences of a determined event by
> date exctracted by the timestamp. I think that excrating the date (or
> the time) from a datetime it's really a speedy operation if
> accomplished by the database, and should be supported by all major
> databases ( I think really all databases!).
>
> By the way I resolved with this quick and dirty hack:
>
> rows = db(db.test).select(db.test.data[:10])
that "dirty hack" fails for the MSSQL adapter in 1.97.1:
>>> rr=db2(db2.test).select(db2.test.data[:10])
Traceback (most recent call last):
File "", line 1, in
File "C:\w2p\web2py_1.97.1_src\gluon\dal.py", line 5394, in select
return self.db._adapter.select(self.query,fields,attributes)
File "C:\w2p\web2py_1.97.1_src\gluon\dal.py", line 1176, in select
rows = response(sql)
File "C:\w2p\web2py_1.97.1_src\gluon\dal.py", line 1166, in response
self.execute(sql)
File "C:\w2p\web2py_1.97.1_src\gluon\dal.py", line 1251, in execute
return self.log_execute(*a, **b)
File "C:\w2p\web2py_1.97.1_src\gluon\dal.py", line 1246, in
log_execute
ret = self.cursor.execute(*a,**b)
ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server
Driver][SQL Ser
ver]Argument data type datetime is invalid for argument 1 of substring
function.
(8116) (SQLExecDirectW)')
the SQL command issued is:
>>> db2._lastsql
'SELECT SUBSTRING(test.data,1,(11 - 1)) FROM test WHERE (test.id >
0);'
in the case of SQLite the command is:
>>> db._lastsql
'SELECT SUBSTR(test.data,1,(11 - 1)) FROM test WHERE (test.id > 0);'
so you see it all depends on the DB backend, and we would have to
check if all the other DBs do have such functions.
The MSSQL error could be a bug.
Also note how the content of each row differs from a 'standard'
select:
>>> rr=db(db.test).select(db.test.data)
>>> print rr[0]
>>> rr=db(db.test).select(db.test.data[:10])
>>> print rr[0]
}
>
>
> slicing the first 10 character is enough for me!
>
> If anyoune intrested, I'll explore te possibility to add a .date() and
> a .time() to FIeld object, or find a way to concatenate .year()
> .month() .day()!
>
> Thank you!
Re: [web2py] Re: Get date from datetime with DAL
Hi Denes,
thank you for your time!
row.data.date() could be the solution to the problem, but it forces me
to traverse the table returned by the query and build another table
with dates instead of datetimes... Not the best solution in speed and
elegance!
I think that a query like the one I made it's really common. I have a
timestamp and I want to count the occurences of a determined event by
date exctracted by the timestamp. I think that excrating the date (or
the time) from a datetime it's really a speedy operation if
accomplished by the database, and should be supported by all major
databases ( I think really all databases!).
By the way I resolved with this quick and dirty hack:
rows = db(db.test).select(db.test.data[:10])
slicing the first 10 character is enough for me!
If anyoune intrested, I'll explore te possibility to add a .date() and
a .time() to FIeld object, or find a way to concatenate .year()
.month() .day()!
Thank you!
2011/7/13 DenesL :
> Hi Angelo,
>
> sorry, I missed the datetime in the title (duh!).
>
> The date function belongs to the datetime object so it would be
> available in the rows only, the field does not have it:
>
> for row in rows:
> print row.data.date()
>
> Note that the year month day hour minute second functions might not be
> available in all DBs since the SQL implementation varies.
>
> By the way, the MSSQL adapter has it wrong in 1.94.5, I have to check
> on the newer versions.
> Hmmm... that means I have never came across the need for those
> functions.
>
> If you are only working with SQLite then adding date() or time() would
> not be hard and they seem like sensible options. Maybe even add them
> to other adapters as well.
>
> Is the row.data.date() option enough for you?.
>
> Denes.
>
>
> On Jul 12, 5:39 pm, Angelo Compagnucci
> wrote:
>> Sorry for being pedantic!
>>
>> I made an empty application with only a table in the model defined as:
>>
>> db.define_table('test',Field('data','datetime'))
>>
>> and inserted some datetimes.
>>
>> then I made a method in the controller:
>>
>> def getdata():
>> rows = db(db.test).select()
>> return dict(rows=rows)
>>
>> and it works.
>>
>> It works also:
>>
>> def getdata():
>> rows = db(db.test).select(db.test.data.year())
>> return dict(rows=rows)
>>
>> but if I try:
>>
>> def getdata():
>> rows = db(db.test).select(db.test.data.date())
>> return dict(rows=rows)
>>
>> web2py throws an exception with the message:
>>
>> File "/home/angelo/DEV/web2py/applications/welcome/controllers/default.py",
>> line 13, in getdata
>> rows = db(db.test.ALL).select(db.test.data.data())
>> AttributeError: 'Field' object has no attribute 'date'
>>
>> So what's wrong?
>>
>> Thank you!
>>
>> 2011/7/12 pbreit :
>>
>>
>>
>>
>>
>>
>>
>> > I believe datetime is a Python datetime.datetime object and gets validated
>> > by IS_DATETIME().
>> >http://docs.python.org/library/datetime.html#datetime.datetime
>> >http://web2py.com/book/default/chapter/07?search=IS_DATETIME
[web2py] Re: Get date from datetime with DAL
Hi Angelo,
sorry, I missed the datetime in the title (duh!).
The date function belongs to the datetime object so it would be
available in the rows only, the field does not have it:
for row in rows:
print row.data.date()
Note that the year month day hour minute second functions might not be
available in all DBs since the SQL implementation varies.
By the way, the MSSQL adapter has it wrong in 1.94.5, I have to check
on the newer versions.
Hmmm... that means I have never came across the need for those
functions.
If you are only working with SQLite then adding date() or time() would
not be hard and they seem like sensible options. Maybe even add them
to other adapters as well.
Is the row.data.date() option enough for you?.
Denes.
On Jul 12, 5:39 pm, Angelo Compagnucci
wrote:
> Sorry for being pedantic!
>
> I made an empty application with only a table in the model defined as:
>
> db.define_table('test',Field('data','datetime'))
>
> and inserted some datetimes.
>
> then I made a method in the controller:
>
> def getdata():
> rows = db(db.test).select()
> return dict(rows=rows)
>
> and it works.
>
> It works also:
>
> def getdata():
> rows = db(db.test).select(db.test.data.year())
> return dict(rows=rows)
>
> but if I try:
>
> def getdata():
> rows = db(db.test).select(db.test.data.date())
> return dict(rows=rows)
>
> web2py throws an exception with the message:
>
> File "/home/angelo/DEV/web2py/applications/welcome/controllers/default.py",
> line 13, in getdata
> rows = db(db.test.ALL).select(db.test.data.data())
> AttributeError: 'Field' object has no attribute 'date'
>
> So what's wrong?
>
> Thank you!
>
> 2011/7/12 pbreit :
>
>
>
>
>
>
>
> > I believe datetime is a Python datetime.datetime object and gets validated
> > by IS_DATETIME().
> >http://docs.python.org/library/datetime.html#datetime.datetime
> >http://web2py.com/book/default/chapter/07?search=IS_DATETIME
Re: [web2py] Re: Get date from datetime with DAL
Sorry for being pedantic!
I made an empty application with only a table in the model defined as:
db.define_table('test',Field('data','datetime'))
and inserted some datetimes.
then I made a method in the controller:
def getdata():
rows = db(db.test).select()
return dict(rows=rows)
and it works.
It works also:
def getdata():
rows = db(db.test).select(db.test.data.year())
return dict(rows=rows)
but if I try:
def getdata():
rows = db(db.test).select(db.test.data.date())
return dict(rows=rows)
web2py throws an exception with the message:
File "/home/angelo/DEV/web2py/applications/welcome/controllers/default.py",
line 13, in getdata
rows = db(db.test.ALL).select(db.test.data.data())
AttributeError: 'Field' object has no attribute 'date'
So what's wrong?
Thank you!
2011/7/12 pbreit :
> I believe datetime is a Python datetime.datetime object and gets validated
> by IS_DATETIME().
> http://docs.python.org/library/datetime.html#datetime.datetime
> http://web2py.com/book/default/chapter/07?search=IS_DATETIME
>
Re: [web2py] Re: Get date from datetime with DAL
I believe datetime is a Python datetime.datetime object and gets validated by IS_DATETIME(). http://docs.python.org/library/datetime.html#datetime.datetime http://web2py.com/book/default/chapter/07?search=IS_DATETIME
Re: [web2py] Re: Get date from datetime with DAL
Thank you Denes, What I'm trying to do is to select the date part from a datetime as specified in the email subject, so the AcctStartTime field is a datetime. I'm navigating the epydoc documentation for dal.Field http://www.web2py.com/examples/static/epydoc/web2py.gluon.dal.Field-class.html and I cannot find reference for the date() method (only year(),month(), day(), hour(), minutes(), seconds()) . Perhaps the documentation is out of date. I made several tries and in one of this I tryed also the date() method in a query something like this: rows = dbradius(dbradius.radacct.CalledStationId==hsname).select( count, dbradius.radacct.AcctStartTime.date(), groupby=entry_date) and it throws an exception. I can really make a mistake, so I'll try your suggestion as soon as possible! Thank you. 2011/7/12 DenesL : > Hi Angelo, > > it depends on the type of field, > > date fields can be accessed directly, > e.g. dbradius.radacct.AcctStartTime > > datetime fields need .date(): > dbradius.radacct.AcctStartTime.date() > > Denes. > > On Jul 12, 1:58 pm, Angelo Compagnucci > wrote: >> Ello everybody! >> >> I'm stuck whith a really simple thing, but the simpler are the worst! >> >> I wrote this query: >> >> rows = dbradius(dbradius.radacct.CalledStationId==hsname).select( >> count, >> dbradius.radacct.AcctStartTime.year(), >> dbradius.radacct.AcctStartTime.month(), >> dbradius.radacct.AcctStartTime.day(), >> groupby=entry_date) >> >> Query works well, but I want a signle field for the date part, not the >> three fields (year, month, day) I obtain. >> >> There is a simple way to accomplish this? >> >> Thank you!
[web2py] Re: Get date from datetime with DAL
Hi Angelo, it depends on the type of field, date fields can be accessed directly, e.g. dbradius.radacct.AcctStartTime datetime fields need .date(): dbradius.radacct.AcctStartTime.date() Denes. On Jul 12, 1:58 pm, Angelo Compagnucci wrote: > Ello everybody! > > I'm stuck whith a really simple thing, but the simpler are the worst! > > I wrote this query: > > rows = dbradius(dbradius.radacct.CalledStationId==hsname).select( > count, > dbradius.radacct.AcctStartTime.year(), > dbradius.radacct.AcctStartTime.month(), > dbradius.radacct.AcctStartTime.day(), > groupby=entry_date) > > Query works well, but I want a signle field for the date part, not the > three fields (year, month, day) I obtain. > > There is a simple way to accomplish this? > > Thank you!
