Simon Davies wrote: > 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.
Thanks Simon, I had been looking at the "column-constraint" diagram and misinterpreted the "expr" part of "default ( expr )" as a generic expression that would include a function. > > A trigger can probably achieve what you want : Good idea thank you. > > 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 -- Regards John McMahon [EMAIL PROTECTED] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users