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
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Regards
John McMahon
[EMAIL PROTECTED]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
