org table calc and lisp for hh:mm timetable

org table calc and lisp for hh:mm timetable

[Martin Halder]

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)))

Re: org table calc and lisp for hh:mm timetable

[Eric S Fraga]

Martin Halder <martin.halder@gmail.com> 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)

Re: org table calc and lisp for hh:mm timetable

[Martin Halder]

>> 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: org table calc and lisp for hh:mm timetable

[Eric S Fraga]

Martin Halder <martin.halder@gmail.com> 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: org table calc and lisp for hh:mm timetable

[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))))


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: org table calc and lisp for hh:mm timetable

[Martin Halder]

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 10000) 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: org table calc and lisp for hh:mm timetable

[Eric S Fraga]

Christian Moe <mail@christianmoe.com> 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: org table calc and lisp for hh:mm timetable

[Bastien]

Hi Martin,

Martin Halder <martin.halder@gmail.com> 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: org table calc and lisp for hh:mm timetable

[Eric Schulte]

[-- Attachment #1: Type: text/plain, Size: 463 bytes --]

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


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: time.org --]
[-- Type: text/org, Size: 2000 bytes --]

|  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

[-- Attachment #3: Type: text/plain, Size: 1733 bytes --]


Bastien <bzg@altern.org> writes:

> Hi Martin,
>
> Martin Halder <martin.halder@gmail.com> 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: org table calc and lisp for hh:mm timetable

[Eric Schulte]

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 <bzg@altern.org> writes:

> Hi Martin,
>
> Martin Halder <martin.halder@gmail.com> 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: org table calc and lisp for hh:mm timetable

[Eric S Fraga]

"Eric Schulte" <schulte.eric@gmail.com> 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: org table calc and lisp for hh:mm timetable

[Christian Moe]

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<bzg@altern.org>  writes:
>
>> Hi Martin,
>>
>> Martin Halder<martin.halder@gmail.com>  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: org table calc and lisp for hh:mm timetable

[Eric Schulte]

Christian Moe <mail@christianmoe.com> 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<bzg@altern.org>  writes:
>>
>>> Hi Martin,
>>>
>>> Martin Halder<martin.halder@gmail.com>  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: org table calc and lisp for hh:mm timetable

[Eric Schulte]

[-- Attachment #1: Type: text/plain, Size: 1500 bytes --]

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


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: 0001-org-table-convert-times-to-integers-on-table-formula.patch --]
[-- Type: text/x-diff, Size: 2242 bytes --]

From 76b416013ee4c9a492c8ddced57727215165c298 Mon Sep 17 00:00:00 2001
From: Eric Schulte <schulte.eric@gmail.com>
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: org table calc and lisp for hh:mm timetable

[Christian Moe]

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: org table calc and lisp for hh:mm timetable

[Carsten Dominik]

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 <schulte.eric@gmail.com>
> 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: org table calc and lisp for hh:mm timetable

[Eric Schulte]

Christian Moe <mail@christianmoe.com> 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: org table calc and lisp for hh:mm timetable

[Martin Halder]

>> 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: org table calc and lisp for hh:mm timetable

[Bastien]

Hi all,

Carsten Dominik <carsten.dominik@gmail.com> 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