Re: [sqlite] Time comparisen and CASE WHEN

[Pavel Ivanov]

> How come my sqlite can't recognize this statement?  It's not showing as 
> completed and I don't see why.

Maybe there should be semicolon after update statement (i.e. after END
belonging to CASE)?


Pavel


On Tue, Nov 22, 2011 at 3:50 PM, Black, Michael (IS)
<michael.bla...@ngc.com> wrote:
> How come my sqlite can't recognize this statement?  It's not showing as 
> completed and I don't see why.
>
>
>
> Using 3.7.9 with default options.
>
>
>
> sqlite> CREATE TRIGGER tableA _InsertUpdate
>   ...>     AFTER INSERT
>   ...>     ON tableA
>   ...>  begin
>   ...>    update tableB
>   ...>    set
>   ...>     [LowestTime] = CASE WHEN ( [LowestTime] IS NULL ) OR
>   ...>  ([LowestTime]>  TIME(NEW.TimeStamp)) THEN TIME(NEW.[TimeStamp]) ELSE  
> [LowestTime] END
>   ...> end;
>   ...>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Steffen Mangold [steffen.mang...@balticsd.de]
> Sent: Tuesday, November 22, 2011 2:44 PM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] Time comparisen and CASE WHEN
>
> Now I fixed it.
>
> CREATE TRIGGER tableA _InsertUpdate
>    AFTER INSERT
>    ON tableA
>  begin
>   update tableB
>   set
>    [LowestTime] = CASE WHEN ( [LowestTime] IS NULL ) OR
>  ([LowestTime]>  TIME(NEW.TimeStamp)) THEN TIME(NEW.[TimeStamp]) ELSE  
> [LowestTime] END
> end;
>
> ... THEN TIME(NEW.[TimeStamp]) - was the key.
>
> If I format a field with "type" 'time' my sqlite db tool only show me the 
> time, even if I put in a hole datetime.
> But I seems that sqlite also wrote the hole datetime in the field!
>
> THANK YOU IGOR!
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time comparisen and CASE WHEN

[Black, Michael (IS)]

How come my sqlite can't recognize this statement?  It's not showing as 
completed and I don't see why.



Using 3.7.9 with default options.



sqlite> CREATE TRIGGER tableA _InsertUpdate
   ...> AFTER INSERT
   ...> ON tableA
   ...>  begin
   ...>update tableB
   ...>set
   ...> [LowestTime] = CASE WHEN ( [LowestTime] IS NULL ) OR
   ...>  ([LowestTime]>  TIME(NEW.TimeStamp)) THEN TIME(NEW.[TimeStamp]) ELSE  
[LowestTime] END
   ...> end;
   ...>



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Steffen Mangold [steffen.mang...@balticsd.de]
Sent: Tuesday, November 22, 2011 2:44 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Time comparisen and CASE WHEN

Now I fixed it.

CREATE TRIGGER tableA _InsertUpdate
AFTER INSERT
ON tableA
 begin
   update tableB
   set
[LowestTime] = CASE WHEN ( [LowestTime] IS NULL ) OR
 ([LowestTime]>  TIME(NEW.TimeStamp)) THEN TIME(NEW.[TimeStamp]) ELSE  
[LowestTime] END
end;

... THEN TIME(NEW.[TimeStamp]) - was the key.

If I format a field with "type" 'time' my sqlite db tool only show me the time, 
even if I put in a hole datetime.
But I seems that sqlite also wrote the hole datetime in the field!

THANK YOU IGOR!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time comparisen and CASE WHEN

[Steffen Mangold]

Now I fixed it.

CREATE TRIGGER tableA _InsertUpdate
AFTER INSERT
ON tableA
 begin
   update tableB
   set
[LowestTime] = CASE WHEN ( [LowestTime] IS NULL ) OR  
 ([LowestTime]>  TIME(NEW.TimeStamp)) THEN TIME(NEW.[TimeStamp]) ELSE  
[LowestTime] END 
end;

... THEN TIME(NEW.[TimeStamp]) - was the key.

If I format a field with "type" 'time' my sqlite db tool only show me the time, 
even if I put in a hole datetime.
But I seems that sqlite also wrote the hole datetime in the field!

THANK YOU IGOR!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time comparisen and CASE WHEN

[Igor Tandetnik]

On 11/22/2011 3:08 PM, Steffen Mangold wrote:

Ok here the complete example (sorry if I wasn’t clear before):

1.  I had a table where I insert some data with a datetime and a value
CREATE TABLE tableA
(
  [TimeStamp]   datetime,
   [Value]   varchar
);

