On Mon, Nov 2, 2009 at 3:30 PM, Dale E. Moore <[email protected]> wrote: > Hi Puneet; > > Thanks for your feedback! > > My complete picture is that I would I'd like to enter running, cycling, > walking times like hh:mm:ss.hundredths and have them saved magically as > DURATION in sqlite. Then I would like to run a SELECT SUM(DURATION) ... > GROUP BY something-or-other and have yy:months:dd.hh:mm:ss.hundredths > appear. > > If I kept them as separate columns, when I do the summing it seems like kind > of a mess to put them all back together. I mean I don't want 240 hours to > appear as 240:0:0.0 I would like it to appear as 10.0:0:0.0 or 10 days, 0 > hours, 0 minutes, 0 seconds and 0 hundredths. > > I keep hoping that there might be some magical method or format of using > STRFTIME that would give me the results I want from a SELECT statement. > > But, I think that I what I am hearing from you is that you know of no way to > do that, and I need to write some code in my application if I want to > convert seconds to years-months-days.hours:minutes:seconds.hundredths. >
No, you still didn't give the complete picture (or, at least, you didn't spell it out). Are you building an application in a particular language, using sqlite as the store, or do you want to do all your magic with the sqlite command line tool? You could store your start time and end times as timestamps in the 'yyyy-mm-dd hh:mm:ss' format, and then use the datetime functions to do the math, calc the duration and format it. You would still have to do your math, and do your own calcs. The datetime functions don't store duration. They store timestamps. 22 days, 33 mins and 10 seconds is very different from '2009-10-22 10:23:01'. > I look forward to hearing from you, > Dale > _______________________________________________ > sqlite-users mailing list > [email protected] > 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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
