Reconstruct a sleep diary

A lot of programs remember when recent events happened. For example, your calendar saves the start and end times for entries; and your browser cache stores the time you visited each page. You may be able to reconstruct a sleep diary by gathering up these records and assuming you were asleep during the periods where nothing happened.

Every program that logs your activity has its own bespoke format, designed around the requirements of that program. And for privacy and security reasons, access to the data is often limited. That means reconstructing a diary is quite technical and often inaccurate, but it can be a quick way to see your past sleeping pattern. See the diary creation page for information about creating a proper diary for future events.

A collection of example sources are presented below, but your best source depends on your personal behaviour. For example, if you're always on your Android phone, Google Takeoutopen in new window will let you download a lot of data about your activity.

General overview

To reconstruct a diary, you need to create an activity log that will then be converted to a normal diary. This is a simple text file that looks something like:

maximum_day_length_ms=129600000
ActivityStart,ActivityEnd
2016-08-04T02:01:00Z,2016-08-04T04:08:16Z
12345678,23456789
... etc ...

The first line is optional, and tells the analysis program how many milliseconds there are in the longest possible day. A 24-hour day is 86400000 milliseconds, and you should only need to set this explicitly if your average day length is more than about 32 hours long.

The rest of the file uses CSVopen in new window - a type of simple spreadsheet with commas between each column.

The second line is the CSV header. It says this file has two columns, indicating the start and end times for an activity.

Lines after the second are the CSV body. They indicate the times for specific activities. Each line should have a start time in the first column and an end in the second column. If your data source only specifies one time, use that time in both columns. The example above shows dates in ISO 8601open in new window format and in Unix timeopen in new window, but analysis programs should support a wide variety of date formats.

The way to generate this data depends on your data source. The examples below discuss some possible solutions, but you may need to do some programming to make your particular source work.

The examples on this page use the dashboardopen in new window to analyse your data. See the ActivityLog documentationopen in new window if you're interested in creating your own analysis software.

Calendars

Calendars store the dates and times when events occur. If you normally have events throughout your day, you can use them as an activity log. The general process looks like this:

export your data to iCalendar format
analyse your data

Most calendar software can export to iCalendar formatopen in new window - a standard format designed to be read by other calendaring programs. You will need to search online to find the relevant steps for your program.

Once you have an iCalendar file, you can add it directly to the dashboardopen in new window. It will convert the file to an activity log automatically.

Limitations

A reconstructed sleep diary will always produce lower-quality data than one created deliberately. Here are some specific issues people often have with calendars:

Calendars can't tell the difference between being asleep and having uscheduled time. For example, if your last meeting ended at 6pm, the program will assume you went to sleep the moment the meeting was over.

Calendars can only record when something was supposed to happen. If you slept through your 8am meeting, a diary will still think you were awake at that time.

As with all reconstructed diaries, a calendar can give you a general idea of your sleeping pattern, but you'll need to create a proper diary to get high quality data.

Desktop browsers

Browsers store the date and time whenever you go to a new page, so they can decide when to remove that page from the browser cache. Depending on your settings, this information might be deleted after a month or so. Most browsers store this in a standard format that can be extracted with a little work. The general process looks like this:

find your history database
(optionally) extract the cache activity
analyse your data

The first step is to find your history database. Type chrome:version or about:support in the address bar - depending on your browser, one or other of them should take you to a page full of technical information. The line that says Profile Path or Profile Directory tells you the folder where your profile is saved. Depending on your browser, your history database will be called History, History.db or places.sqlite in that folder. If you are using MacOS and can't see your profile folder, press Cmd + Shift + G in the Finder and type your folder name.

The dashboardopen in new window can create an activity log directly from your history database. You can skip the next stage if you're comfortable with that.

Manually create an activity log (optional)

If you would rather not upload your complete browser history to the dashboardopen in new window, you can extract your activity log by hand with a command-line program called SQLiteopen in new window. This is installed by default in MacOS and available for all Linux distributions, but you will have to install it manually if you use Windows. Once you've installed SQLite, run one of these commands on a command-line (remember to replace ... with the folder from the first step):

# Run whichever command matches your history filename:
sqlite3 -csv C:\\...\\History       '.output activity-log.chrome.csv'  '.header on' 'SELECT visit_time/1000-11644473600000 AS ActivityStart, visit_time/1000-11644473600000 AS ActivityEnd FROM visits'
sqlite3 -csv C:\\...\\places.sqlite '.output activity-log.firefox.csv' '.header on' 'SELECT visit_date/1000                AS ActivityStart, visit_date/1000                AS ActivityEnd FROM moz_historyvisits'
sqlite3 -csv C:\\...\\History.db    '.output activity-log.safari.csv'  '.header on' 'SELECT (visit_time+978307200)*1000    AS ActivityStart, (visit_time+978307200)*1000    AS ActivityEnd FROM history_visits'

