TimeField for duration
Olivier Guilyardi
Django TimeField is mapped to Python datetime.time. That doesn't fit my needs
since I must deal with durations, which can exceed 24h, and datetime.time can't
do that.
On the MySQL side, TimeField is mapped to the MySQL TIME column type, which is
correct IMO:
http://dev.mysql.com/doc/refman/5.0/en/time.html
"TIME values may range from '-838:59:59' to '838:59:59'. The hours part may be
so large because the TIME type can be used not only to represent a time of day
(which must be less than 24 hours), but also elapsed time or a time interval
between two events (which may be much greater than 24 hours, or even negative)."
From basic testing, SQLite also seems to get this right. I don't know about
other database backends though.
But what about mapping to datetime.timedelta instead of datetime.time? It seems
that it can do the job, altough it lacks strftime(), etc... but that can be
worked around.
I understand that the datetime.time mapping is very intuitive and must stay here
for BC. But, if I am to prepare a patch for that, how should I proceed?
Create a new DurationField?
Let TimeField accept an extra 'duration' or 'delta' option so that it uses
timedelta instead of time?
(I tend to prefer this last one)
--
Olivier
Jerome Leclanche
I've worked on a DurationField implementation (ticket:
http://code.djangoproject.com/ticket/2443). However I was extremely
displeased with the whole DecimalField backend.
With the recent addition of BigIntegerField, I am now saving durations
with a bigint and mapping them to timedelta. I wanted to post an
updated patch with that overhaul but never got around to do it; since
you seem to need it I'll do that right now.
It's the same DurationField I use on
http://db.mmo-champion.com/items/?duration=3600000000 - it can store
up to a few hundred thousand years in microseconds, ought to be enough
for everybody, right? :)
J. Leclanche / Adys
> --
> You received this message because you are subscribed to the Google Groups "Django developers" group.
> To post to this group, send email to django-d...@googlegroups.com.
> To unsubscribe from this group, send email to django-develop...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.
>
>
>
>
Olivier Guilyardi
>
> I've worked on a DurationField implementation (ticket:
> http://code.djangoproject.com/ticket/2443). However I was extremely
> displeased with the whole DecimalField backend.
> With the recent addition of BigIntegerField, I am now saving durations
> with a bigint and mapping them to timedelta. I wanted to post an
> updated patch with that overhaul but never got around to do it; since
> you seem to need it I'll do that right now.
> It's the same DurationField I use on
> http://db.mmo-champion.com/items/?duration=3600000000 - it can store
> up to a few hundred thousand years in microseconds, ought to be enough
> for everybody, right? :)
That sounds interesting, but I tend to prefer using the SQL TIME type. It
doesn't feature such precision, but it's more consistent and readable when
browsing the database directly: it's a time column and it contains time information.
Things get quite obscure with big integers IMO.
If you want to go that way, then why wouldn't the TimeField itself be stored as
a numeric type, for microseconds precision too?
But it's not, and I think there's a good reason for it: AFAICS Django fields try
to follow quite closely SQL types, and I think that it's a good thing. It make
things transparent.
And if someone needs high precision then he/she can use a numeric field explicitly.
The problem I see with the current TimeField is that it's not *consistent* with
the SQL TIME type, at least on MySQL and SQLite, and that could be called a bug,
actually.
--
Olivier
Jerome Leclanche
(slower, made a lot less sense as well).
Keep in mind, TIME fields are for storing time, not for storing
durations. For durations, postgres has an INTERVAL field (cf ticket),
however not every database has that field type. Personally, I rather
have a builtin DurationField which plays with python timedeltas
instead of using a bigint and do myself all the conversion, the admin
widgets and so on.
Attaching the patch, if there are any bugs/side effects please give me
a poke, my django core has a lot of modifications and I had to strip
down the patchset.
J. Leclanche / Adys
Olivier Guilyardi
> Keep in mind, TIME fields are for storing time, not for storing
> durations. For durations, postgres has an INTERVAL field (cf ticket),
> however not every database has that field type.
Not in MySQL, from the official docs:
"TIME values may range from '-838:59:59' to '838:59:59'. The hours part may be
so large because the TIME type can be used not only to represent a time of day
(which must be less than 24 hours), but also elapsed time or a time interval
between two events (which may be much greater than 24 hours, or even negative)."
SQLite3 also doesn't complain when setting a time column to eg '900:00:00'.
So for consistence with the database, I'd say that either TIME or INTERVAL (for
postgres) can be used. No need for integers so far.
--
Olivier
Łukasz Rekucki
2010/1/21 Jerome Leclanche <ady...@gmail.com>:
> A decimal backend was a lot worse than a bigint backend on that matter
> (slower, made a lot less sense as well).
>
> Keep in mind, TIME fields are for storing time, not for storing
> durations. For durations, postgres has an INTERVAL field (cf ticket),
> however not every database has that field type.
I did a little searching and I found that:
* Oracle implements SQL92's INTERVAL DAY TO SECOND
* Mysql has TIME
* PostreSQL has of course the INTERVAL
* SQLite3 doesn't support any kind of time fields (datetime, time,
interval), but accepts all as legal types.
So actually, all django backends support some kind of INTERVAL field.
To be clear, I'm not criticizing your approach with BigInt, nor I have
time & enough django expertise to provide a patch for this using
native datatypes.
Best regards,
Lukasz Rekucki
Jerome Leclanche
free to base yourself on my patch, but good luck implementing 4-5
implementations and keeping them abstract...
J. Leclanche / Adys
Jerome Leclanche
Main issue is precision. I personally very, very often work with
milliseconds, and I can imagine many use cases where I'd need to work
with microseconds. How many of these backends do not at least support
millisecond precision?
J. Leclanche / Adys
2010/1/21 Łukasz Rekucki <lrek...@gmail.com>:
Olivier Guilyardi
>
> Main issue is precision. I personally very, very often work with
> milliseconds, and I can imagine many use cases where I'd need to work
> with microseconds. How many of these backends do not at least support
> millisecond precision?
If you want to go that way then you should also modify TimeField to support such
precision.
IMO precision can be dealt with by using a numeric field explicitly.
--
Olivier
Jerome Leclanche
J. Leclanche / Adys
Łukasz Rekucki
> Hi Lukasz, thanks for the wrap up
>
> Main issue is precision. I personally very, very often work with
> milliseconds, and I can imagine many use cases where I'd need to work
> with microseconds. How many of these backends do not at least support
> millisecond precision?
From my understanding of the docs PostgreSQL can do microseconds
(6digits), Oracle - nanoseconds (9digits), SQLite3 seems to accept
miliseconds in string representation "HH:MM:SS.SSS".
And the worst case is MySQL:
"""A trailing .uuuuuu microseconds part of TIME values is allowed
under the same conditions as for other temporal values, as described
in Section 10.3.1, “The DATETIME, DATE, and TIMESTAMP Types”. This
includes the property that any microseconds part is discarded from
values stored into TIME columns."""
After seeing all this mess, i'm +1 on using BigIntField. Good work.
--
Łukasz Rekucki
Olivier Guilyardi
> Which is exactly what I'm doing using a BigIntegerField...
AFAICS you are doing this for your new DurationField. If you do so, then you
should also modify the current TimeField for better precision, so that it is
coherent with this DurationField. It doesn't make sense for the later to be more
precise than the former. They should play well together, obviously.
That would forbid using the TIME column to store day time using TimeField as it
is currently done. Storing such day time with reliable extra precision would
also require integers, for portability over database backends.
I think that this is not desirable and might also break backward compatibility.
If the user wants more precision then he/she can add a BigIntegerField or
DecimalField explicitly to his/her models.
Nevertheless, if using PostgreSQL with TimeField mapped to TIME and
DurationField mapped to INTERVAL, then extra precision would be supported at the
backend level, I suppose.
That said, I have to go and sleep now ;)
--
Olivier
Ian Kelly
In other words, all the same limitations that we already handle for
TimeFields. This looks to me like a green light to just use the
native datatypes.
I see another big downside to the BigIntField approach: you can't add
TimeFields and IntervalFields at the database level (e.g. with F
expressions). I'm not sure how PostgreSQL or MySQL would handle this
if you tried it, but I know what Oracle would do: it would accept the
SQL as valid, it would interpret the IntervalField value as a decimal
number of /days/, and it would return a completely incorrect result.
Cheers,
Ian
Alex Gaynor
> You received this message because you are subscribed to the Google Groups "Django developers" group.
> To post to this group, send email to django-d...@googlegroups.com.
> To unsubscribe from this group, send email to django-develop...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.
>
>
>
>
Ian,
FWIW there's a ticket with a patch (of unkown quality :P) for support
for using timedeltas with F() expressions.
Alex
--
"I disapprove of what you say, but I will defend to the death your
right to say it." -- Voltaire
"The people's good is the highest law." -- Cicero
"Code can always be simpler than you think, but never as simple as you
want" -- Me
Ian Kelly
> Ian,
>
> FWIW there's a ticket with a patch (of unkown quality :P) for support
> for using timedeltas with F() expressions.
>
> Alex
I know, I wrote the Oracle backend code for it. :-)
I'm not talking here about passing in literal timedeltas to be added
to TimeFields; I'm talking about adding a TimeField to an
IntervalField implemented as a BigIntField. From a user's standpoint,
if you can do one, then the other should work as well, but that's not
the case.
Cheers,
Ian
Olivier Guilyardi
I personally agree with this. Native datatypes, if possible, are also cleaner
than obscure integer mappings.
The TIME and/or INTERVAL precision depends on the backend, that's all (although
that may be confusing).
And people who need to handle nanoseconds or so can just add custom numeric
fields to their models.
--
Olivier
Olivier Guilyardi
> In other words, all the same limitations that we already handle for
> TimeFields. This looks to me like a green light to just use the
> native datatypes.
I wrote a simple DurationField for my own purpose, it is not meant to be
included in Django as-is, but could serve as a proof-of-concept. See attached
duration.py.
I'm using Django r12271 with MySQL and this DurationField is mapped to a TIME
column. I can retrieve records which contain a day time, that is: the TIME
column is less than 24h. But it fails when it is over 24h.
The following exception is raised:
ValueError: hour must be in 0..23
The traceback was not helpful, saying that it comes from:
File "/[...]/django/db/backends/mysql/base.py", line 86, in execute
return self.cursor.execute(query, args)
After some debugging, I found that this exception is raised by the
datetime.time() constructor, from django/db/backends/util.py line 62:
def typecast_time(s): # does NOT store time zone information
if not s: return None
hour, minutes, seconds = s.split(':')
if '.' in seconds: # check whether seconds have a fractional part
seconds, microseconds = seconds.split('.')
else:
microseconds = '0'
return datetime.time(int(hour), int(minutes), int(seconds),
int(float('.'+microseconds) * 1000000))
This typecast_time() method is used as a converter by MySQLdb, as explained in
django/db/backends/mysql/base.py:
# MySQLdb-1.2.1 returns TIME columns as timedelta -- they are more like
# timedelta in terms of actual behavior as they are signed and include days --
# and Django expects time, so we still need to override that. [...]
I would quite like to receive this timedelta object as an argument to
DurationField.to_python(). But as shown above, it is converted to a time object
at low-level.
So, this is a bit of a user question, but I how can I make my DurationField work
without actually modifying the Django source code?
--
Olivier