Export/Import TimeSpans as csv/json

[jmattheis]

No description provided.

[Shahin-rmz]

Hi, is it implemented?
I think yes,
then where is documentation?
Or I should use graphql?
Thanks :))

[jmattheis]

Not implemented, you've to use the graphql api.

[brandon1024]

Would love to see this implemented some day. This is the main feature holding me back from making the plunge from Toggle.

[microSoftware]

Same ! Love Traggo but a time tracker where you can't export is useless. It basically means you put all the effort tracking your time but you can do nothing with all this data.

[jmattheis]

The data is still yours, and you can export the data directly from the sqlite database with a query like this:

SELECT u.name,
       start_utc,
       end_utc,
       group_concat(key || ':' || string_value, ' ') AS tags
FROM time_spans ts
JOIN time_span_tags tst ON ts.id = tst.time_span_id
JOIN users u ON u.id = ts.user_id
GROUP BY time_span_id

$ sqlite3 data.db -header -csv "select u.name, start_utc, end_utc, group_concat(key || ':' || string_value, ' ') as tags from time_spans ts join time_span_tags tst on ts.id = tst.time_span_id join users u on u.id = ts.user_id group by time_span_id"
name,start_utc,end_utc,tags
admin,"2024-01-01 00:00:00+00:00","2024-01-01 00:09:00+00:00","proj:traggo type:review issue:747"
admin,"2024-01-01 00:13:00+00:00","2024-01-01 02:17:00+00:00","proj:gotify type:review issue:676"
admin,"2024-01-01 02:29:00+00:00","2024-01-01 04:07:00+00:00","proj:gotify type:review issue:676"
admin,"2024-01-01 04:18:00+00:00","2024-01-01 06:22:00+00:00","proj:gotify type:review issue:676"
admin,"2024-01-01 06:34:00+00:00","2024-01-01 08:58:00+00:00","proj:gotify type:review issue:676"
admin,"2024-01-01 09:09:00+00:00","2024-01-01 10:21:00+00:00","type:support with:nicories"
admin,"2024-01-01 10:27:00+00:00","2024-01-01 11:24:00+00:00","proj:traggo type:work issue:747"
admin,"2024-01-01 11:39:00+00:00","2024-01-01 12:09:00+00:00","proj:gotify type:work issue:676"
admin,"2024-01-01 12:21:00+00:00","2024-01-01 14:34:00+00:00","proj:traggo type:work issue:747"

[brandon1024]

Forcing users to query the database manually to generate a CSV export is kinda brutal 😆 Wouldn't it be pretty straightforward to wrap this in a simple API?

[jmattheis]

Yes, but it depends on what kind of customization the api should support. I could imagine that some users want the tag as separate columns and not as just text.

[brandon1024]

I don't think we need anything fancy in the beginning. Even the simplest API (start/end date as query params) would suffice for now. Additional features can come after that 🙂

[microSoftware]

The data is still yours, and you can export the data directly from the sqlite database with a query like this:

SELECT u.name,
       start_utc,
       end_utc,
       group_concat(key || ':' || string_value, ' ') AS tags
FROM time_spans ts
JOIN time_span_tags tst ON ts.id = tst.time_span_id
JOIN users u ON u.id = ts.user_id
GROUP BY time_span_id

$ sqlite3 data.db -header -csv "select u.name, start_utc, end_utc, group_concat(key || ':' || string_value, ' ') as tags from time_spans ts join time_span_tags tst on ts.id = tst.time_span_id join users u on u.id = ts.user_id group by time_span_id"
name,start_utc,end_utc,tags
admin,"2024-01-01 00:00:00+00:00","2024-01-01 00:09:00+00:00","proj:traggo type:review issue:747"
admin,"2024-01-01 00:13:00+00:00","2024-01-01 02:17:00+00:00","proj:gotify type:review issue:676"
admin,"2024-01-01 02:29:00+00:00","2024-01-01 04:07:00+00:00","proj:gotify type:review issue:676"
admin,"2024-01-01 04:18:00+00:00","2024-01-01 06:22:00+00:00","proj:gotify type:review issue:676"
admin,"2024-01-01 06:34:00+00:00","2024-01-01 08:58:00+00:00","proj:gotify type:review issue:676"
admin,"2024-01-01 09:09:00+00:00","2024-01-01 10:21:00+00:00","type:support with:nicories"
admin,"2024-01-01 10:27:00+00:00","2024-01-01 11:24:00+00:00","proj:traggo type:work issue:747"
admin,"2024-01-01 11:39:00+00:00","2024-01-01 12:09:00+00:00","proj:gotify type:work issue:676"
admin,"2024-01-01 12:21:00+00:00","2024-01-01 14:34:00+00:00","proj:traggo type:work issue:747"

thanks that'll do it for me. Else, i'm sure there is a library already out there to export data into csv from a sqlite database.

[b1tray3r]

I don't think we need anything fancy in the beginning. Even the simplest API (start/end date as query params) would suffice for now. Additional features can come after that 🙂

Totally agreeing with this statement. The resulting output can be parsed further.
Would love to see JSON, but CSV is fine too. :)

[b1tray3r]

Also:

Don't forget the note column:

SELECT u.name,
       start_utc,
       end_utc,
       group_concat(key || ':' || string_value, ' ') AS tags,
       note
FROM time_spans ts
JOIN time_span_tags tst ON ts.id = tst.time_span_id
JOIN users u ON u.id = ts.user_id
GROUP BY time_span_id

[jmattheis]

Would love to see JSON, but CSV is fine too. :)

You can use -json instead of -csv in the sqlite3 command to output json.