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

[Reverend Jim]

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?

[Reverend Jim]

Got it. The solution was

SELECT DATE('1899-12-31','localtime','+'||day||' days') AS day

[Schol-R-LEA]

The date format in question appears to be from MS Excel, which uses 1899-12-30 for the Date Zero; Excel in turn seems to have taken this from Lotus 1-2-3, back in the day. The page I linked to below opines that it was supposed to be 1900-01-01 but that someone miscalculated when the format was first developed, and Microsoft 'corrected' this by simply resetting the Day Zero.

Anyway, I did find this SO page which addresses at least part of the problem as so:

SELECT DATETIME((49400 * 3600 * 24) - 3014928000, 'unixepoch');

Which converts the Excel date to SQLite's native ISO-8601 subset by way of UNIX Epoch time, but this is more or less equivalent to what you are already doing, and still leaves the question of how to make this conversion inside the SQLite query for a given Excel date.

That page also has the alternate, but more or less equivalent, forumulation:

SELECT DATETIME(( 40074 - 25569) * 86400 , 'unixepoch') AS UNIX_DATE

Which may be more useful, I'm not sure.

Something like this may do it, but I have no way of testing it right now.

CREATE VIEW UsageByDay AS
SELECT DATETIME((day - 25569) * 86400, 'unixepoch') AS date, 
                SUM(data_in+data_out)/1024.0/1024.0 AS total 
  FROM usage 
GROUP BY day 
ORDER BY day DESC;

No idea if this works, sorry.

[Reverend Jim]

Yes. That works also.