July 9, 2021 – The Blog Pros

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?

Day: July 9, 2021

How do I convert a SQLITE day number to a date string