using date-time in emacs spreadsheets

Question

I'm just starting to use ses-mode in emacs, and I plan to use it with timestamps, but I do not manage to have them parsed in a way that I can then use.

I'm taking measurements on three days of the week, so my distances between one measurement and the other is either 2 or 3 days. I chose to use ses-mode in emacs because it runs on all of my computers, including the phone.

my spreadsheet contains datestamp, conductivity, temperature, and gallon count, a couple of subsequent lines would look like this:

2014-10-03  2.95  33.4  4031070
2014-10-06  3.07  33.5  4086930
2014-10-08  2.97  33.6  4119590

I would add two more columns, the first with the difference of days between the readings, the second with the "gallon-per-day" value.

I do not manage to have the string timestamp parsed into a format where I can do computations, staying within a simple emacs spreadsheet (SES).

I've tried date-to-time, but it always returns the same value (14445 17280).

parse-time-string gives me a 9-tuple which I can't directly pass to format-time-string.

SES *is* cool, but I concluded recently that using org-mode for spreadsheets in Emacs was almost certainly the better way to go nowadays. If you've not investigated that option yet, you might find it interesting/useful. — phils, Oct 15 '14 at 11:02

Tobias · Accepted Answer · 2014-10-14T11:53:51.793

The function encode-time helps:

(let ((l (parse-time-string "2014-09-12")))
  (format-time-string "%d %m %Y" (encode-time 0 0 0 (nth 3 l) (nth 4 l) (nth 5 l))))

The following version uses cl-flet to avoid doubling of code if the encoding is needed multiple times. If you need the encoding also in other functions you can use defun instead of cl-flet.

(eval-when (compile) (require 'cl)) ;; for cl-flet
(let ((A2 "2014-10-08")  ;; just for testing
      (A1 "2014-10-03")) ;; just for testing
  (cl-flet ((encode (str)
              (let ((l (parse-time-string str)))
                (encode-time 0 0 0 (nth 3 l) (nth 4 l) (nth 5 l)))))
    (let* ((t-prev (encode A1))
           (t-this (encode A2)))
      (/ (time-to-seconds (time-subtract t-this t-prev)) (* 24 60 60)))))

As a function:

(eval-when (compile) (require 'cl)) ;; for cl-flet

(defun day-diff (date1 date2)
  "Calculate the difference of dates  in days between DATE1-STR and DATE2-STR."
  (interactive "sDate1:\nsDate2:")
  (cl-flet ((encode (str)
            (let ((l (parse-time-string str)))
              (encode-time 0 0 0 (nth 3 l) (nth 4 l) (nth 5 l)))))
    (setq date1 (encode date1)
      date2 (encode date2))
    (let ((ret (/ (time-to-seconds (time-subtract date1 date2)) (* 24 60 60))))
      (when (called-interactively-p 'any)
    (message "Day difference: %s" ret))
      ret)))

(put 'day-diff 'safe-function t)

An alternative using calc would be:

(require 'calc)
(defun day-diff (date1 date2)
  "Calculate the difference of dates  in days between DATE1-STR and DATE2-STR."
  (interactive "sDate1:\nsDate2:")
  (let ((ret (string-to-number (calc-eval (format "<%s>-<%s>" date1 date2)))))
    (when (called-interactively-p 'any)
      (message "Day difference: %s" ret))
    ret))

If you omit the nice-to-have features this becomes almost a simple cell formula: (string-to-number (calc-eval (format "<%s>-<%s>" A1 A2))).

If you want to save the stuff in the spreadsheet you can put the defun in table cell A1. A more simple example:

(progn (defun day-diff (date1 date2) (string-to-number (calc-eval (format "<%s>-<%s>" date1 date2)))) (put 'day 'safe-function t) "Actual header")

To have a more convenient editing possibility you can switch to M-x lisp-mode. There you find

^L
(ses-cell A1 "Actual Header" (progn (defun day-diff (date1 date2) (string-to-number (calc-eval (format "<%s>-<%s>" date1 date2)))) (put 'day 'safe-function t) "Actual header") nil nil)

which you can edit. But do not insert linebreaks! ses identifies cell-positions with line numbers in that file!

Another nice alternative is to put the definition of your function into the file-local variable list. Switch to lisp-interaction mode by M-x lisp-interaction-mode. Go to the end of the file. There you find the lines:

;; Local Variables:
;; mode: ses
;; End:

Add your function definition as eval to this list:

;; Local Variables:
;; mode: ses
;; eval:
;; (progn
;;   (defun day-diff (date1 date2)
;;     (string-to-number (calc-eval (format "<%s>-<%s>" date1 date2))))
;;   (put 'day-diff 'safe-function t))
;; End:

You can add the progn without the comment characters ;. In this case even indentation works. Afterwards you can call comment-region for the progn.

You can save the file and run M-x normal-mode. Afterwards the function is defined and you can use it in the spreadsheet.

it does help, but what I made with it `(let* ((l-prev (parse-time-string A1)) (l-this (parse-time-string A2)) (t-prev (encode-time 0 0 0 (nth 3 l-prev) (nth 4 l-prev) (nth 5 l-prev))) (t-this (encode-time 0 0 0 (nth 3 l-this) (nth 4 l-this) (nth 5 l-this)))) (/ (time-to-seconds (time-subtract t-this t-prev)) (* 24 60 60)))` looks terrible! — mariotomo, Oct 08 '14 at 19:22
with your edit it looks already better, but then comes the `ses` part: how do I define the function in the spreadsheet, so I don't need repeat it in every cell? — mariotomo, Oct 08 '14 at 21:14

using date-time in emacs spreadsheets

1 Answers1