Re: [sqlite] Data types for date and time functions

On Monday, 25 September, 2017 06:20, R Smith wrote: >On 2017/09/25 10:12 AM, David Wellman wrote: >All of these have pro's and cons. Integer storage is usually most >efficient, but it takes some calculation to interpret, however SQLite >is very efficient at it, but if you

Re: [sqlite] Data types for date and time functions

On 2017/09/25 3:46 PM, R Smith wrote: PS: I refer to "Excel" only, but the problem probably persists in all of MS Office, though I didn't check. Thanks to Igor's post and some quick testing, I can confirm that it seems to only affect Excel, not all of MS Office.

Re: [sqlite] Data types for date and time functions

On 2017/09/25 2:23 PM, Stephan Buchert wrote: I was just going to write that you can easily convert an MS serial date value stored in Sqlite to a date string (using 40777 as example): sqlite> select date('1899-12-31', 40777||' days'); 2011-08-23 However, according to

Re: [sqlite] Data types for date and time functions

On 9/25/2017 8:23 AM, Stephan Buchert wrote: I was just going to write that you can easily convert an MS serial date value stored in Sqlite to a date string (using 40777 as example): sqlite> select date('1899-12-31', 40777||' days'); 2011-08-23 However, according to

Re: [sqlite] Data types for date and time functions

On 2017-09-25 08:19:52, "R Smith" wrote: On 2017/09/25 10:12 AM, David Wellman wrote: C - Storing a string with a date or date and time, typically the standard form is ISO8601 which looks like '-MM-DDTHH:NN:SS.MSS +ZZ:ZZ' with the T optionally being a space and the +

[sqlite] Data types for date and time functions

I fired up an MS Excel 2013 and yes, there 1900-02-29 exists and counts for the serial date value! My original comment was, that also storing in Sqlite the MS serial date values would be possible (as well as Matlab date numbers, etc.), and the Sqlite date/time functions allow quite easily to do

[sqlite] Data types for date and time functions

I was just going to write that you can easily convert an MS serial date value stored in Sqlite to a date string (using 40777 as example): sqlite> select date('1899-12-31', 40777||' days'); 2011-08-23 However, according to

Re: [sqlite] Data types for date and time functions

On 2017/09/25 10:12 AM, David Wellman wrote: Hi, We're designing a new feature which will involve a lot of date/time calculations which we intend to do within the SQLite engine// The question has come up as to how we should store date/time values in our tables? Basically how should

Re: [sqlite] Data types for date and time functions

Internally SQLite stores and process numbers as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. I have not examined the code in any depth but would assume that if you store the data in the same format it would save on any processing overhead for

Re: [sqlite] Data types for date and time functions

David Wellman wrote: > The question has come up as to how we should store date/time values in our > tables? Basically how should we define our date/time columns? SQLite does not have a separate date/time type. If you want to use the built-in date/time function, you can store values in one of

[sqlite] Data types for date and time functions

Hi, We're designing a new feature which will involve a lot of date/time calculations which we intend to do within the SQLite engine. As far as we can tell it has the functions that we need. Basically we'll be loading data into SQLite and performing analysis and calculations using SQL. The