2008/11/3 John <[EMAIL PROTECTED]>: > Hi > > I want to (if possible) create a table with a default timestamp in a > format other than "YYYY-MM-DD ..." per example below. I have tried a few > variants but always get same error. > > Can this be done and if so, how? > > create table ( > custnum integer primary key not null, > note text, > stamp default (strftime('%s', current_timestamp)) > ); > SQL error: near "(": syntax error > > -- > Regards > John McMahon > [EMAIL PROTECTED] > >
>From http://www.sqlite.org/lang_createtable.html, "The DEFAULT constraint specifies a default value to use when doing an INSERT. The value may be NULL, a string constant or a number. "; using a function as a default is not covered. A trigger can probably achieve what you want : create table tst( id integer primary key, ts integer default current_timestamp ); create trigger tst_update_ts after insert on tst begin update tst set ts = case strftime( '%s', ts ) not null when 1 then strftime( '%s', ts ) else ts end where id=new.id; end; insert into tst( id ) values( null ); insert into tst( id ) values( null ); select * from tst; 1|1225703251 2|1225703259 insert into tst values( null, 'my birthday' ); select * from tst; 1|1225703251 2|1225703259 3|my birthday Rgds, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users