Re: [sqlite] Date Formatting from Excel Float Date/Time Representation
select t0.key, "Issue Type", strftime('%Y-%m-%d %H:%M:%S', (Updated - (julianday('1970-01-01') - julianday('1899-12-30'))) + 2440587.5) Updated from JIRA_Stat_0_20170106124800 t0 inner join JIRA_Stat_1_20170106124800 t1 on t0.key = t1.key where "Last Comment" is not null order by assignee; give that a try On Fri, Jan 6, 2017 at 2:23 PM, Jens Alfkewrote: > > > On Jan 6, 2017, at 12:16 PM, Ed Lipson wrote: > > > > The Updated column is properly reflected as a float, as that is the > > internal Excel format. What formatting functions can I use to get it to > > appear as a date time in SQL output, as it appears in Excel? I have tried > > strftime and date but I don't get any meaningful data. > > This doesn’t seem like a question related to SQLite; you’ll need to find > out the definition of Excel’s internal date/time format. Most numeric time > formats have much larger numbers since they count in seconds, but the > values around 42,000 that you showed would be consistent with a number of > days since 1/1/1900. You can probably use one of the standard library > functions to convert that into a time_t and print it. > > —Jens > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date Formatting from Excel Float Date/Time Representation
> On Jan 6, 2017, at 12:16 PM, Ed Lipsonwrote: > > The Updated column is properly reflected as a float, as that is the > internal Excel format. What formatting functions can I use to get it to > appear as a date time in SQL output, as it appears in Excel? I have tried > strftime and date but I don't get any meaningful data. This doesn’t seem like a question related to SQLite; you’ll need to find out the definition of Excel’s internal date/time format. Most numeric time formats have much larger numbers since they count in seconds, but the values around 42,000 that you showed would be consistent with a number of days since 1/1/1900. You can probably use one of the standard library functions to convert that into a time_t and print it. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Date Formatting from Excel Float Date/Time Representation
I'm trying to work with JIRA information. I export my JIRA records and load them into a SQLite3 database via a python script. The export is in Excel format. When I send the data back from SQLite3 to Excel and have Excel format the date, it is correctly interpreted. From that I am assuming I have a 'proper' process of reading the Excel and writing the Excel, with SQLite3 as the middle. How would I format the data in a SQL statement so it looks correct? When I run: select t0.key, "Issue Type", Updated from JIRA_Stat_0_20170106124800 t0 inner join JIRA_Stat_1_20170106124800 t1 on t0.key = t1.key where "Last Comment" is not null order by assignee I get output which looks like: key Issue Type Updated TSGOPM-176 Task 42674.629861 TSGOPM-139 Task 42698.225695 The Updated column is properly reflected as a float, as that is the internal Excel format. What formatting functions can I use to get it to appear as a date time in SQL output, as it appears in Excel? I have tried strftime and date but I don't get any meaningful data. Thanks, Ed ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users