Re: [sqlite] Date Formatting from Excel Float Date/Time Representation

[Ben Newberg]

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 Alfke  wrote:

>
> > 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

[Jens Alfke]

> 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] Date Formatting from Excel Float Date/Time Representation

[Ed Lipson]

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