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
