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

[Christian Moe <mail@christianmoe.com>]

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
>