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?