I have a SQLite database that for some insane reason stores dates as the number of days since 1899-12-31. I want to create a view that will display the date as an actual date. I can calculate a specific date from a specific day number as
select date('1899-12-31','Localtime','+44386 day') as dddd
which returns the single value '2021-07-09'. Unfortunately there seems to be no way to replace the hard coded number with the 'day' column. The original table also has the data broken down by hour (thus the GROUP BY clause), and by data_out and data_in (thus the SUM) So far I have
CREATE VIEW UsageByDay AS
SELECT day, SUM(data_in+data_out)/1024.0/1024.0 AS total
FROM usage GROUP BY day
ORDER BY day DESC;
Which gives me on select
DAY total
44386 45.2344284057617
44385 35.1123762130737
44381 26.000226020813
.
.
.
I want to replace the numerical values for day with the calculated date to give
DAY total
2021-07-09 45.2344284057617
2021-07-08 35.1123762130737
2021-07-07 26.000226020813
.
.
.
Any suggestions on how to modify my CREATE VIEW query?