Am 26.07.2012 23:32, schrieb C M:
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This seems to work:
C:\Users\Bernd>sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t (duration);
sqlite>
sqlite> insert into t values ('9:34:15:000000');
sqlite> insert into t values ('19:22:44:003500');
sqlite> insert into t values ('6:17:29:120000');
sqlite> insert into t values ('22:18:00:937500');
sqlite> insert into t values ('0:02:11:000000');
sqlite>
sqlite> select min(duration) from t;
0:02:11:000000
sqlite> select max(duration) from t;
9:34:15:000000
sqlite> select min(case when length(duration) = 15 then duration else
'0' || du
ration end) from t;
00:02:11:000000
sqlite> select max(case when length(duration) = 15 then duration else
'0' || du
ration end) from t;
22:18:00:937500
sqlite> .q
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
