datetime's sub-second information lost in SQLite/GAE

[Dan]

I'm using a datetime field with SQLite and GAE (different systems for
development and deployment), and I can only get time resolution of 1
second when reading from the database. I know that I am working with
information down to the 0.01 second resolution, and this is what I'm
inserting into the database. How can I make sure the full resolution
is preserved?

[Dan]

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)

[Dan]

I think I see where this is happening in the /gluon/sql.py code. There
are lines in that file that use this pattern, which truncates
information about fractional seconds:

datetime.isoformat()[:19]

what is the best way to modify this behavior to retain the precision?
(note that this pattern appears in the files /gluon/tools.py and /
gluon/contrib/simplejson/encoder.py)

Dan

[mdipierro]

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

[mdipierro]

correction. not print statements on GAE but logging statetements.

[Dan]

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

[mdipierro]

I am afraid including microsecods will break the datepicker calendar.

[Dan]

OK, to avoid altering the gluon code, I'll change the model to use a
long int to represent epoch-milliseconds

[Dan]

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
)