Re: [sqlite] Datatype for prices (1,500)

<< All you need to store in the database table is the number. How it is formatted for viewing or printing is controlled by the user interface. yes thats right, but it would be nice to have same behavior between MySQL and sqlite I think to use a char datatype is not a good solution. 2016-11-30

Re: [sqlite] Datatype for prices (1,500)

On 30 Nov 2016, at 3:52pm, Werner Kleiner wrote: > < > I tried this, but Inserting 1.700 is also stored as 1.7 > > With TEXT I can store it like 1.700 but we want to calculate later > with PHP or C# If what you want is calculation-ready value, then storing it as

Re: [sqlite] Datatype for prices (1,500)

Storing as either an integer or real will let you do math on the returned value. If you're looking for a pretty representation of the number, then that should be done in your own application. Alternatively though, you can explicitly query for the formatted text version of your number using the

Re: [sqlite] Datatype for prices (1,500)

On Wed, 30 Nov 2016, Igor Tandetnik wrote: Store it as an integer, in $.0001 units. So $1.500 would be represented simply as an integer 1500. All you need to store in the database table is the number. How it is formatted for viewing or printing is controlled by the user interface. Rich

Re: [sqlite] Datatype for prices (1,500)

On 30 Nov 2016, at 3:26pm, Werner Kleiner wrote: > << > In sqlite studio you can configure a column a decimal(7,4) . > but anyway, how can you store a price like 1.500 with numeric type? Declare it as REAL. However, if you’re

Re: [sqlite] Datatype for prices (1,500)

Werner; The data is stored as raw as it gets. What you use to physically SEE that data is presented by 'whatever it is you use'. So SQLite3.exe is going to present it one way, as close to as raw format as possible, in as minimalistic , while tools like SQLite Expert looks at the value types

Re: [sqlite] Datatype for prices (1,500)

On 2016-11-30 16:39, Werner Kleiner wrote: << All you need to store in the database table is the number. How it is formatted for viewing or printing is controlled by the user interface. yes thats right, but it would be nice to have same behavior between MySQL and sqlite I think to use a char

Re: [sqlite] Datatype for prices (1,500)

On Wed, Nov 30, 2016 at 04:39:25PM +0100, Werner Kleiner wrote: > yes thats right, but it would be nice to have same behavior between > MySQL and sqlite SQLite3 has very few types. Adding types is very difficult, so I doubt new types will be added anytime soon. Price data and datetime data are

Re: [sqlite] Datatype for prices (1,500)

@Simon <: > << All you need to store in the database table is the number. How it is > formatted for viewing or printing is controlled by the user interface. > > yes thats right, but it would be nice to have same behavior between > MySQL and sqlite > I think to use a char datatype is not a good

Re: [sqlite] Datatype for prices (1,500)

On 11/30/2016 9:42 AM, Werner Kleiner wrote: It would be nice if there is a way to store in the same way as in MySQL with filling zeros. Is this not possible with sqlite? Store it as an integer, in $.0001 units. So $1.500 would be represented simply as an integer 1500. -- Igor Tandetnik

Re: [sqlite] Datatype for prices (1,500)

<<: > On 30 Nov 2016 at 14:42, Werner Kleiner wrote: > >> Hello, >> there is a small application which uses MYSQL db and can also switch to >> sqlite >> In MySQL a table "prices" has a column "basicprice" which is decimal(7,4) >> I can store a price in form 1.500 there.

Re: [sqlite] Datatype for prices (1,500)

On 11/30/16, Chris Locke wrote: > I recently had this problem. Values stored as real values. Had to check > records in the database to see if any value had changed, and needed > updating. Even though all values in my code were singles, I had bad > rounding problems where

Re: [sqlite] Datatype for prices (1,500)

I recently had this problem. Values stored as real values. Had to check records in the database to see if any value had changed, and needed updating. Even though all values in my code were singles, I had bad rounding problems where (as an example) 0.1+2.2 did not equal 2.3 in the database. Aargh.

Re: [sqlite] Datatype for prices (1,500)

On Wed, Nov 30, 2016 at 9:46 AM, Chris Locke wrote: > I recently had this problem. Values stored as real values. Had to check > records in the database to see if any value had changed, and needed > updating. Even though all values in my code were singles, I had bad >

Re: [sqlite] Datatype for prices (1,500)

