Re: [sqlite] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

[Dale E. Moore]

I've been fiddling through Nabble, and I've been missing things. (Perhaps
it's me and not Nabble;(

You guys are WONDERFUL!

David, Igor, Alex; all have given me vital information and I very much
appreciate it!

Thanks!!
Dale

On Mon, Nov 2, 2009 at 4:24 PM, David Bicking  wrote:

> I probably should have explicitly stated that my suggestion only worked
> up to 24 hours. Unfortunately I couldn't think of a solution for greater
> values.
>
> Yesterday Igor posted a solution that works with days. You never
> responded to him so perhaps you didn't see it. I'll copy it here:
> **
> SELECT
>cast(secs/86400 as integer) || '.' ||
>strftime('%H:%M:%f', secs ,'unixepoch') from
> (select SUM(tripSeconds) as secs from mytable);
>
> Igor Tandetnik
> **
>
> You really can't go higher than "days" because months come in different
> sizes, as do years, i.e. months can be either 28, 29, 30 or 31 days
> depending on the time of year, etc.
> Years can be 365 or 366 days.
>
> If you want to make every MONTH=30 DAYS, well, you would need to do the
> math. Probably would be much easier to do in your application program
> than in SQL.
>
> Hopefully that helps.
>
> David
>
>
> On Mon, 2009-11-02 at 03:22 -0800, DaleEMoore wrote:
> > Hi David;
> >
> > That's LOVELY for small numbers like:
> >
> > SELECT STRFTIME('%H:%M:%f',62.5,'unixepoch')
> > 00:01:02.500
> >
> > What do you think I should do with larger periods of time? In the
> following
> > I hoped for 00-00-00.01:02.500.
> >
> > SELECT STRFTIME('%Y-%m-%d.%H:%M:%f',62.5,'unixepoch')
> > 1970-01-01.00:01:02.500
> >
> > I really appreciate hearing from you,
> > Dale
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

[David Bicking]

I probably should have explicitly stated that my suggestion only worked
up to 24 hours. Unfortunately I couldn't think of a solution for greater
values.

Yesterday Igor posted a solution that works with days. You never
responded to him so perhaps you didn't see it. I'll copy it here:
**
SELECT
cast(secs/86400 as integer) || '.' || 
strftime('%H:%M:%f', secs ,'unixepoch') from
(select SUM(tripSeconds) as secs from mytable);

Igor Tandetnik
**

You really can't go higher than "days" because months come in different
sizes, as do years, i.e. months can be either 28, 29, 30 or 31 days
depending on the time of year, etc.
Years can be 365 or 366 days.

If you want to make every MONTH=30 DAYS, well, you would need to do the
math. Probably would be much easier to do in your application program
than in SQL.

Hopefully that helps.

David


On Mon, 2009-11-02 at 03:22 -0800, DaleEMoore wrote:
> Hi David;
> 
> That's LOVELY for small numbers like:
> 
> SELECT STRFTIME('%H:%M:%f',62.5,'unixepoch')
> 00:01:02.500
> 
> What do you think I should do with larger periods of time? In the following
> I hoped for 00-00-00.01:02.500.
> 
> SELECT STRFTIME('%Y-%m-%d.%H:%M:%f',62.5,'unixepoch')
> 1970-01-01.00:01:02.500
> 
> I really appreciate hearing from you,
> Dale

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

Re: [sqlite] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

[P Kishor]

On Mon, Nov 2, 2009 at 1:08 PM, DaleEMoore  wrote:
>
> Thanks David SELECT STRFTIME('%H:%M:%f',62.5,'unixepoch') works great for
> seconds less than 1 day!
>
> I wonder what I should do where the time becomes days, weeks, months, years?


I have no idea why you think it won't work for long time periods

sqlite> SELECT STRFTIME('%H:%M:%f',62.5,'unixepoch');
00:01:02.500
sqlite> SELECT STRFTIME('%H:%M:%f',12345678762.5,'unixepoch');
19:12:42.500
sqlite>


>
> Do you have any suggestions about how to handle:
>
> SELECT STRFTIME('%y-%m-%d.%H:%M:%f',962.5,'unixepoch')
>
> I look forward to hearing from you,
> Dale E. Moore
> --
> View this message in context: 
> http://old.nabble.com/SUM%28tripSeconds%29-and-format-output-as-days.hours%3Aminutes%3Aseconds.hundredths--tp26154270p26157787.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

[DaleEMoore]

Hi David;

That's LOVELY for small numbers like:

SELECT STRFTIME('%H:%M:%f',62.5,'unixepoch')
00:01:02.500

What do you think I should do with larger periods of time? In the following
I hoped for 00-00-00.01:02.500.

SELECT STRFTIME('%Y-%m-%d.%H:%M:%f',62.5,'unixepoch')
1970-01-01.00:01:02.500

I really appreciate hearing from you,
Dale
-- 
View this message in context: 
http://old.nabble.com/SUM%28tripSeconds%29-and-format-output-as-days.hours%3Aminutes%3Aseconds.hundredths--tp26154270p26156678.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

[Dr. David Kirkby]

Alex Mandel wrote:
> DaleEMoore wrote:
>> I'd like to SUM(tripSeconds) and format output as
>> days.hours:minutes:seconds.hundredths, but have not been able to figure out
>> how to do that with sqlite. This didn't seem to come close:
>>
>> SELECT 
>> STRFTIME('%d', SUM(tripSeconds)) + '.' +
>> STRFTIME('%H', SUM(tripSeconds)) + ':' +
>> STRFTIME('%M', SUM(tripSeconds)) + ':' +
>> STRFTIME('%f', SUM(tripSeconds)) AS Duration, 
>> SUM(tripSeconds)
>>
>> Any ideas are appreciated,
>> Dale E. Moore

I assume this involves multiple lookups to get the time. It could so happen 
they 
are a different second, hour, day or even year. That looks dangerous to me.

> What about something like
> SELECT SUM(STRFTIME('%s', tripSeconds)) AS Duration
> from table
> GROUP BY trip
> 
> Alex


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

Re: [sqlite] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

[Igor Tandetnik]

DaleEMoore wrote:
> I'd like to SUM(tripSeconds) and format output as
> days.hours:minutes:seconds.hundredths, but have not been able to figure out
> how to do that with sqlite. This didn't seem to come close:
> 
> SELECT
> STRFTIME('%d', SUM(tripSeconds)) + '.' +
> STRFTIME('%H', SUM(tripSeconds)) + ':' +
> STRFTIME('%M', SUM(tripSeconds)) + ':' +
> STRFTIME('%f', SUM(tripSeconds)) AS Duration,
> SUM(tripSeconds)

SELECT
cast(secs/86400 as integer) || '.' || 
strftime('%H:%M:%f', secs ,'unixepoch') from
(select SUM(tripSeconds) as secs from mytable);

Igor Tandetnik


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

Re: [sqlite] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

[DaleEMoore]

SELECT  SUM(STRFTIME('%s', 62.4)) gets -210861368640 and I would like
0.0:01:02.4 or the appropriate days.hours:minutes:seconds.hundredths.
-- 
View this message in context: 
http://old.nabble.com/SUM%28tripSeconds%29-and-format-output-as-days.hours%3Aminutes%3Aseconds.hundredths--tp26154270p26155824.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

[David Bicking]

On Sun, 2009-11-01 at 12:15 -0800, DaleEMoore wrote:
> I'd like to SUM(tripSeconds) and format output as
> days.hours:minutes:seconds.hundredths, but have not been able to figure out
> how to do that with sqlite. This didn't seem to come close:
> 
> SELECT 
> STRFTIME('%d', SUM(tripSeconds)) + '.' +
> STRFTIME('%H', SUM(tripSeconds)) + ':' +
> STRFTIME('%M', SUM(tripSeconds)) + ':' +
> STRFTIME('%f', SUM(tripSeconds)) AS Duration, 
> SUM(tripSeconds)
> 
> Any ideas are appreciated,
> Dale E. Moore

Assuming you have seconds in tripSeconds, this seems to come close to
what you want:

SELECT STRFTIME('%H:%M:%f',duration,'unixepoch') from (SELECT
SUM(tripSeconds) FROM yourtable);

David

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

Re: [sqlite] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

[DaleEMoore]

Hi Alex;

SUM(STRFTIME('%s', tripSeconds)) gives me -210748564714, what did you get?

I look forward to hearing from you,
Dale

-- 
View this message in context: 
http://old.nabble.com/SUM%28tripSeconds%29-and-format-output-as-days.hours%3Aminutes%3Aseconds.hundredths--tp26154270p26154644.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SUM(tripSeconds) and format output as days.hours:minutes:seconds.hundredths?

[Alex Mandel]

DaleEMoore wrote:
> I'd like to SUM(tripSeconds) and format output as
> days.hours:minutes:seconds.hundredths, but have not been able to figure out
> how to do that with sqlite. This didn't seem to come close:
> 
> SELECT 
> STRFTIME('%d', SUM(tripSeconds)) + '.' +
> STRFTIME('%H', SUM(tripSeconds)) + ':' +
> STRFTIME('%M', SUM(tripSeconds)) + ':' +
> STRFTIME('%f', SUM(tripSeconds)) AS Duration, 
> SUM(tripSeconds)
> 
> Any ideas are appreciated,
> Dale E. Moore

What about something like
SELECT SUM(STRFTIME('%s', tripSeconds)) AS Duration
from table
GROUP BY trip

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