2.  Now I have a second table where I want save the lowest time insert in 
tableA
CREATE TABLE tableB
(
  [LowestTime] time,
);

3.  To store the time I wrote a trigger for tableA
CREATE TRIGGER tableA _InsertUpdate
   AFTER INSERT
   ON tableA
begin
  update tableB
  set
   [LowestTime] = CASE WHEN ( [LowestTime] IS NULL ) OR  ([LowestTime]>  
TIME(NEW.TimeStamp)) THEN NEW.[TimeStamp] ELSE [LowestTime] END
end;


Do you ever create a record in tableB? UPDATE statement never changes 
the number of rows in the table.


Note that you set LowestTime to be equal to TimeStamp, not 
time(TimeStamp). It ends up containing a string that encodes both date 
and time, not just time.



4.  Now I make 2 inserts in tableA (update trigger works because I created 
a dummy row to work)
INSERT into tableA ( [TimeStamp], [Value] ) VALUES ( ‘2011-01-01 01:00:00’, 
‘Dump’ );


After this, LowestTime is set to '2011-01-01 01:00:00'


INSERT into tableA ( [TimeStamp], [Value] ) VALUES ( ‘2011-01-01 02:00:00’, 
‘Dump’ );


'2011-01-01 01:00:00' is greater (lexicographically, the way strings are 
compared) than '02:00:00', so LowestTime is set to '2011-01-01 02:00:00'



5.  [LowestTime] should now be ‘01:00:00’ BUT it is ’02:00:00’??? 


Are you sure? How do you check this? I would expect it to be neither, 
but instead have a value of '2011-01-01 02:00:00'.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time comparisen and CASE WHEN

[Steffen Mangold]

Ok here the complete example (sorry if I wasn’t clear before):

1.  I had a table where I insert some data with a datetime and a value
CREATE TABLE tableA 
( 
 [TimeStamp]   datetime,  
  [Value]   varchar
);

2.  Now I have a second table where I want save the lowest time insert in 
tableA
CREATE TABLE tableB 
(
 [LowestTime] time,
);

3.  To store the time I wrote a trigger for tableA
CREATE TRIGGER tableA _InsertUpdate
  AFTER INSERT
  ON tableA
begin
 update tableB
 set
  [LowestTime] = CASE WHEN ( [LowestTime] IS NULL ) OR  ([LowestTime] > 
TIME(NEW.TimeStamp)) THEN NEW.[TimeStamp] ELSE [LowestTime] END
end;

4.  Now I make 2 inserts in tableA (update trigger works because I created 
a dummy row to work)
INSERT into tableA ( [TimeStamp], [Value] ) VALUES ( ‘2011-01-01 01:00:00’, 
‘Dump’ );
INSERT into tableA ( [TimeStamp], [Value] ) VALUES ( ‘2011-01-01 02:00:00’, 
‘Dump’ );

5.  [LowestTime] should now be ‘01:00:00’ BUT it is ’02:00:00’??? 


Hopes is more clear now.

Steffen Mangold


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time comparisen and CASE WHEN

[Igor Tandetnik]

On 11/22/2011 2:21 PM, Steffen Mangold wrote:

Oh sorry, i looked wrong. I insert this way:
INSERT INTO [filed1] VALUES '2011-01-01 08:00:00'

And because of the init of:
CREATE TABLE tabel1 (
   [field1] time,
);

SQLite writes only the time to the database.


What makes you believe so? To verify your (incorrect) assumption, why 
don't you run


select field1 from table1;

Then read http://sqlite.org/datatype3.html


But this fails:
[field1]<  TIME(NEW.TimeStamp)
  And I don't know why


Well, now you do (or at least, you should).


and if I do this:
TIME( [field1] )<  TIME( NEW.TimeStamp )
It returns always exact the opposite of what I aspect.


What do you expect, and how does the observed outcome differ from your 
expectations? Preferably, show a complete example - creating a table, 
inserting data, running SELECT statement that produces unexpected 
output; enough information for the reader to reproduce the problem.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time comparisen and CASE WHEN

[Steffen Mangold]

>
> sqlite> select time( '2011-01-29 08:00:00' );
> 08:00:00
>

Oh sorry, i looked wrong. I insert this way:
INSERT INTO [filed1] VALUES '2011-01-01 08:00:00'

And because of the init of:
CREATE TABLE tabel1 (  
  [field1] time,  
);

SQLite writes only the time to the database.

But this fails: 
[field1] < TIME(NEW.TimeStamp) 
 And I don't know why

and if I do this:
TIME( [field1] ) < TIME( NEW.TimeStamp )
It returns always exact the opposite of what I aspect.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time comparisen and CASE WHEN

