In case anyone is interested, I implemented a solution using the
recently introduced SQLCustomType object. The nice thing about this is
that it keeps all of the code in the model file, which is the right
place to deal with database issues. Here is the part that defines the
type, which I put in the part of the model definition that gets
invoked if it's not running on GAE:
from gluon.sql import SQLCustomType
import datetime
from time import mktime, localtime
datetime_microseconds = SQLCustomType(
type='datetime',
native='NUMERIC(16,6)', # this should be better for range
selection queries (?)
# native='string', # this works, too
encoder=(lambda x: str(mktime(x.timetuple()) + x.microsecond/
1000000.0)),
decoder=(lambda x: datetime.datetime(* (list(localtime(int(x))
[:6])+[int(round(x%1,6)*1000000)]) ))
)
and then later in the model the table gets defined conditionally with
the new type like this:
# This table is a test for datetime resolution
if request.env.web2py_runtime_gae:
db.define_table('timetest',
db.Field('starttime','datetime',default=request.now),
db.Field('endtime','datetime',default=request.now),
db.Field('starttime_micro','datetime',default=request.now),
db.Field('endtime_micro','datetime',default=request.now),
)
else: # running on SQLite
db.define_table('timetest',
db.Field('starttime','datetime',default=request.now),
db.Field('endtime','datetime',default=request.now),
db.Field
('starttime_micro',datetime_microseconds,default=request.now), #
custom field type
db.Field
('endtime_micro',datetime_microseconds,default=request.now), # custom
field type
)
On Jun 30, 7:47 am, Dan <danbr...@gmail.com> wrote:
> OK, to avoid altering the gluon code, I'll change the model to use a
> long int to represent epoch-milliseconds
>
> On Jun 30, 7:03 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > I am afraid including microsecods will break the datepicker calendar.
>
> > On Jun 30, 2:13 am, Dan <danbr...@gmail.com> wrote:
>
> > > Thanks for the suggestion Massimo.
>
> > > It looks like the GAE code is working properly, at least for inserting
> > > the data (see below for details on how I tested this). The truncation
> > > for displaying GAE datetime values was happening in the as_list()
> > > function in the sql.py file [line 2346], and when I removed that step
> > > from my code I could work with information in GAE's datastore with
> > > micro-second precision.
>
> > > However, it took more work to get fractional seconds working for
> > > SQLite. Here is a description of the changes I made to the code in
> > > sql.py.
>
> > > Truncation of fractional seconds happens during both insertion and
> > > reading from the database.
>
> > > To fix insertion, I changed line 409 from this:
> > > obj = obj.strftime('%Y-%m-%d %H:%M:%S')
> > > to this:
> > > if obj.microsecond > 0:
> > > obj = obj.strftime('%Y-%m-%d %H:%M:%S') + '.' + str
> > > (obj.microsecond).zfill(6)
> > > else:
> > > obj = obj.strftime('%Y-%m-%d %H:%M:%S')
>
> > > To fix reading from the database, I added these lines after 2302:
> > > ms = 0 # default
> > > if (len(str(value)) > 19) and (str(value)[19] == '.'):
> > > dec = str(value).rfind('.')
> > > dec_end = len(str(value))
> > > if 18 < dec < dec_end:
> > > ms = int(int(str(value)[dec+1:dec_end]) * pow
> > > (10,(7-dec_end+dec)) )
> > > and I changed this line:
> > > row[tablename][fieldname] = datetime.datetime(y, m, d,
> > > h, mi, s)
> > > to be this:
> > > row[tablename][fieldname] = datetime.datetime(y, m, d,
> > > h, mi, s, ms)
>
> > > If I'm interpreting the sql.py code correctly, this issue probably
> > > affects all of the other databases - I didn't change any of the
> > > special-case code for datetime support for other types of databases,
> > > or for the time data type. And I didn't change the IS_DATETIME()
> > > function in validators.py either.
>
> > > Note that these routines could be simplified when operating with
> > > python2.6:
> > > "New in version 2.6: time and datetime objects support a %f format
> > > code which expands to the number of microseconds in the object, zero-
> > > padded on the left to six places." (http://docs.python.org/library/
> > > datetime.html)
> > > For python2.5, it's possible to use the datetime's microsecond
> > > attribute to get the information.
>
> > > Dan
>
> > > For future reference by GAE developers (including myself):
> > > Using the GAE development console (http://localhost:8080/_ah/admin/
> > > interactive), I could run a quick set of lines to get the actual
> > > values stored in the GAE datastore, which indeed contain the full
> > > precision of what I inserted. These are the lines that I used:
>
> > > import datetime as datetime
> > > from google.appengine.ext import db
> > > class timetest(db.Model):
> > > starttime = db.DateTimeProperty()
> > > endtime = db.DateTimeProperty()
> > > qry = db.Query(timetest)
> > > rows = qry.fetch(limit=5)
> > > for r in rows:
> > > print r.starttime
>
> > > which produced these results, including some fractional seconds:
> > > 1970-01-01 00:00:44.400000
> > > 1970-01-01 00:00:12.200000
>
> > > On Jun 29, 6:55 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > > > correction. not print statements on GAE but logging statetements.
>
> > > > On Jun 29, 8:54 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > > > > In gluon/contrib/gql.py web2py does this:
>
> > > > > if field.type == 'datetime' and value != None and not
> > > > > isinstance(value, datetime.datetime):
> > > > > (y, m, d) = [int(x) for x in
> > > > > str(value)[:10].strip().split('-')]
> > > > > time_items = [int(x) for x in
> > > > > str(value)[11:].strip().split(':')[:3]]
> > > > > if len(time_items) == 3:
> > > > > (h, mi, s) = time_items
> > > > > else:
> > > > > (h, mi, s) = time_items + [0]
> > > > > row[tablename][fieldname] = datetime.datetime
> > > > > (y,m,d,h,mi,s)
> > > > > ....
> > > > > else:
> > > > > row[tablename][fieldname] = value
>
> > > > > perhaps you can put some print statements and see what "value" vs what
> > > > > it should be.
>
> > > > > Massimo
>
> > > > > On Jun 29, 8:36 pm, Dan <danbr...@gmail.com> wrote:
>
> > > > > > Here is some sample code to produce the behavior I'm trying to
> > > > > > describe. Note that in the controller, there are 2 lines commented
> > > > > > out
> > > > > > because they return an error ("TypeError: unsupported operand
> > > > > > type(s)
> > > > > > for -: 'str' and 'str'")
>
> > > > > > db.define_table('timetest',
> > > > > > db.Field('starttime','datetime',default=request.now),
> > > > > > db.Field('endtime','datetime',default=request.now),
> > > > > > )
>
> > > > > > def timetest():
> > > > > > form=FORM(TABLE(
> > > > > > TR("Start time (Epoch Seconds UTC):",INPUT
> > > > > > (_type="text",_name="start_time_es")),
> > > > > > TR("End time (Epoch Seconds UTC):",INPUT
> > > > > > (_type="text",_name="end_time_es")),
> > > > > > TR("",INPUT(_type="submit",_value="Submit"))
> > > > > > ))
>
> > > > > > rowtoinsert = {}
> > > > > > if form.accepts(request.vars):
> > > > > > response.flash="form accepted input"
> > > > > > startdatetime = datetime.datetime.utcfromtimestamp(float
> > > > > > (form.vars.start_time_es))
> > > > > > enddatetime = datetime.datetime.utcfromtimestamp(float
> > > > > > (form.vars.end_time_es))
> > > > > > # make the database insertion
> > > > > > rowtoinsert={
> > > > > > 'starttime':startdatetime,
> > > > > > 'endtime':enddatetime,
> > > > > > }
> > > > > > idinserted = db.timetest.insert(**rowtoinsert)
> > > > > > elif form.errors:
> > > > > > response.flash="form is invalid"
>
> > > > > > existing_rows = db(db.timetest.id>0).select().as_list()
> > > > > > # for r in existing_rows:
> > > > > > # r['time_difference'] = r['endtime'] - r['starttime']
>
> > > > > > return dict(form=form,
> > > > > > rowtoinsert=rowtoinsert,
> > > > > > existing_rows=existing_rows)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"web2py Web Framework" group.
To post to this group, send email to web2py@googlegroups.com
To unsubscribe from this group, send email to
web2py+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/web2py?hl=en
-~----------~----~----~----~------~----~------~--~---
