Skip to content

Instantly share code, notes, and snippets.

@lowjoel
Last active July 13, 2019 10:57
Show Gist options
  • Save lowjoel/7f8612a2b945d15eb96e to your computer and use it in GitHub Desktop.
Save lowjoel/7f8612a2b945d15eb96e to your computer and use it in GitHub Desktop.
Migrates SleepBot data to Sleep as Android's format
require 'csv'
# Parses CSV-exported data from SleepBot
def parse_export(row)
# Parse the wake up date
date_to = row[0].split('/')
date_to = Time.new(2000 + date_to[2].to_i, date_to[1], date_to[0])
# Parse the wake up/sleep times
time_from = row[1].split(':')
time_to = row[2].split(':')
time_from = Time.new(1970, 1, 1, time_from[0], time_from[1])
time_to = Time.new(1970, 1, 1, time_to[0], time_to[1])
# Check whether we advanced a day. Our start date is one day before in that case.
is_from_prev_day = time_to < time_from
# Construct the sleep date/time and wake up date/time
time_to = Time.new(date_to.year, date_to.mon, date_to.day, time_to.hour, time_to.min)
time_from = Time.new(date_to.year, date_to.mon, date_to.day, time_from.hour, time_from.min)
time_from -= 1 * 24 * 60 * 60 if is_from_prev_day
hours = row[3].to_f
notes = row[4]
[time_from, time_to, hours, 0, notes]
end
# Parses raw SQLite database dumps from the SleepBot app.
def parse_raw(row)
# Java timestamps: Unix timestamps, with ns resolution
timestamp_from = row[1].to_f / 1000.0
timestamp_to = row[2].to_f / 1000.0
hours = (timestamp_to - timestamp_from) / 60 / 60
notes = row[3]
rating = row[10]
[Time.at(timestamp_from), Time.at(timestamp_to), hours, rating.to_i, notes || '']
end
# Formates Date/Time objects in Sleep as Android format
def sleepandroid_time(time)
'%.2d. %.2d. %d %d:%.2d' % [time.day, time.mon, time.year, time.hour, time.min]
end
results = []
parser = :parse_export
# Load the import file.
CSV.foreach('sleepbot-raw.csv') do |row|
date_to = row[0]
# Detect the type of the file we are importing.
parser = :parse_raw if date_to == '_id'
next if date_to == 'Date' || date_to == '_id'
a = send(parser, row)
puts a.inspect
results << a
end
# Write the converted file in the Sleep as Android format.
CSV.open('sleepbot-converted.csv', 'wb') do |csv|
results.each do |result|
csv << ['Id', 'Tz', 'From', 'To', 'Sched', 'Hours', 'Rating', 'Comment', 'Framerate', 'Snore', 'Noise', 'Cycles', 'DeepSleep', 'LenAdjust', 'Geo', '%d:%d' % [result[1].hour, result[1].min], 'Event', 'Event']
csv << [result[0].to_i * 1000, 'Asia/Singapore', sleepandroid_time(result[0]), sleepandroid_time(result[1]), sleepandroid_time(result[1]), result[2], result[3], result[4], 10000, -1, -1, -1, -1, 0, '', 0, 'DEEP_START-' + (result[0].to_i * 1000).to_s, 'DEEP_END-' + (result[0].to_i * 1000).to_s]
end
end
@zenware
Copy link

zenware commented Oct 11, 2014

This is a nice insight to the difference in the data formats. If you need help adding the activity conversions I'd be happy to break out some of my old reverse engineering skills.

@T3chArmy
Copy link

I forked this and updated it and also added the steps I had to go through to get it to work correctly ... feel free to take my changes and fix up this script!

@ticstyle
Copy link

can't get this to work, it converts the data and makes a file but its not correctly formated so i cant import it

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment