Re: [O] org table calc and lisp for hh:mm timetable
Hi all, Carsten Dominik writes: > How about introducing another flag for the formula to turn > on time string processing like this? I've implemented this. You can now use the "T" flag to compute durations: | Task 1 | Task 2 | Total | |++-| | 35:00 | 35:00 | 1:10:00 | #+TBLFM: @2$3=$1+$2;T The "T" flag works also for Elisp formulas. | Task 1 | Task 2 | Total | |++-| | 35:00 | 35:00 | 1:10:00 | #+TBLFM: @2$3='(+ $1 $2);T Time values must be of the form [HH:]MM:SS. Thanks to everyone for ideas about this! Of course, timestamp manipulation is what comes next. But please test this and report any problem. Best, -- Bastien
Re: [O] org table calc and lisp for hh:mm timetable
>> If we're not just looking for a neat workaround for some special cases >> anymore, but looking at making org-tables aware of time-like strings >> by default, a better strategy than to convert them into integers might >> be to translate them into Calc time format and back again. >> >> After all, Calc and hence the Org spreadsheet already handles time >> arithmetic perfectly well, it's just that it would be nice to have >> this functionality with the extra option to enter and display times as >> =12:45= rather than =12@ 45'= or =12h 45m=. >> > > I did not realize that calc (and hence Org) already supported operating > on time values. The =7@ 30'= format does indeed work well for time > table formulas (although the =12h 5m= format does not), e.g., > > | 1@ 20' | 2 | 0@ 40' 0." | > | 2@ 5' | 3 | 0@ 41' 40." | > #+TBLFM: $3=$1/$2 > > Maybe all that is needed is explicit mention of this syntax somewhere in > the table section of the Org-mode manual. > > The only remaining reason to support a %d:%d time format is that it is > what I (and I expect most users) would first think of to represent time, > and supporting such a format would allow time calculation in a table to > "just work" -- meaning users could likely manipulate time without having > to first look in the manual for the appropriate Calc format. > > However as Carsten mentioned such an automatic translation of e.g., > =7:20= to =7@ 20'= would be a bit bold, and (as mentioned elsewhere) it > would result in having to make some denomination decisions, namely does > =7:20= convert to =7@ 20'= or =7' 20"=. > > For now I've posted my `with-time' macro up to Worg. > > Cheers -- Eric Hi all, just came back on this thread and was amazed what you guys did with it.. amazing.. havent tried the patch yet but the with-time macro is great. Now it is even easy to sum up a whole column of timevalues. I use it like the following now: | start | lunch | back | leave | sum | |---+---+---+---+---| | 8:00 | 12:00 | 13:00 | 17:00 | 8:00 | | 8:00 | 12:00 | 13:00 | 17:00 | 8:00 | |---+---+---+---+---| | | | | | 16:00 | #+TBLFM: @>$5='(with-time t (+ @2..@>>)) Thanks a lot (also for making it to org hacks : ) Martin
Re: [O] org table calc and lisp for hh:mm timetable
Christian Moe writes: > Hi, > > If we're not just looking for a neat workaround for some special cases > anymore, but looking at making org-tables aware of time-like strings > by default, a better strategy than to convert them into integers might > be to translate them into Calc time format and back again. > > After all, Calc and hence the Org spreadsheet already handles time > arithmetic perfectly well, it's just that it would be nice to have > this functionality with the extra option to enter and display times as > =12:45= rather than =12@ 45'= or =12h 45m=. > I did not realize that calc (and hence Org) already supported operating on time values. The =7@ 30'= format does indeed work well for time table formulas (although the =12h 5m= format does not), e.g., | 1@ 20' | 2 | 0@ 40' 0." | | 2@ 5' | 3 | 0@ 41' 40." | #+TBLFM: $3=$1/$2 Maybe all that is needed is explicit mention of this syntax somewhere in the table section of the Org-mode manual. The only remaining reason to support a %d:%d time format is that it is what I (and I expect most users) would first think of to represent time, and supporting such a format would allow time calculation in a table to "just work" -- meaning users could likely manipulate time without having to first look in the manual for the appropriate Calc format. However as Carsten mentioned such an automatic translation of e.g., =7:20= to =7@ 20'= would be a bit bold, and (as mentioned elsewhere) it would result in having to make some denomination decisions, namely does =7:20= convert to =7@ 20'= or =7' 20"=. For now I've posted my `with-time' macro up to Worg. Cheers -- Eric
Re: [O] org table calc and lisp for hh:mm timetable
On 22.3.2011, at 05:40, Eric Schulte wrote: >> >> While this topic is raised, would it make sense for Org-mode table >> formula to automatically parse any time-like string into time units >> (i.e., base sixty). That would be the easiest for most users, and (I >> imagine) would rarely result in surprising and unexpected behavior. >> > > So, I took a shot at folding this into org-table.el, the resulting patch > is attached. I'm not sure if this sort of automatic interpretation of > time-like strings into integers is a good idea, or if this is the best > implementation (I'm not incredibly familiar with Org's table handling) > but after a couple of simple tests the patch does seem to work. For > example the following... > > | 2:30 | 2 | 75 | > #+TBLFM: $3=$1/$2 I think it might be a bit bold to turn this on by default. How about introducing another flag for the formula to turn on time string processing like this? - Carsten > > It may make sense to also include functionality for converting the > result back into a time string, e.g. > > #+begin_src emacs-lisp > (defun org-time-seconds-to-string (secs) >"Convert a number of seconds to a time string." >(cond ((>= secs 3600) (format-seconds "%h:%.2m:%.2s" secs)) > ((>= secs 60) (format-seconds "%m:%.2s" secs)) > (t (format-seconds "%s" secs > #+end_src > > | 2:30 | 2 | 1:15 | > #+TBLFM: $3='(org-time-seconds-to-string (/ (string-to-number $1) > (string-to-number $2))) > > While the above is cumbersome, there may be a simpler syntax or > convention -- e.g., whenever one of the inputs is a time string then the > results are displayed as a time string. Not sure what the best option > is here, but thought this patch may spur some good suggestions. > > Best -- Eric > > From 76b416013ee4c9a492c8ddced57727215165c298 Mon Sep 17 00:00:00 2001 > From: Eric Schulte > Date: Mon, 21 Mar 2011 19:43:19 -0600 > Subject: [PATCH] org-table: convert times to integers on table formula > evaluation > > * lisp/org-table.el (org-table-to-time): If cell contents look like a > time string, then converts to an integer. > (org-table-eval-formula): Convert times to integers on table formula > evaluation. > --- > lisp/org-table.el | 26 ++ > 1 files changed, 22 insertions(+), 4 deletions(-) > > diff --git a/lisp/org-table.el b/lisp/org-table.el > index 3573032..3674b53 100644 > --- a/lisp/org-table.el > +++ b/lisp/org-table.el > @@ -2273,6 +2273,21 @@ of the new mark." > (cons var (cons value modes))) > modes) > > +(defun org-table-to-time (s) > + "Convert cell to numerical time if contents look like a time string." > + (cond > + ((and (stringp s) > + (string-match "\\([0-9]+\\):\\([0-9]+\\):\\([0-9]+\\)" s)) > +(let ((hour (string-to-number (match-string 1 s))) > + (min (string-to-number (match-string 2 s))) > + (sec (string-to-number (match-string 3 s > + (+ (* hour 3600) (* min 60) sec))) > + ((and (stringp s) > + (string-match "\\([0-9]+\\):\\([0-9]+\\)" s)) > +(let ((min (string-to-number (match-string 1 s))) > + (sec (string-to-number (match-string 2 s > + (+ (* min 60) sec) > + > (defun org-table-eval-formula (&optional arg equation >suppress-align suppress-const >suppress-store suppress-analysis) > @@ -2369,10 +2384,13 @@ not overwrite the stored one." > (setq formula (org-table-formula-substitute-names formula))) > (setq orig (or (get-text-property 1 :orig-formula formula) "?")) > (while (> ndown 0) > - (setq fields (org-split-string > - (org-no-properties > -(buffer-substring (point-at-bol) (point-at-eol))) > - " *| *")) > + (setq fields (mapcar (lambda (cell) > +(let ((time (org-table-to-time cell))) > + (if time (number-to-string time) cell))) > + (org-split-string > + (org-no-properties > +(buffer-substring (point-at-bol) (point-at-eol))) > + " *| *"))) > (if (eq numbers t) > (setq fields (mapcar > (lambda (x) (number-to-string (string-to-number x))) > -- > 1.7.1 >
Re: [O] org table calc and lisp for hh:mm timetable
Hi, If we're not just looking for a neat workaround for some special cases anymore, but looking at making org-tables aware of time-like strings by default, a better strategy than to convert them into integers might be to translate them into Calc time format and back again. After all, Calc and hence the Org spreadsheet already handles time arithmetic perfectly well, it's just that it would be nice to have this functionality with the extra option to enter and display times as =12:45= rather than =12@ 45'= or =12h 45m=. (Personally, I can live with a =12h 45m= format, which is already a Calc option: info:calc#HMS%20Forms, info:calc#HMS%20Formats.) Consider this: | Departure | Travel time | Arrival| |+-+| | [2011-03-22 Tue 23:15] | 7@ 05' | <2011-03-23 Wed 06:20> | #+TBLFM: $3=<$1>+$2 With Calc, you can add a HMS time to a date-time and get the resulting date-time. However, Calc apparently interprets integers as days, not minutes or seconds. So if you convert 7:05 to an integer (425) and add it to a date-time, you get a date about one year and two months ahead. (Converting 7:05 to the integer 0.295139 days would work... but with rounding problems.) The Org spreadsheet already allows us to give the date-time as an Org-style timestamp rather than in the Calc date format by using the angle brackets in the formula. The solution would be similar option for time-like strings, so we could write the travel time above as =7:05=, and so the result below would be returned as =7:05=. | Arrival| Departure | Travel time | |++-| | <2011-03-23 Wed 06:20> | [2011-03-22 Tue 23:15] | 7@ 5' 0"| #+TBLFM: $3=time(<$1>-<$2>) Yours, Christian On 3/22/11 5:40 AM, Eric Schulte wrote: While this topic is raised, would it make sense for Org-mode table formula to automatically parse any time-like string into time units (i.e., base sixty). That would be the easiest for most users, and (I imagine) would rarely result in surprising and unexpected behavior. So, I took a shot at folding this into org-table.el, the resulting patch is attached. I'm not sure if this sort of automatic interpretation of time-like strings into integers is a good idea, or if this is the best implementation (I'm not incredibly familiar with Org's table handling) but after a couple of simple tests the patch does seem to work. For example the following... | 2:30 | 2 | 75 | #+TBLFM: $3=$1/$2 It may make sense to also include functionality for converting the result back into a time string, e.g. #+begin_src emacs-lisp (defun org-time-seconds-to-string (secs) "Convert a number of seconds to a time string." (cond ((>= secs 3600) (format-seconds "%h:%.2m:%.2s" secs)) ((>= secs 60) (format-seconds "%m:%.2s" secs)) (t (format-seconds "%s" secs #+end_src | 2:30 | 2 | 1:15 | #+TBLFM: $3='(org-time-seconds-to-string (/ (string-to-number $1) (string-to-number $2))) While the above is cumbersome, there may be a simpler syntax or convention -- e.g., whenever one of the inputs is a time string then the results are displayed as a time string. Not sure what the best option is here, but thought this patch may spur some good suggestions. Best -- Eric
Re: [O] org table calc and lisp for hh:mm timetable
> > While this topic is raised, would it make sense for Org-mode table > formula to automatically parse any time-like string into time units > (i.e., base sixty). That would be the easiest for most users, and (I > imagine) would rarely result in surprising and unexpected behavior. > So, I took a shot at folding this into org-table.el, the resulting patch is attached. I'm not sure if this sort of automatic interpretation of time-like strings into integers is a good idea, or if this is the best implementation (I'm not incredibly familiar with Org's table handling) but after a couple of simple tests the patch does seem to work. For example the following... | 2:30 | 2 | 75 | #+TBLFM: $3=$1/$2 It may make sense to also include functionality for converting the result back into a time string, e.g. #+begin_src emacs-lisp (defun org-time-seconds-to-string (secs) "Convert a number of seconds to a time string." (cond ((>= secs 3600) (format-seconds "%h:%.2m:%.2s" secs)) ((>= secs 60) (format-seconds "%m:%.2s" secs)) (t (format-seconds "%s" secs #+end_src | 2:30 | 2 | 1:15 | #+TBLFM: $3='(org-time-seconds-to-string (/ (string-to-number $1) (string-to-number $2))) While the above is cumbersome, there may be a simpler syntax or convention -- e.g., whenever one of the inputs is a time string then the results are displayed as a time string. Not sure what the best option is here, but thought this patch may spur some good suggestions. Best -- Eric >From 76b416013ee4c9a492c8ddced57727215165c298 Mon Sep 17 00:00:00 2001 From: Eric Schulte Date: Mon, 21 Mar 2011 19:43:19 -0600 Subject: [PATCH] org-table: convert times to integers on table formula evaluation * lisp/org-table.el (org-table-to-time): If cell contents look like a time string, then converts to an integer. (org-table-eval-formula): Convert times to integers on table formula evaluation. --- lisp/org-table.el | 26 ++ 1 files changed, 22 insertions(+), 4 deletions(-) diff --git a/lisp/org-table.el b/lisp/org-table.el index 3573032..3674b53 100644 --- a/lisp/org-table.el +++ b/lisp/org-table.el @@ -2273,6 +2273,21 @@ of the new mark." (cons var (cons value modes))) modes) +(defun org-table-to-time (s) + "Convert cell to numerical time if contents look like a time string." + (cond + ((and (stringp s) + (string-match "\\([0-9]+\\):\\([0-9]+\\):\\([0-9]+\\)" s)) +(let ((hour (string-to-number (match-string 1 s))) + (min (string-to-number (match-string 2 s))) + (sec (string-to-number (match-string 3 s + (+ (* hour 3600) (* min 60) sec))) + ((and (stringp s) + (string-match "\\([0-9]+\\):\\([0-9]+\\)" s)) +(let ((min (string-to-number (match-string 1 s))) + (sec (string-to-number (match-string 2 s + (+ (* min 60) sec) + (defun org-table-eval-formula (&optional arg equation suppress-align suppress-const suppress-store suppress-analysis) @@ -2369,10 +2384,13 @@ not overwrite the stored one." (setq formula (org-table-formula-substitute-names formula))) (setq orig (or (get-text-property 1 :orig-formula formula) "?")) (while (> ndown 0) - (setq fields (org-split-string - (org-no-properties - (buffer-substring (point-at-bol) (point-at-eol))) - " *| *")) + (setq fields (mapcar (lambda (cell) + (let ((time (org-table-to-time cell))) + (if time (number-to-string time) cell))) + (org-split-string + (org-no-properties + (buffer-substring (point-at-bol) (point-at-eol))) + " *| *"))) (if (eq numbers t) (setq fields (mapcar (lambda (x) (number-to-string (string-to-number x))) -- 1.7.1
Re: [O] org table calc and lisp for hh:mm timetable
Christian Moe writes: > Hi, > > Returning to this thread: > > 1. I love Eric's macro wrapper idea -- now time arithmetic in tables > gets truly manageable. If it's not included into Org-mode, it's a must > for Worg! > Great, if no Org-mode changes result, then I will certainly post this code up to Worg. > > 2. There's duplication with org-timer-hms-to-secs and > org-timer-secs-to-hms. (Cf. my > http://permalink.gmane.org/gmane.emacs.orgmode/39501.) > I believe these new functions are slightly more forgiving of alternate time format strings, than the strict format expected by the org-timer-* functions, however maybe it makes sense to consolidate these and the org-timer-* functions around a set of core org-time functions. > > Do Martin's/Bastien's/Eric's hms/seconds conversion functions add > value that should be patched into org-timer? > I think of these as separate from org-timer given that they mainly deal with table formulas, however maybe both these and org-timer-* could benefit from a centralized org-time-* functionality. > > 3. One thing Eric's converters do and org-timer doesn't is to handle a > string with only two two-digit groups (e.g. 12:45). > > Eric's converters interpret it as 12m 45s -- good for running > times. The functions I posted (see link above) interpreted such > strings as 12h 45m -- good for time of day. > > I suggest the latter is more convenient for most use cases: When I'm > working with seconds (running times, audio track durations) it's sort > of a technical use, so I'm prepared to add 0 hours in front. When a > time of day like 12:45 is good enough, I don't want to have to add 00 > seconds in back. (And am/pm is not used in my locale.) > I think the best would be for these functions (at least the table formula functions) to remain agnostic as to the actual denomination of the time, but rather just parse *:* as base sixty digits. That way a string like "1:20" could mean 80 minutes or 80 seconds, the parser needs not know which, and the formula writer would /hopefully/ get back what's expected. While this topic is raised, would it make sense for Org-mode table formula to automatically parse any time-like string into time units (i.e., base sixty). That would be the easiest for most users, and (I imagine) would rarely result in surprising and unexpected behavior. Best -- Eric > > Yours, > Christian > > On 3/20/11 6:50 PM, Eric Schulte wrote: >> I wrapped Bastien's functions below in a simple macro, which IMO results >> in a very nice way to handle time values in Org-mode tables as shown >> below. >> >> Note, the first argument to the `with-time' macro controls whether >> results are returned as a time string or a numerical value. That >> argument may be followed by any number of expressions. >> >> | time | miles | minutes/mile | >> |---+---+--| >> | 34:43 | 2.9 |11:58 | >> | 56:00 | 5.5 |10:10 | >> | 31:00 | 3.04 |10:11 | >> | 32:15 | 2.77 |11:38 | >> | 33:56 | 3.0 |11:18 | >> | 72:00 | 6.74 |10:40 | >> | 52:22 | 4.62 |11:20 | >> #+TBLFM: $3='(with-time t (/ $1 $2)) >> >> #+begin_src emacs-lisp >>(defun org-time-string-to-seconds (s) >> "Convert a string HH:MM:SS to a number of seconds." >> (cond >> ((and (stringp s) >> (string-match "\\([0-9]+\\):\\([0-9]+\\):\\([0-9]+\\)" s)) >>(let ((hour (string-to-number (match-string 1 s))) >> (min (string-to-number (match-string 2 s))) >> (sec (string-to-number (match-string 3 s >> (+ (* hour 3600) (* min 60) sec))) >> ((and (stringp s) >> (string-match "\\([0-9]+\\):\\([0-9]+\\)" s)) >>(let ((min (string-to-number (match-string 1 s))) >> (sec (string-to-number (match-string 2 s >> (+ (* min 60) sec))) >> ((stringp s) (string-to-number s)) >> (t s))) >> >>(defun org-time-seconds-to-string (secs) >> "Convert a number of seconds to a time string." >> (cond ((>= secs 3600) (format-seconds "%h:%.2m:%.2s" secs)) >>((>= secs 60) (format-seconds "%m:%.2s" secs)) >>(t (format-seconds "%s" secs >> >>(defmacro with-time (time-output-p&rest exprs) >> "Evaluate an org-table formula, converting all fields that look >>like time data to integer seconds. If TIME-OUTPUT-P then return >>the result as a time value." >> (list >> (if time-output-p 'org-time-seconds-to-string 'identity) >> (cons 'progn >> (mapcar >> (lambda (expr) >>`,(cons (car expr) (mapcar #'org-time-string-to-seconds (cdr >> expr >> `,@exprs >> #+end_src >> >> Bastien writes: >> >>> Hi Martin, >>> >>> Martin Halder writes: >>> this is fantastic, already love lisp, thanks a lot.. now I have exactly what I wanted.. additionally I needed the time format in industrial mode (1h = 100m = 100s), implement
Re: [O] org table calc and lisp for hh:mm timetable
Hi, Returning to this thread: 1. I love Eric's macro wrapper idea -- now time arithmetic in tables gets truly manageable. If it's not included into Org-mode, it's a must for Worg! 2. There's duplication with org-timer-hms-to-secs and org-timer-secs-to-hms. (Cf. my http://permalink.gmane.org/gmane.emacs.orgmode/39501.) Do Martin's/Bastien's/Eric's hms/seconds conversion functions add value that should be patched into org-timer? 3. One thing Eric's converters do and org-timer doesn't is to handle a string with only two two-digit groups (e.g. 12:45). Eric's converters interpret it as 12m 45s -- good for running times. The functions I posted (see link above) interpreted such strings as 12h 45m -- good for time of day. I suggest the latter is more convenient for most use cases: When I'm working with seconds (running times, audio track durations) it's sort of a technical use, so I'm prepared to add 0 hours in front. When a time of day like 12:45 is good enough, I don't want to have to add 00 seconds in back. (And am/pm is not used in my locale.) Yours, Christian On 3/20/11 6:50 PM, Eric Schulte wrote: I wrapped Bastien's functions below in a simple macro, which IMO results in a very nice way to handle time values in Org-mode tables as shown below. Note, the first argument to the `with-time' macro controls whether results are returned as a time string or a numerical value. That argument may be followed by any number of expressions. | time | miles | minutes/mile | |---+---+--| | 34:43 | 2.9 |11:58 | | 56:00 | 5.5 |10:10 | | 31:00 | 3.04 |10:11 | | 32:15 | 2.77 |11:38 | | 33:56 | 3.0 |11:18 | | 72:00 | 6.74 |10:40 | | 52:22 | 4.62 |11:20 | #+TBLFM: $3='(with-time t (/ $1 $2)) #+begin_src emacs-lisp (defun org-time-string-to-seconds (s) "Convert a string HH:MM:SS to a number of seconds." (cond ((and (stringp s) (string-match "\\([0-9]+\\):\\([0-9]+\\):\\([0-9]+\\)" s)) (let ((hour (string-to-number (match-string 1 s))) (min (string-to-number (match-string 2 s))) (sec (string-to-number (match-string 3 s (+ (* hour 3600) (* min 60) sec))) ((and (stringp s) (string-match "\\([0-9]+\\):\\([0-9]+\\)" s)) (let ((min (string-to-number (match-string 1 s))) (sec (string-to-number (match-string 2 s (+ (* min 60) sec))) ((stringp s) (string-to-number s)) (t s))) (defun org-time-seconds-to-string (secs) "Convert a number of seconds to a time string." (cond ((>= secs 3600) (format-seconds "%h:%.2m:%.2s" secs)) ((>= secs 60) (format-seconds "%m:%.2s" secs)) (t (format-seconds "%s" secs (defmacro with-time (time-output-p&rest exprs) "Evaluate an org-table formula, converting all fields that look like time data to integer seconds. If TIME-OUTPUT-P then return the result as a time value." (list (if time-output-p 'org-time-seconds-to-string 'identity) (cons 'progn (mapcar (lambda (expr) `,(cons (car expr) (mapcar #'org-time-string-to-seconds (cdr expr `,@exprs #+end_src Bastien writes: Hi Martin, Martin Halder writes: this is fantastic, already love lisp, thanks a lot.. now I have exactly what I wanted.. additionally I needed the time format in industrial mode (1h = 100m = 100s), implemented in ihms. thanks for these functions -- I allowed myself to add them to Worg/org-hacks.html, in a new "Times computation" section: http://orgmode.org/worg/org-hacks.html I added these functions I myself wrote for a particular purpose: #+begin_src emacs-lisp (defun org-hh:mm:ss-string-to-seconds (s) "Convert a string HH:MM:SS to a number of seconds." (when (string-match "\\([0-9]+\\):\\([0-9]+\\):\\([0-9]+\\)" s) (let ((hour (string-to-number (match-string 1 s))) (min (string-to-number (match-string 2 s))) (sec (string-to-number (match-string 3 s (+ (* hour 3600) (* min 60) sec (defun org-subtract-hh:mm:ss-time (t1 t2) "Substract two hh:mm:ss time values." (let* ((sec (- (org-hh:mm:ss-string-to-seconds t2) (org-hh:mm:ss-string-to-seconds t1))) (hour (floor (/ sec 3600))) (min (floor (/ (- sec (* 3600 hour)) 60))) (secs (round (- sec (* 3600 hour) (* 60 min) (format "%.2d:%.2d:%.2d" hour min secs))) #+end_src With these function, you can subtract durations in a table like this: | Part |Begin | End | Duration | |---+--+--+--| | One | 00:00:00 | 00:01:11 | 00:01:11 | | Two | 00:01:12 | 00:02:00 | 00:00:48 | | Three | 00:02:05 | 00:16:06 | 00:14:01 | #+TBLFM: $4='(org-subtract-hh:mm:ss-time $2 $3) Which was useful for me when I had to derush video files. HTH,
Re: [O] org table calc and lisp for hh:mm timetable
"Eric Schulte" writes: > I wrapped Bastien's functions below in a simple macro, which IMO results > in a very nice way to handle time values in Org-mode tables as in the > attached org file (below). > > Note, the first argument to the `with-time' macro controls whether > results are returned as a time string or a numerical value. That > argument may be followed by any number of (possibly nested) expressions. > > If this looks to be generally useful I'd be happy to post it to worg. Although I don't need it now, I *know* I will need something like this in the future so please do put it up on Worg! Nice and elegant. -- : Eric S Fraga (GnuPG: 0xC89193D8FFFCF67D) in Emacs 24.0.50.1 : using Org-mode version 7.5 (release_7.5.90.g1fb3.dirty)
Re: [O] org table calc and lisp for hh:mm timetable
I wrapped Bastien's functions below in a simple macro, which IMO results in a very nice way to handle time values in Org-mode tables as shown below. Note, the first argument to the `with-time' macro controls whether results are returned as a time string or a numerical value. That argument may be followed by any number of expressions. | time | miles | minutes/mile | |---+---+--| | 34:43 | 2.9 |11:58 | | 56:00 | 5.5 |10:10 | | 31:00 | 3.04 |10:11 | | 32:15 | 2.77 |11:38 | | 33:56 | 3.0 |11:18 | | 72:00 | 6.74 |10:40 | | 52:22 | 4.62 |11:20 | #+TBLFM: $3='(with-time t (/ $1 $2)) #+begin_src emacs-lisp (defun org-time-string-to-seconds (s) "Convert a string HH:MM:SS to a number of seconds." (cond ((and (stringp s) (string-match "\\([0-9]+\\):\\([0-9]+\\):\\([0-9]+\\)" s)) (let ((hour (string-to-number (match-string 1 s))) (min (string-to-number (match-string 2 s))) (sec (string-to-number (match-string 3 s (+ (* hour 3600) (* min 60) sec))) ((and (stringp s) (string-match "\\([0-9]+\\):\\([0-9]+\\)" s)) (let ((min (string-to-number (match-string 1 s))) (sec (string-to-number (match-string 2 s (+ (* min 60) sec))) ((stringp s) (string-to-number s)) (t s))) (defun org-time-seconds-to-string (secs) "Convert a number of seconds to a time string." (cond ((>= secs 3600) (format-seconds "%h:%.2m:%.2s" secs)) ((>= secs 60) (format-seconds "%m:%.2s" secs)) (t (format-seconds "%s" secs (defmacro with-time (time-output-p &rest exprs) "Evaluate an org-table formula, converting all fields that look like time data to integer seconds. If TIME-OUTPUT-P then return the result as a time value." (list (if time-output-p 'org-time-seconds-to-string 'identity) (cons 'progn (mapcar (lambda (expr) `,(cons (car expr) (mapcar #'org-time-string-to-seconds (cdr expr `,@exprs #+end_src Bastien writes: > Hi Martin, > > Martin Halder writes: > >> this is fantastic, already love lisp, thanks a lot.. now I have exactly >> what I wanted.. additionally I needed the time format in industrial mode >> (1h = 100m = 100s), implemented in ihms. > > thanks for these functions -- I allowed myself to add them to > Worg/org-hacks.html, in a new "Times computation" section: > > http://orgmode.org/worg/org-hacks.html > > I added these functions I myself wrote for a particular purpose: > > #+begin_src emacs-lisp > (defun org-hh:mm:ss-string-to-seconds (s) > "Convert a string HH:MM:SS to a number of seconds." > (when (string-match "\\([0-9]+\\):\\([0-9]+\\):\\([0-9]+\\)" s) > (let ((hour (string-to-number (match-string 1 s))) > (min (string-to-number (match-string 2 s))) > (sec (string-to-number (match-string 3 s > (+ (* hour 3600) (* min 60) sec > > (defun org-subtract-hh:mm:ss-time (t1 t2) > "Substract two hh:mm:ss time values." > (let* ((sec (- (org-hh:mm:ss-string-to-seconds t2) >(org-hh:mm:ss-string-to-seconds t1))) >(hour (floor (/ sec 3600))) >(min (floor (/ (- sec (* 3600 hour)) 60))) >(secs (round (- sec (* 3600 hour) (* 60 min) > (format "%.2d:%.2d:%.2d" hour min secs))) > #+end_src > > With these function, you can subtract durations in a table like this: > > | Part |Begin | End | Duration | > |---+--+--+--| > | One | 00:00:00 | 00:01:11 | 00:01:11 | > | Two | 00:01:12 | 00:02:00 | 00:00:48 | > | Three | 00:02:05 | 00:16:06 | 00:14:01 | > #+TBLFM: $4='(org-subtract-hh:mm:ss-time $2 $3) > > Which was useful for me when I had to derush video files. > > HTH,
Re: [O] org table calc and lisp for hh:mm timetable
I wrapped Bastien's functions below in a simple macro, which IMO results in a very nice way to handle time values in Org-mode tables as in the attached org file (below). Note, the first argument to the `with-time' macro controls whether results are returned as a time string or a numerical value. That argument may be followed by any number of (possibly nested) expressions. If this looks to be generally useful I'd be happy to post it to worg. Best -- Eric | time | miles | minutes/mile | |---+---+--| | 34:43 | 2.9 |11:58 | | 56:00 | 5.5 |10:10 | | 31:00 | 3.04 |10:11 | | 32:15 | 2.77 |11:38 | | 33:56 | 3.0 |11:18 | | 72:00 | 6.74 |10:40 | | 52:22 | 4.62 |11:20 | #+TBLFM: $3='(with-time t (/ $1 $2)) #+begin_src emacs-lisp :results silent (defun org-time-string-to-seconds (s) "Convert a string HH:MM:SS to a number of seconds." (cond ((and (stringp s) (string-match "\\([0-9]+\\):\\([0-9]+\\):\\([0-9]+\\)" s)) (let ((hour (string-to-number (match-string 1 s))) (min (string-to-number (match-string 2 s))) (sec (string-to-number (match-string 3 s (+ (* hour 3600) (* min 60) sec))) ((and (stringp s) (string-match "\\([0-9]+\\):\\([0-9]+\\)" s)) (let ((min (string-to-number (match-string 1 s))) (sec (string-to-number (match-string 2 s (+ (* min 60) sec))) ((stringp s) (string-to-number s)) (t s))) (defun org-time-seconds-to-string (secs) "Convert a number of seconds to a time string." (cond ((>= secs 3600) (format-seconds "%h:%.2m:%.2s" secs)) ((>= secs 60) (format-seconds "%m:%.2s" secs)) (t (format-seconds "%s" secs (defmacro with-time (time-output-p &rest exprs) "Evaluate an org-table formula, converting all fields that look like time data to integer seconds. If TIME-OUTPUT-P then return the result as a time value." (list (if time-output-p 'org-time-seconds-to-string 'identity) (cons 'progn (mapcar (lambda (expr) `,(cons (car expr) (mapcar (lambda (el) (if (listp el) (list 'with-time nil el) (org-time-string-to-seconds el))) (cdr expr `,@exprs #+end_src Bastien writes: > Hi Martin, > > Martin Halder writes: > >> this is fantastic, already love lisp, thanks a lot.. now I have exactly >> what I wanted.. additionally I needed the time format in industrial mode >> (1h = 100m = 100s), implemented in ihms. > > thanks for these functions -- I allowed myself to add them to > Worg/org-hacks.html, in a new "Times computation" section: > > http://orgmode.org/worg/org-hacks.html > > I added these functions I myself wrote for a particular purpose: > > #+begin_src emacs-lisp > (defun org-hh:mm:ss-string-to-seconds (s) > "Convert a string HH:MM:SS to a number of seconds." > (when (string-match "\\([0-9]+\\):\\([0-9]+\\):\\([0-9]+\\)" s) > (let ((hour (string-to-number (match-string 1 s))) > (min (string-to-number (match-string 2 s))) > (sec (string-to-number (match-string 3 s > (+ (* hour 3600) (* min 60) sec > > (defun org-subtract-hh:mm:ss-time (t1 t2) > "Substract two hh:mm:ss time values." > (let* ((sec (- (org-hh:mm:ss-string-to-seconds t2) >(org-hh:mm:ss-string-to-seconds t1))) >(hour (floor (/ sec 3600))) >(min (floor (/ (- sec (* 3600 hour)) 60))) >(secs (round (- sec (* 3600 hour) (* 60 min) > (format "%.2d:%.2d:%.2d" hour min secs))) > #+end_src > > With these function, you can subtract durations in a table like this: > > | Part |Begin | End | Duration | > |---+--+--+--| > | One | 00:00:00 | 00:01:11 | 00:01:11 | > | Two | 00:01:12 | 00:02:00 | 00:00:48 | > | Three | 00:02:05 | 00:16:06 | 00:14:01 | > #+TBLFM: $4='(org-subtract-hh:mm:ss-time $2 $3) > > Which was useful for me when I had to derush video files. > > HTH,
Re: [O] org table calc and lisp for hh:mm timetable
Hi Martin, Martin Halder writes: > this is fantastic, already love lisp, thanks a lot.. now I have exactly > what I wanted.. additionally I needed the time format in industrial mode > (1h = 100m = 100s), implemented in ihms. thanks for these functions -- I allowed myself to add them to Worg/org-hacks.html, in a new "Times computation" section: http://orgmode.org/worg/org-hacks.html I added these functions I myself wrote for a particular purpose: #+begin_src emacs-lisp (defun org-hh:mm:ss-string-to-seconds (s) "Convert a string HH:MM:SS to a number of seconds." (when (string-match "\\([0-9]+\\):\\([0-9]+\\):\\([0-9]+\\)" s) (let ((hour (string-to-number (match-string 1 s))) (min (string-to-number (match-string 2 s))) (sec (string-to-number (match-string 3 s (+ (* hour 3600) (* min 60) sec (defun org-subtract-hh:mm:ss-time (t1 t2) "Substract two hh:mm:ss time values." (let* ((sec (- (org-hh:mm:ss-string-to-seconds t2) (org-hh:mm:ss-string-to-seconds t1))) (hour (floor (/ sec 3600))) (min (floor (/ (- sec (* 3600 hour)) 60))) (secs (round (- sec (* 3600 hour) (* 60 min) (format "%.2d:%.2d:%.2d" hour min secs))) #+end_src With these function, you can subtract durations in a table like this: | Part |Begin | End | Duration | |---+--+--+--| | One | 00:00:00 | 00:01:11 | 00:01:11 | | Two | 00:01:12 | 00:02:00 | 00:00:48 | | Three | 00:02:05 | 00:16:06 | 00:14:01 | #+TBLFM: $4='(org-subtract-hh:mm:ss-time $2 $3) Which was useful for me when I had to derush video files. HTH, -- Bastien
Re: [O] org table calc and lisp for hh:mm timetable
Christian Moe writes: > Hi, > > This is ingenious! But I have a different solution that borrows > conversion functions from org-timer.el. [...] Your solution is perfect. Simple and leverages the built-in functions from org. Thanks for this. -- : Eric S Fraga (GnuPG: 0xC89193D8FFFCF67D) in Emacs 24.0.50.1 : using Org-mode version 7.5 (release_7.5.55.g87c42.dirty)
Re: [O] org table calc and lisp for hh:mm timetable
Hi Christian, this is fantastic, already love lisp, thanks a lot.. now I have exactly what I wanted.. additionally I needed the time format in industrial mode (1h = 100m = 100s), implemented in ihms. Thanks, Martin | Date | Start | Lunch | Back | End | Sum | Ind | |--+---+---+---+---+--+--| | [2011-03-01 Tue] | 8:00 | 12:00 | 12:30 | 18:15 | 9:45 | 9.75 | #+TBLFM: $6='(hms (+ (- (sec $5) (sec $4)) (- (sec $3) (sec $2::$7='(ihms (+ (- (sec $5) (sec $4)) (- (sec $3) (sec $2 (defun sec (arg) (if (string-match org-timer-re arg) (org-timer-hms-to-secs arg) (org-timer-hms-to-secs (concat arg ":00" (defun hms (s) (let (m h) (setq s (abs s) m (/ s 60) s (- s (* 60 m)) h (/ m 60) m (- m (* 60 h))) (format "%d:%02d" h m))) (defun ihms (s) (let (m h) (setq s (/ (* s 1) 3600) s (abs s) m (/ s 100) s (- s (* 100 m)) h (/ m 100) m (- m (* 100 h))) (format "%d.%02d" h m))) Am 15.03.2011 um 22:47 schrieb Christian Moe: > Hi, > > This is ingenious! But I have a different solution that borrows conversion > functions from org-timer.el. > > To avoid an insanely long formula, I'll alias those functions with shorter > names which don't seem to colide with anything in my Emacs. > > #+begin_src emacs-lisp > (defun sec (arg) >(org-timer-hms-to-secs arg)) > > (defun hms (arg) >(org-timer-secs-to-hms arg)) > #+end_src > > Now, just do this: > > |Start |Lunch | Back | End | Sum| > |--+--+--+--+-| > | 08:00:00 | 12:20:00 | 13:00:00 | 17:00:00 | 8:20:00 | > #+TBLFM: $5='(hms (+ (- (sec $4) (sec $3)) (- (sec $2) (sec $1 > > This already works for me, because my main interest here is in keeping track > of my jogging, but those seconds are spurious precision for your use case, > and take up space. So rewrite sec and hms: > > #+begin_src emacs-lisp > (defun sec (arg) >(if (string-match org-timer-re arg) >(org-timer-hms-to-secs arg) > (org-timer-hms-to-secs (concat arg ":00" > > (defun hms (arg) >(if (integerp (/ arg 60)) >(substring (org-timer-secs-to-hms arg) 0 -3) > (org-timer-secs-to-hms arg))) > #+end_src > > Now sec will correctly convert hh:mm stamps, too, and hms will convert to > hh:mm format if the argument is in whole minutes, otherwise to hh:mm:ss. So: > > | Start | Lunch | Back | End | Sum | > |---+---+---+---+--| > | 08:00 | 12:20 | 13:00 | 17:00 | 8:20 | > #+TBLFM: $5='(hms (+ (- (sec $4) (sec $3)) (- (sec $2) (sec $1
Re: [O] org table calc and lisp for hh:mm timetable
Hi, This is ingenious! But I have a different solution that borrows conversion functions from org-timer.el. To avoid an insanely long formula, I'll alias those functions with shorter names which don't seem to colide with anything in my Emacs. #+begin_src emacs-lisp (defun sec (arg) (org-timer-hms-to-secs arg)) (defun hms (arg) (org-timer-secs-to-hms arg)) #+end_src Now, just do this: |Start |Lunch | Back | End | Sum| |--+--+--+--+-| | 08:00:00 | 12:20:00 | 13:00:00 | 17:00:00 | 8:20:00 | #+TBLFM: $5='(hms (+ (- (sec $4) (sec $3)) (- (sec $2) (sec $1 This already works for me, because my main interest here is in keeping track of my jogging, but those seconds are spurious precision for your use case, and take up space. So rewrite sec and hms: #+begin_src emacs-lisp (defun sec (arg) (if (string-match org-timer-re arg) (org-timer-hms-to-secs arg) (org-timer-hms-to-secs (concat arg ":00" (defun hms (arg) (if (integerp (/ arg 60)) (substring (org-timer-secs-to-hms arg) 0 -3) (org-timer-secs-to-hms arg))) #+end_src Now sec will correctly convert hh:mm stamps, too, and hms will convert to hh:mm format if the argument is in whole minutes, otherwise to hh:mm:ss. So: | Start | Lunch | Back | End | Sum | |---+---+---+---+--| | 08:00 | 12:20 | 13:00 | 17:00 | 8:20 | #+TBLFM: $5='(hms (+ (- (sec $4) (sec $3)) (- (sec $2) (sec $1 Yours, Christian On 3/15/11 8:49 PM, Martin Halder wrote: I was trying to generate a simple table with time format "hh:mm" and auto calculate daily sum.. clocking working time was too much so I thought this would be easy but ended up with the following.. it works but is not beautiful (apply formula twice and same information multiple times) and I would like to get rid of the "hms", "hh" and "mm" columns and therefore call "hmconcat" directly somehow.. Any help is highly appreciated.. Thanks, Martin | Date | Start | Lunch | Back | End | Sum | hms | hh | mm | |--+---+---+---+---+---+---++| | [2011-03-01 Tue] | 08:00 | 12:20 | 13:00 | 17:00 | 08:20 | 8@ 20' 0" | 8 | 20 | #+TBLFM: $6='(hmconcat $8 $9)::$7=time(<2010-01-01 $5>)-time(<2010-01-01 $4>)+time(<2010-01-01 $3>)-time(<2010-01-01 $2>)::$8=hour($7)::$9=minute($7) (defun hmconcat (hh mm) (interactive) (if (> (length hh) 1) (setq temp (concat hh ":")) (setq temp (concat "0" hh ":"))) (if (> (length mm) 1) (concat temp mm) (concat temp "0" mm))) Martin, glad to see you got further with this! You can definitely get rid of hmconcat by using a combination of string-to-number and format (and I'm sure it's possible to get this done with simpler elisp): --8<---cut here---start->8--- | Date | Start | Lunch | Back | End | Sum | hms | hh | mm | |--+---+---+---+---+---+---++| | [2011-03-01 Tue] | 08:00 | 12:20 | 13:00 | 17:00 | 08:20 | 8@ 20' 0" | 8 | 20 | #+TBLFM: $6='(format "%02d:%02d" (string-to-number $8) (string-to-number $9))::$7=time(<2010-01-01 $5>)-time(<2010-01-01 $4>)+time(<2010-01-01 $3>)-time(<2010-01-01 $2>)::$8=hour($7)::$9=minute($7) --8<---cut here---end--->8--- Hi Eric, yes and thanks for the previous help, too.. the good old printf.. I would like to pass the result of time() directly to a lisp function, like: #+TBLFM: $6='(coolfunc (time(...$5)-time(...$4))) If I would know how to pass the result, eg as a string, to a lisp function I could sort it out, I guess. Thanks, Martin
Re: [O] org table calc and lisp for hh:mm timetable
Martin Halder writes: [...] > Hi Eric, > > yes and thanks for the previous help, too.. the good old printf.. I > would like to pass the result of time() directly to a lisp function, > like: > #+TBLFM: $6='(coolfunc (time(...$5)-time(...$4))) > > If I would know how to pass the result, eg as a string, to a lisp > function I could sort it out, I guess. Hopefully somebody else can jump in; I have no idea how and where the time functions are evaluated. Looking at the org-table.el code hasn't made me any wiser unfortunately and I don't quite understand the link between that code and the emacs calc package... -- : Eric S Fraga (GnuPG: 0xC89193D8FFFCF67D) in Emacs 24.0.50.1 : using Org-mode version 7.5 (release_7.5.52.g0dc16.dirty)
Re: [O] org table calc and lisp for hh:mm timetable
>> I was trying to generate a simple table with time format "hh:mm" and >> auto calculate daily sum.. clocking working time was too much so I >> thought this would be easy but ended up with the following.. it works >> but is not beautiful (apply formula twice and same information >> multiple times) and I would like to get rid of the "hms", "hh" and >> "mm" columns and therefore call "hmconcat" directly somehow.. Any help >> is highly appreciated.. >> >> Thanks, >> Martin >> >> | Date | Start | Lunch | Back | End | Sum | hms | hh >> | mm | >> |--+---+---+---+---+---+---++| >> | [2011-03-01 Tue] | 08:00 | 12:20 | 13:00 | 17:00 | 08:20 | 8@ 20' 0" | 8 >> | 20 | >> #+TBLFM: $6='(hmconcat $8 $9)::$7=time(<2010-01-01 $5>)-time(<2010-01-01 >> $4>)+time(<2010-01-01 $3>)-time(<2010-01-01 $2>)::$8=hour($7)::$9=minute($7) >> >> (defun hmconcat (hh mm) (interactive) >>(if (> (length hh) 1) >>(setq temp (concat hh ":")) (setq temp (concat "0" hh ":"))) >>(if (> (length mm) 1) >>(concat temp mm) (concat temp "0" mm))) > > Martin, > > glad to see you got further with this! > > You can definitely get rid of hmconcat by using a combination of > string-to-number and format (and I'm sure it's possible to get this done > with simpler elisp): > > --8<---cut here---start->8--- > > | Date | Start | Lunch | Back | End | Sum | hms | hh | > mm | > |--+---+---+---+---+---+---++| > | [2011-03-01 Tue] | 08:00 | 12:20 | 13:00 | 17:00 | 08:20 | 8@ 20' 0" | 8 | > 20 | > #+TBLFM: $6='(format "%02d:%02d" (string-to-number $8) (string-to-number > $9))::$7=time(<2010-01-01 $5>)-time(<2010-01-01 $4>)+time(<2010-01-01 > $3>)-time(<2010-01-01 $2>)::$8=hour($7)::$9=minute($7) > --8<---cut here---end--->8--- Hi Eric, yes and thanks for the previous help, too.. the good old printf.. I would like to pass the result of time() directly to a lisp function, like: #+TBLFM: $6='(coolfunc (time(...$5)-time(...$4))) If I would know how to pass the result, eg as a string, to a lisp function I could sort it out, I guess. Thanks, Martin
Re: [O] org table calc and lisp for hh:mm timetable
Martin Halder writes: > Hi all, > > some words of warning: this was written by an Emacs, Org, Lisp and > Calc newbie.. and congratulations on converting a vim user btw, org > mode is great : ) > > I was trying to generate a simple table with time format "hh:mm" and > auto calculate daily sum.. clocking working time was too much so I > thought this would be easy but ended up with the following.. it works > but is not beautiful (apply formula twice and same information > multiple times) and I would like to get rid of the "hms", "hh" and > "mm" columns and therefore call "hmconcat" directly somehow.. Any help > is highly appreciated.. > > Thanks, > Martin > > | Date | Start | Lunch | Back | End | Sum | hms | hh | > mm | > |--+---+---+---+---+---+---++| > | [2011-03-01 Tue] | 08:00 | 12:20 | 13:00 | 17:00 | 08:20 | 8@ 20' 0" | 8 | > 20 | > #+TBLFM: $6='(hmconcat $8 $9)::$7=time(<2010-01-01 $5>)-time(<2010-01-01 > $4>)+time(<2010-01-01 $3>)-time(<2010-01-01 $2>)::$8=hour($7)::$9=minute($7) > > (defun hmconcat (hh mm) (interactive) > (if (> (length hh) 1) > (setq temp (concat hh ":")) (setq temp (concat "0" hh ":"))) > (if (> (length mm) 1) > (concat temp mm) (concat temp "0" mm))) Martin, glad to see you got further with this! You can definitely get rid of hmconcat by using a combination of string-to-number and format (and I'm sure it's possible to get this done with simpler elisp): --8<---cut here---start->8--- | Date | Start | Lunch | Back | End | Sum | hms | hh | mm | |--+---+---+---+---+---+---++| | [2011-03-01 Tue] | 08:00 | 12:20 | 13:00 | 17:00 | 08:20 | 8@ 20' 0" | 8 | 20 | #+TBLFM: $6='(format "%02d:%02d" (string-to-number $8) (string-to-number $9))::$7=time(<2010-01-01 $5>)-time(<2010-01-01 $4>)+time(<2010-01-01 $3>)-time(<2010-01-01 $2>)::$8=hour($7)::$9=minute($7) --8<---cut here---end--->8--- -- : Eric S Fraga (GnuPG: 0xC89193D8FFFCF67D) in Emacs 24.0.50.1 : using Org-mode version 7.5 (release_7.5.52.g0dc16.dirty)
[O] org table calc and lisp for hh:mm timetable
Hi all, some words of warning: this was written by an Emacs, Org, Lisp and Calc newbie.. and congratulations on converting a vim user btw, org mode is great : ) I was trying to generate a simple table with time format "hh:mm" and auto calculate daily sum.. clocking working time was too much so I thought this would be easy but ended up with the following.. it works but is not beautiful (apply formula twice and same information multiple times) and I would like to get rid of the "hms", "hh" and "mm" columns and therefore call "hmconcat" directly somehow.. Any help is highly appreciated.. Thanks, Martin | Date | Start | Lunch | Back | End | Sum | hms | hh | mm | |--+---+---+---+---+---+---++| | [2011-03-01 Tue] | 08:00 | 12:20 | 13:00 | 17:00 | 08:20 | 8@ 20' 0" | 8 | 20 | #+TBLFM: $6='(hmconcat $8 $9)::$7=time(<2010-01-01 $5>)-time(<2010-01-01 $4>)+time(<2010-01-01 $3>)-time(<2010-01-01 $2>)::$8=hour($7)::$9=minute($7) (defun hmconcat (hh mm) (interactive) (if (> (length hh) 1) (setq temp (concat hh ":")) (setq temp (concat "0" hh ":"))) (if (> (length mm) 1) (concat temp mm) (concat temp "0" mm)))
