See below.
-----Original Message-----
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Simon Slavin
Sent: Thursday, July 26, 2012 8:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] MIN() for a timedelta?
On 27 Jul 2012, at 12:04am, C M <cmpyt...@gmail.com> wrote:
> On Thu, Jul 26, 2012 at 6:45 PM, Nico Williams <n...@cryptonector.com> wrote:
>>
>>
>> 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.
As near as I can tell, your query should have worked. What is the minimum
timedelta in the table? Or, if the table's too big to answer that, can you
please give us an example a timedelta that should have been smaller than this?
Also, can you get rid of the MIN and add ORDER BY Durations and see what data
your CASE statement generates?
RobR
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