If you see a message like Error: database is locked, you will need to close your browser before you run the command.

Depending on your browser, your file will be called activity-log.chrome.csv, activity-log.firefox.csv or activity-log.safari.csv. You can confirm its contents by opening it like a normal spreadsheet.

Finally, add your activity log or history database to the dashboardopen in new window. You might like to convert the result to a spreadsheet, so you can fix anything it got wrong.

Limitations

A reconstructed sleep diary will always produce lower-quality data than one created deliberately. Here are some specific issues people often have with browsers:

Browsers can't tell the difference between being asleep and not navigating to new pages. For example, if you go to Netflix and put a movie on while you fall asleep, the program will assume you fell asleep the moment the page loaded.

Browsers also can't tell the difference between being awake and navigating to new pages. For example, if you go to YouTube and start a playlist of sleeping music, the program will assume you were awake all night clicking through to the next video.

As with all reconstructed diaries, a browser can give you a general idea of your sleeping pattern, but you'll need to create a proper diary to get high quality data.

Desktop operating systems

Your operating system logs a lot of system information, like when it boots up and shuts down. If you turn your computer on when you wake up and off when you go to sleep, you can use that information as an activity log. The general process looks like this:

run a command-line program to create an activity log
analyse your data

If you use Windows, you can create an activity log with PowerShell. Click Start, type PowerShell and press enter. Copy the following block of text, then right-click on the PowerShell window and click paste:

if ( $out_path = [System.Environment]::GetFolderPath([System.Environment+SpecialFolder]::Desktop)+"\\activity-log.windows.csv" ) {
  write "Saving to $out_path..."
  write "ActivityStart,ActivityEnd" | Out-File -encoding ASCII -FilePath $out_path
  ForEach ( $log in Get-EventLog System ) {
    if ( $log.EventId -eq 12 ) { # powered on
       $start_time = Get-Date -Format u $log.TimeGenerated
    } elseif ( $log.EventId -eq 13 ) { # powered off
       $end_time = Get-Date -Format u $log.TimeGenerated
       write "$start_time,$end_time" | Out-File -encoding ASCII -append -FilePath $out_path
    }
  }
  write "You can close PowerShell now."
}
# Now press enter twice

Press enter twice to run the command. After a few seconds, a file called activity-log.windows.csv will appear on your desktop. You can close PowerShell once that happens.

If you use Linux, run the following command on a command-line:

echo 'ActivityStart,ActivityEnd' > ~/activity-log.linux.csv
{
  echo -n "Getting dates from log files..." >&2
  sudo zcat -f /var/log/messages* /var/log/syslog*
  echo " done" >&2
  echo -n "Getting dates from system log..." >&2
  sudo journalctl --no-pager --system
  echo " done" >&2
  echo -n "Getting dates from user log..." >&2
  sudo journalctl --no-pager --user
  echo " done" >&2
} \
  | grep -a '^[A-Z]' \
  | cut -c 1-12 \
  | sort -u \
  | while read REPLY
    do DATE="$( date -Iseconds -d "$REPLY:00" )"; echo "$DATE,$DATE"
    done \
  | tee -a ~/activity-log.linux.csv \
  | while read REPLY
    do
        echo "$REPLY"
        for (( N=0; N!=100; ++N)); do read; done
    done

A file called activity-log.linux.csv will slowly be populated in your home directory. The program might take a minute or two to run.

Limitations

A reconstructed sleep diary will always produce lower-quality data than one created deliberately. Here are some specific issues people often have with desktop logs:

Desktop logs can't tell the difference between being asleep and being offline. For example, if you turn your laptop off at 8pm, the program will assume you went to sleep at the same time as it did.

Desktop logs also can't tell the difference between being awake and being in use. For example, if Windows Update keeps your computer awake until 3am, the program will assume you stayed awake until 3am as well.

As with all reconstructed diaries, a desktop log can give you a general idea of your sleeping pattern, but you'll need to create a proper diary to get high quality data.

Other sources

The examples above show how to extract an activity log from some sources. It generally involves extracting the data you want from the source, converting it to an activity log, then analysing it with the diary.

If you work out how to extract data from another source, let us knowopen in new window so we can add it to this page!

# Reconstruct a sleep diary

# General overview

# Calendars

# Limitations

# Desktop browsers

# Manually create an activity log (optional)

# Limitations

# Desktop operating systems

# Limitations

# Other sources

Reconstruct a sleep diary

General overview

Calendars

Limitations

Desktop browsers

Manually create an activity log (optional)

Limitations

Desktop operating systems

Limitations

Other sources