Interactive Time Lines

I was trying to estimate the date/time for undated events in an 18th century travel diary. Realizing it would help to use OOCalc for date time math such as daily totals and speed estimates, I set up a spreadsheet for the purpose. As I worked on the spreadsheet I thought it would be great to see a time line graph to check my work for errors. Searching around it became clear that none of the built-in graphing functions in spreadsheets would work for creating a time line graphic. It appears that most people simply use drawing tools to make time lines.

Then I found the Simile Widgets Timeline component a versatile JavaScript based solution. Soon after I got a time line going it became clear that keeping the data file in sync with the changing spreadsheet data was cumbersome. With all the data already in OOCalc I decided it would be nice to output formatted data directly from the spreadsheet for use in the timeline.

With my success in creating a spreadsheet for this one project I decided to create a generic version of the spreadsheet that covers all time line attributes. The spreadsheet contains a final version of my custom Calc functions for time line JSON data creation that handles all time line data options (download the spreadsheet here). To test out my new OOCalc functions I created a time line of English and British Monarchs (also useful for my research) starting with my spreadsheet sample. I haven’t finished adding text excerpts to the monarchs time line but it does have pictures and Wikipedia links for all items.

Last month while organizing notes from a tour of historic sites I found I had not recorded the dates and times in the notes. As I was giving myself a dope slap for failing to record the times it dawned on me that the photos I took would give me the missing time information. While I was viewing the photos to get the times from the EXIF data it occurred to me that I could set up nearly automatic time line generation using my spreadsheet and the command line ExifTool.

To start you open a command prompt in the directory with your photos and then run the following command line.

"C:Program FilesEXIFtoolexiftool.exe" -p "$filename, $createdate" -q -f -d "%Y/%m/%d %H:%M:%S" . >PhotoTimes.csv

This creates a CSV data file containing the image filename and creation date that can be opened by OOCalc. Using the spreadsheet you generate a JSON data file to give you a time line as shown in these screenshots.



I’ve put a zip archive with the files used to create the photo time line on my site. Download the archive from this link.

The archive contains these files for the Photographs Time Line:

PhotoTimes.csv output from the ExifTool run
PhotoTimeline.ods spreadsheet for creating the JSON data file
PhotoTimes.js the JSON data file
PhotoTimes.html HTML page for displaying the time line
PhotoTimes.css CSS for better control of image size

See Also:

Simile Widgets Timeline documentation

2 Replies to “Interactive Time Lines”

  1. You may be able to skip the spreadsheet step by writing JSON directly from exiftool. (using either the -json or -p option).


  2. Thanks for the tip Phil I was using an older version of ExifTool without the JSON output option. I’ve updated now it looks like I should be able to script the whole file generation, cool.

    For others who stumble across this, Phil is the author of ExifTool the great Perl library and command line app for EXIF information that I mentioned in the article.


Comments are closed.