On Wed, Nov 30, 2016 at 02:22:42PM -0600, John McKown wrote: > On Wed, Nov 30, 2016 at 9:46 AM, Chris Locke wrote: > > Storing as integers is the way to go. > > At present, I think that is the best way. Of course, we could argue that > every RDMS "should" implement Decimal

Re: [sqlite] Datatype for prices (1,500)

Wattage problem based on incoherent understanding of how floating point numbers are stored. Not an actual problem if you do your comparisons properly: SQLite version 3.16.0 2016-11-30 05:08:59 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to

Re: [sqlite] Datatype for prices (1,500)

> You were given a good recommendation save everything in "cents". Which > might also be a good solution depending on the underlying language you > use. as you can't store money in a float! And why can you not store money in a float? ___

Re: [sqlite] Datatype for prices (1,500)

> > I recently had this problem. Values stored as real values. Had to check > > records in the database to see if any value had changed, and needed > > updating. Even though all values in my code were singles, I had bad > > rounding problems where (as an example) 0.1+2.2 did not equal 2.3 in the

Re: [sqlite] Datatype for prices (1,500)

On Wed, Nov 30, 2016 at 5:25 PM, Nico Williams wrote: > On Wed, Nov 30, 2016 at 02:22:42PM -0600, John McKown wrote: > > > > [...] every RDMS "should" implement Decimal Floating Point. > > You could argue that every programming language needs that. What makes > SQL more

Re: [sqlite] Datatype for prices (1,500)

On Wednesday, 30 November, 2016 17:58, Darren Duncan said: > On 2016-11-30 2:43 PM, Keith Medcalf wrote: > > > >> You were given a good recommendation save everything in "cents". Which > >> might also be a good solution depending on the underlying language you > >>

Re: [sqlite] Datatype for prices (1,500)

On 2016-11-30 3:14 PM, Keith Medcalf wrote: On Wednesday, 30 November, 2016 17:58, Darren Duncan said: On 2016-11-30 2:43 PM, Keith Medcalf wrote: You were given a good recommendation save everything in "cents". Which might also be a good solution depending on

Re: [sqlite] Datatype for prices (1,500)

On 2016-11-30 2:43 PM, Keith Medcalf wrote: You were given a good recommendation save everything in "cents". Which might also be a good solution depending on the underlying language you use. as you can't store money in a float! And why can you not store money in a float? Unlike say

Re: [sqlite] Datatype for prices (1,500)

On 11/30/2016 11:42 AM, Richard Hipp wrote: On 11/30/16, Chris Locke wrote: I recently had this problem. Values stored as real values. Had to check records in the database to see if any value had changed, and needed updating. Even though all values in my code were

Re: [sqlite] Datatype for prices (1,500)

On 30 Nov 2016, at 10:43pm, Keith Medcalf wrote: >> You were given a good recommendation save everything in "cents". Which >> might also be a good solution depending on the underlying language you >> use. as you can't store money in a float! > > And why can you not store

[sqlite] SQLite does not use opportunity to optimise COUNT() queries using partial indexes

I am not very familiar with the SQLite internals, but I believe that index structure is similar to that of a table, ie it's a B-TREE with a root containing a node count value. If so, then queries like SELECT COUNT() FROM FOO WHERE <...>; can be optimised the same way that queries like SELECT

[sqlite] performance issue through Dll upgrade

Hello, we have following performance Issue since we upgraded from SQLite 3.8.5 to SQLite 3.10.0: SQLite 3.8.5: to INSERT 380.000 records into a in inMemory Database it takes 10 seconds SQLite 3.10.0 to INSERT 380.000 records into a in inMemory Database

[sqlite] Datatype for prices (1,500)

Hello, there is a small application which uses MYSQL db and can also switch to sqlite In MySQL a table "prices" has a column "basicprice" which is decimal(7,4) I can store a price in form 1.500 there. Same table and same columns in sqlite with datatype float(7,4) stores this value like 1.5 It

Re: [sqlite] Datatype for prices (1,500)

On 30 Nov 2016 at 14:42, Werner Kleiner wrote: > Hello, > there is a small application which uses MYSQL db and can also switch to sqlite > In MySQL a table "prices" has a column "basicprice" which is decimal(7,4) > I can store a price in form 1.500 there. > Same table