On Mon, Jan 24, 2005 at 09:09:29PM -0000, Lee Wenchian Civ AFRL/IFTC wrote: > > When creating a table, according to the syntax, a column can be specified > with DEFAULT column constraint and "The DEFAULT constraint specifies a > default value to use when doing an INSERT ...If the value is CURRENT_TIME, > CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC date and/or time is > inserted into the columns." > > Here's what I have. > > Create table TemperatureData ( Tempt real, TimeStamp default > CURRENT_TIMESTAMP); > > Insert into TempertaureData values( 40.2, NULL); > > > The code above creates a table storing a temperature value with the current > timestamp. > I would give TimeStamp a type, but I am not sure what type is appropriate > (text or blob). > The code as written does NOT insert the current time stamp to the last > field, but leaving it empty (as NULL indicates). I would get a syntax error > if I literally leave the second field blank. Does anyone know how it works? > > Also, I would like to know what type I should use for the comparison > operator to properly compare times on the automatically generated time > stamp.
Reference: http://freshmeat.net/articles/view/1428/ CREATE TABLE TemperatureData (Tempt real, timeEnter DATE); CREATE TRIGGER insert_tempdata_timeEnter AFTER INSERT ON TemperatureData BEGIN UPDATE TemperatureData SET timeEnter = DATETIME('NOW') WHERE rowid = new.rowid; END; Note, that give UTC. The link above gives ideas on how to get localtime. Hope that helps. Regards, Mike Chirico
