On Thu, Jul 26, 2012 at 6:45 PM, Nico Williams <n...@cryptonector.com> wrote:
> On Thu, Jul 26, 2012 at 4:32 PM, C M <cmpyt...@gmail.com> wrote:
>> 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.
>
> Just use CASE to add the missing zero as necessary, something like this:
>
> SELECT strftime('%s', (SELECT CASE WHEN '9:12:32' LIKE '0%' THEN
> '9:12:32' ELSE '0' || '9:12:32' END));
>
> Replace '9:12:32' there with whatever expression, probably a column name.
Thanks but I can't figure out how to use that to get the MIN()
timedelta. For example, if I try this (the table is called
Durations...the column is duration):
SELECT MIN(CASE WHEN duration LIKE '0%' THEN
duration ELSE '0' || duration END) FROM Durations WHERE duration != ''
it returns:
01:00:00:000000
which is definitely not the min timedelta in the table.
If I try:
SELECT (SELECT strftime('%s', (SELECT CASE WHEN duration LIKE '0%' THEN
duration ELSE '0' || duration END))) FROM Durations
It returns a large number of results, including negative values like -413362495.
I'm really lost on how to combine the above into a sensible query to
get the minimum timedelta.
Che
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
