You could also store the timedelta as a float seconds as well. Then you wouldn't have to worry about inconsistent string formatting ...
--- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of C M > Sent: Thursday, 26 July, 2012 15:33 > To: General Discussion of SQLite Database > Subject: [sqlite] MIN() for a timedelta? > > I have string representations of a Python timedelta stored in an > SQLite database of the form H:MM:SS:ssssss (the last is microseconds). > Here are a possible examples of such timedeltas: > > '0:00:06.229000' > '9:00:00.000000' > '10:01:23:041000' > > I want to select the shortest duration (the smallest timedelta) using > the SQLite MIN(), like so: > > SELECT MIN(duration) FROM Durations > > The problem is, in Python, the string representation of the timedelta > is not left zero padded, so '9:00:00.000000' (nine hours) is selected > by MIN() as greater than '10:01:23:041000' (ten hours and change). > This is not right in terms of time, as 9 hours is smaller than 10 > hours. > > I could zero pad these strings myself, so that '9:00:00.000000' > becomes '09:00:00.000000', but that would break other uses of these > values in my code and was wondering if there were a way in SQlite to > "see" these values as timedeltas. I tried this: > > SELECT MIN(TIME(duration) FROM Durations > > but that returns nothing. > > Thanks, > Che > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users