Haven't fought with times in sqlite myself, but if everything's being stored as strings, I suspect what you need to do is use something like:
strftime("%s",TIME(NOW)) > strftime("%s",FIELD1) That is, convert the complex strings into something that sqlite can reasonably treat as numbers for the purpose of comparison (i.e. seconds). By the way, the date-time format you showed in your sample (DD-MM-YYYY HH:MM:SS) is going to get you into trouble, and not only in sqlite. You should strongly consider using the ISO standard YYYY-MM-DD HH:MM:SS. -sean -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Steffen Mangold Sent: Tuesday, November 22, 2011 8:58 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Time comparisen and CASE WHEN 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 ----------------------------------------------------------------------------------- This email message is for the sole use of the intended recipient(s) and may contain confidential information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ----------------------------------------------------------------------------------- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users