Get date from datetime with DAL
Angelo Compagnucci
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!
DenesL
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 <angelo.compagnu...@gmail.com>
wrote:
Angelo Compagnucci
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 <dene...@yahoo.ca>:
pbreit
Angelo Compagnucci
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 <pbreit...@gmail.com>:
DenesL
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 <angelo.compagnu...@gmail.com>
wrote:
>
> 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!
>
Angelo Compagnucci
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 <dene...@yahoo.ca>:
DenesL
On Jul 15, 12:12 pm, Angelo Compagnucci <angelo.compagnu...@gmail.com>
wrote:
>
> 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])
>>> rr=db2(db2.test).select(db2.test.data[:10])
Traceback (most recent call last):
File "<console>", line 1, in <module>
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]
<Row {'data': datetime.datetime(2011, 7, 15, 14, 34, 18)}>
>>> rr=db(db.test).select(db.test.data[:10])
>>> print rr[0]
<Row {'_extra': <Row {'SUBSTR(test.data,1,(11 - 1))': u'2011-07-15'}>}
Angelo Compagnucci
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]
> <Row {'data': datetime.datetime(2011, 7, 15, 14, 34, 18)}>
>
>>>> rr=db(db.test).select(db.test.data[:10])
>>>> print rr[0]
> <Row {'_extra': <Row {'SUBSTR(test.data,1,(11 - 1))': u'2011-07-15'}>}
Yes, I noticed that, a substring command returns a string, and not a
date object ... But for me it will suffice.
Thank you!
Alexandre Andrade
dt = request.now.date()