[Petite Abeille]

On Nov 22, 2011, at 6:44 PM, Pavel Ivanov wrote:

>> INSERT INTO [filed1] VALUES TIME('29-01-2011 08:00:00')
> 
> Result of TIME('29-01-2011 08:00:00') is NULL. So your field1 doesn't
> contain anything. Maybe that's why your comparison doesn't work.

As per the fine manual:

http://www.sqlite.org/lang_datefunc.html

Try -MM-DD HH:MM:SS instead. 

So:

sqlite> select time( '2011-01-29 08:00:00' );
08:00:00


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time comparisen and CASE WHEN

[Pavel Ivanov]

> I insert data in this way (for example):
>
> INSERT INTO [filed1] VALUES TIME('29-01-2011 08:00:00')

Result of TIME('29-01-2011 08:00:00') is NULL. So your field1 doesn't
contain anything. Maybe that's why your comparison doesn't work.


Pavel


On Tue, Nov 22, 2011 at 11:57 AM, Steffen Mangold
 wrote:
> Hi  Igor,
>
>>
>> Yes. You can use any expression. AND and OR are operators, just like + or =
>>
>
> Ok, thank you good to know.
>
>>
>> SQLite doesn't have a dedicated "time" type. There are many ways to store 
>> time values - e.g. as a string '12:34', or as a number of seconds from 
>> midnight.
>> How exactly do you put your time values into the field?
>>
>
> I create the table in this way:
>
> CREATE TABLE tabel1 (
>  [field1] time,
> );
>
> I insert data in this way (for example):
>
> INSERT INTO [filed1] VALUES TIME('29-01-2011 08:00:00')
>
>>
>> TIME() produces a string of the form '12:34:56' (hours:minutes:seconds).
>> What's in NEW.TimeStamp? What's in field1?
>>
>
> NEW.TimeStamp is a complete datetime.  But I only want to compare the time 
> part in my trigger.
>
> --
> Steffen Mangold
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time comparisen and CASE WHEN

[Steffen Mangold]

Hi  Igor,

>
> Yes. You can use any expression. AND and OR are operators, just like + or =
>

Ok, thank you good to know.

>
> SQLite doesn't have a dedicated "time" type. There are many ways to store 
> time values - e.g. as a string '12:34', or as a number of seconds from 
> midnight.
> How exactly do you put your time values into the field?
>

I create the table in this way:

CREATE TABLE tabel1 (  
  [field1] time,  
);

I insert data in this way (for example):

INSERT INTO [filed1] VALUES TIME('29-01-2011 08:00:00')

>
> TIME() produces a string of the form '12:34:56' (hours:minutes:seconds). 
> What's in NEW.TimeStamp? What's in field1?
>

NEW.TimeStamp is a complete datetime.  But I only want to compare the time part 
in my trigger.

--
Steffen Mangold

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time comparisen and CASE WHEN

[Igor Tandetnik]

On 11/22/2011 11:26 AM, Steffen Mangold wrote:

i have two little questions.
First one, is this valid syntax for a CASE WHEN?

CASE WHEN ( [field1] IS NOT NULL ) AND ( ( [field1]<  1 ) OR ( [field1]>  0 ) )

In special I mean "can I use AND, OR in CASE WHEN".


Yes. You can use any expression. AND and OR are operators, just like + or =


Second question, I get really strange results when I try to compare time values.
For example:

Given is a table with a field [field1] data type 'time'.


SQLite doesn't have a dedicated "time" type. There are many ways to 
store time values - e.g. as a string '12:34', or as a number of seconds 
from midnight.


How exactly do you put your time values into the field?


Now I make a compare in a trigger like this.
[field1]<  TIME(NEW.TimeStamp)


TIME() produces a string of the form '12:34:56' (hours:minutes:seconds). 
What's in NEW.TimeStamp? What's in field1?


See also http://sqlite.org/lang_datefunc.html
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Time comparisen and CASE WHEN

[Steffen Mangold]

Hi there,

i have two little questions.
First one, is this valid syntax for a CASE WHEN?

CASE WHEN ( [field1] IS NOT NULL ) AND ( ( [field1] < 1 ) OR ( [field1] > 0 ) )

In special I mean "can I use AND, OR in CASE WHEN".



Second question, I get really strange results when I try to compare time values.
For example:

Given is a table with a field [field1] data type 'time'.
Now I make a compare in a trigger like this.
[field1] < TIME(NEW.TimeStamp)

But this is not working :(

Thanks for your help
Steffen Mangold
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users