Re: [sqlite] auto-incrementing integer in composite primary key (SQLite3 Solution)

On April 16, 2012 09:27:06 PDT, "Mr. Puneet Kishor" wrote: Given CREATE TABLE t ( id INTEGER NOT NULL, created_on DATETIME DEFAULT CURRENT_TIMESTAMP PRIMARY KEY (id, created_on) ); how can I make just the 'id' column

Re: [sqlite] auto-incrementing integer in composite primary key

On Mon, 16 Apr 2012 11:27:06 -0500, "Mr. Puneet Kishor" wrote: >Given > > CREATE TABLE t ( > id INTEGER NOT NULL, > created_on DATETIME DEFAULT CURRENT_TIMESTAMP > PRIMARY KEY (id, created_on) > ); > >how can I make just

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

On Tue, Apr 17, 2012 at 2:45 PM, Richard Hipp wrote: > On Tue, Apr 17, 2012 at 2:44 PM, Nico Williams wrote: >> Note that you'll lose any fractional second information when you do >> this.  On the other hand, fractional second information does not sort >>

Re: [sqlite] Time zones

jwzumwalt wrote: > Where can I find a complete list of Sqlite timezones. I Google searched > without success. > > i.e > hawaiin ?? > date_default_timezone_set('America/Los_Angeles'); // pacific timezone date_default_timezone_set is not part of SQLite. You must be using

Re: [sqlite] Time zones

You're talking PHP...not SQLite. SQLite doesn't know about timezones other than "local" and "utc". So your timezones will depend on your OS. On RedHat it's in /usr/share/zoneinfo and there's tons of them. I've got 1,743 of them. Michael D. Black Senior Scientist Advanced

[sqlite] Time zones

Where can I find a complete list of Sqlite timezones. I Google searched without success. i.e hawaiin ?? date_default_timezone_set('America/Los_Angeles'); // pacific timezone date_default_timezone_set('America/Denver'); // mountain timezone central ?? date_default_timezone_set('America/New_York);

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

On Tue, Apr 17, 2012 at 2:44 PM, Nico Williams wrote: > On Tue, Apr 17, 2012 at 11:18 AM, Mark Jones > wrote: > > I think I'll spend the time going back and storing the dates as integer > > time (since the epoch) as Nico suggested and just use

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

Store them as float or do integer and multiple by a power of 10 to get as many digits as you want. So 1.234 seconds *10^3 can be 1234 integer Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

On Tue, Apr 17, 2012 at 11:18 AM, Mark Jones wrote: > I think I'll spend the time going back and storing the dates as integer > time (since the epoch) as Nico suggested and just use strftime to convert > them as and when required. Note that you'll lose any fractional

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

Thanks goes out to both of you for your quick responses! For text in SQLite, delimit with single quotes, not double quotes. Double > quotes are used for tricky entity names. And you probably don't want the > quotes around the real numbers at all. I'll go off and re-read up on the quotes and

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

On Tue, Apr 17, 2012 at 10:45 AM, Mark Jones wrote: > Afternoon all, > > I have the following schema: > > CREATE TABLE day > ("id" INTEGER PRIMARY KEY, > "timestamp" DATETIME, > "value" REAL); > > And the following sample data: > > INSERT INTO day VALUES (NULL,

Re: [sqlite] Slightly unexpected behaviour when comparing date and datetime

On 17 Apr 2012, at 4:45pm, Mark Jones wrote: > I have the following schema: > > CREATE TABLE day > ("id" INTEGER PRIMARY KEY, > "timestamp" DATETIME, > "value" REAL); There is no such datatype as 'DATETIME'. You are actually storing text. See especially section 1.2,

[sqlite] Slightly unexpected behaviour when comparing date and datetime

Afternoon all, I have the following schema: CREATE TABLE day ("id" INTEGER PRIMARY KEY, "timestamp" DATETIME, "value" REAL); And the following sample data: INSERT INTO day VALUES (NULL, "2012-01-01", "5.0"); INSERT INTO day VALUES (NULL, "2012-01-02", "6.0"); INSERT INTO day VALUES (NULL,

Re: [sqlite] How point numbers are they stored in sqlite?

ok, thank you all! Le 17 avr. 2012 à 11:35, Richard Hipp a écrit : > > Key point: Floating point numbers are approximations. This is an inherent > property of IEEE floating point numbers, not a limitation of SQLite. If > you need an exact answer, use integers. > > -- > D. Richard Hipp >

Re: [sqlite] how much "case...when..." command?

YAN HONG YE wrote: > UPDATE bb SET Slevel = >CASE price1>12 WHEN 1 THEN 1 ELSE 0 END + >CASE price1>30 WHEN 1 THEN 1 ELSE 0 END + >CASE price2>20 WHEN 1 THEN 1 ELSE 0 END + >CASE price2>30 WHEN 1 THEN 1 ELSE 0 END + > case... > csse... > ... >CASE

Re: [sqlite] How point numbers are they stored in sqlite?

On 17 avr. 2012, at 11:35, Richard Hipp wrote: On Tue, Apr 17, 2012 at 5:12 AM, Paxdo Presse > wrote: Hi, How point numbers are they stored in sqlite? In a field with REAL affinity: round(11.578767 / 2 , 4) is displayed "5.7894" in SQLite/Navicat :-),

Re: [sqlite] How point numbers are they stored in sqlite?

> From: Paxdo Presse > To: General Discussion of SQLite Database > Date: Tue, 17 Apr 2012 11:12:45 +0200 > Subject: [sqlite] How point numbers are they stored in sqlite? > >Hi, > >How point numbers are they stored in sqlite? > >In a field with REAL

Re: [sqlite] How point numbers are they stored in sqlite?

On Tue, Apr 17, 2012 at 5:12 AM, Paxdo Presse wrote: > > Hi, > > How point numbers are they stored in sqlite? > > In a field with REAL affinity: > > round(11.578767 / 2 , 4) is displayed "5.7894" in SQLite/Navicat :-), > but "5,78939997" in the cursor of my development

[sqlite] How point numbers are they stored in sqlite?

Hi, How point numbers are they stored in sqlite? In a field with REAL affinity: round(11.578767 / 2 , 4) is displayed "5.7894" in SQLite/Navicat :-), but "5,78939997" in the cursor of my development language when I get it to sqlite. Internally, SQLite works with 5.7894 or

Re: [sqlite] how much "case...when..." command?

2012/4/17 YAN HONG YE : > UPDATE bb SET Slevel = >    CASE price1>12 WHEN 1 THEN 1 ELSE 0 END + >    CASE price1>30 WHEN 1 THEN 1 ELSE 0 END + >    CASE price2>20 WHEN 1 THEN 1 ELSE 0 END + >    CASE price2>30 WHEN 1 THEN 1 ELSE 0 END + > case... > csse... > ... >    CASE

[sqlite] how much "case...when..." command?

UPDATE bb SET Slevel = CASE price1>12 WHEN 1 THEN 1 ELSE 0 END + CASE price1>30 WHEN 1 THEN 1 ELSE 0 END + CASE price2>20 WHEN 1 THEN 1 ELSE 0 END + CASE price2>30 WHEN 1 THEN 1 ELSE 0 END + case... csse... ... CASE price2>80 WHEN 1 THEN 1 ELSE 0 END; in this command, I don't