[Bug]: Analytics app doesn't display aggregate data with SQLite backend

[Gotfrid]

Guidelines

• I agree to follow this project's Contributing Guidelines.

Project Version

0.2.0, github:main

Platform and OS Version

macOS 14.5, Ubuntu 22.04.4 LTS (docker container)

Existing Issues

No response

What happened?

When using SQLite as a backend to store telemetry data, analytics app that comes with shiny.telemetry won't show values correctly - all aggregates show 0 counts.

Steps to reproduce

1. Copy existing example with Postgres from inst/examples/postgresql
2. Modify the code so that shiny.telemetry uses SQLite backend
3. Run the app, produce a few events
4. Run analytics app
5. Confirm that data was collected - particularly by exploring a user session.
6. Confirm that "Activity Stats" page displays "Amount: 0" on the plotly chart

Expected behavior

Expected to see correct counts for the user inputs.

Attachments

No response

Screenshots or Videos

Data is available when exploring a particular session:

However data is not aggregated properly on "Activity stats"

Additional Information

I know that the problem is related to how timestamp is stored in SQLite and how its parsed by lubridate. See:

sqlite_ts <- 1718635448
lubridate::as_date(sqlite_ts)
# [1] "4707434-01-14"

Please take a look at the proposed solution in #183.