Re: [sqlite] MIN() for a timedelta?

[C M]

Thanks everyone for the various replies and help.  Very useful and I
will look into the differences and if I have questions about how these
work will let you know.

Thank you,
Che
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MIN() for a timedelta?

[Bernd Lehmkuhl]

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:ss (the last is microseconds).
  Here are a possible examples of such timedeltas:

'0:00:06.229000'
'9:00:00.00'
'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.00' (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.00'
becomes '09:00:00.00', 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


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:00');
sqlite> insert into t values ('19:22:44:003500');
sqlite> insert into t values ('6:17:29:12');
sqlite> insert into t values ('22:18:00:937500');
sqlite> insert into t values ('0:02:11:00');
sqlite>
sqlite> select min(duration) from t;
0:02:11:00
sqlite> select max(duration) from t;
9:34:15:00
sqlite> select min(case when length(duration) = 15  then duration else 
'0' || du

ration end) from t;
00:02:11:00
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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MIN() for a timedelta?

[Rob Richardson]

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:00
> 
> 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

Re: [sqlite] MIN() for a timedelta?

[Keith Medcalf]

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:ss (the last is microseconds).
>  Here are a possible examples of such timedeltas:
> 
> '0:00:06.229000'
> '9:00:00.00'
> '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.00' (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.00'
> becomes '09:00:00.00', 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

Re: [sqlite] MIN() for a timedelta?

[Keith Medcalf]

Select min(substr('0' || duration, -15)) from durations;


---
()  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:ss (the last is microseconds).
>  Here are a possible examples of such timedeltas:
> 
> '0:00:06.229000'
> '9:00:00.00'
> '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.00' (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.00'
> becomes '09:00:00.00', 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

Re: [sqlite] MIN() for a timedelta?

[Igor Tandetnik]

On 7/26/2012 5:32 PM, C M wrote:

I have string representations of a Python timedelta stored in an
SQLite database of the form H:MM:SS:ss (the last is microseconds).
  Here are a possible examples of such timedeltas:

'0:00:06.229000'
'9:00:00.00'
'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


Something like this perhaps:

select min(substr('0', 1, 15-length(duration)) || duration) from Durations;

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MIN() for a timedelta?

[Simon Slavin]

On 27 Jul 2012, at 12:04am, C M  wrote:

> On Thu, Jul 26, 2012 at 6:45 PM, Nico Williams  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:00
> 
> which is definitely not the min timedelta in the table.

So explore it.  Try variations until you can figure out what's going on.  For 
example, execute

SELECT  MIN(duration) FROM Durations WHERE duration LIKE '0%'

and

SELECT  MIN(duration) FROM Durations WHERE duration NOT LIKE '0%'

and see what you get.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MIN() for a timedelta?

[C M]

On Thu, Jul 26, 2012 at 6:45 PM, Nico Williams  wrote:
> On Thu, Jul 26, 2012 at 4:32 PM, C M  wrote:
>> I could zero pad these strings myself, so that '9:00:00.00'
>> becomes '09:00:00.00', 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:00

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

Re: [sqlite] MIN() for a timedelta?

[Nico Williams]

On Thu, Jul 26, 2012 at 4:32 PM, C M  wrote:
> I could zero pad these strings myself, so that '9:00:00.00'
> becomes '09:00:00.00', 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.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users