DATETIME DEFAULT .... other than CURRENT ?

Is it possible to define a default value for a DATETIME column other than CURRENT? I need a default value 8 hours after the record was created; I have tried a variety of syntaxs, and the documentation on what works after DEFAULT is thin. I have also create a stored function that returns the current time plus eight hours, but using the stored function to provide the default value does not work either [at least by the syntaxes I have tried]. CREATE TABLE example ( ... time_out DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND + 8 UNITS HOUR, ... CREATE TABLE example ( ... time_out DATETIME YEAR TO SECOND DEFAULT f_my_function(), ... CREATE TABLE example ( ... time_out DATETIME YEAR TO SECOND DEFAULT DATETIME(f_my_function()) YEAR TO SECOND, ... -- Adam Tauno Williams <mailto:awilliam@whitemice.org> GPG D95ED383 Systems Administrator, Python Developer, LPI / NCLA

I would suggest using an insert trigger and check to see if the value is null then update it. You may not use expression in defaults. John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 04/08/2015 08:19:59 AM: > From: "Adam Tauno Williams" <awilliam@whitemiceconsulting.com> > To: ids@iiug.org > Date: 04/08/2015 08:20 AM > Subject: DATETIME DEFAULT .... other than CURRENT ? [34933] > Sent by: ids-bounces@iiug.org > > Is it possible to define a default value for a DATETIME column other > than CURRENT? I need a default value 8 hours after the record was > created; I have tried a variety of syntaxs, and the documentation on > what works after DEFAULT is thin. > > I have also create a stored function that returns the current time plus > eight hours, but using the stored function to provide the default value > does not work either [at least by the syntaxes I have tried]. > > CREATE TABLE example (> > .... > > time_out DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND + 8 > UNITS HOUR, > > .... > > CREATE TABLE example (> > .... > > time_out DATETIME YEAR TO SECOND DEFAULT f_my_function(), > > .... > > CREATE TABLE example (> > .... > > time_out DATETIME YEAR TO SECOND DEFAULT DATETIME(f_my_function()) > YEAR TO SECOND, > > .... > > -- > Adam Tauno Williams <mailto:awilliam@whitemice.org> GPG D95ED383 > Systems Administrator, Python Developer, LPI / NCLA > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hello. You cannot use default values as a "not fixed" format, I think. The best way to solve your issue is creating a BEFORE INSERT trigger, testing for the datetime as null, for example, and then you can insert whatever calc in that field... Hope it helps. Best regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: awilliam@whitemiceconsulting.com > Subject: DATETIME DEFAULT .... other than CURRENT ? [34933] > Date: Wed, 8 Apr 2015 11:19:59 -0400 > > Is it possible to define a default value for a DATETIME column other > than CURRENT? I need a default value 8 hours after the record was > created; I have tried a variety of syntaxs, and the documentation on > what works after DEFAULT is thin. > > I have also create a stored function that returns the current time plus > eight hours, but using the stored function to provide the default value > does not work either [at least by the syntaxes I have tried]. > > CREATE TABLE example (> > .... > > time_out DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND + 8 > UNITS HOUR, > > .... > > CREATE TABLE example (> > .... > > time_out DATETIME YEAR TO SECOND DEFAULT f_my_function(), > > .... > > CREATE TABLE example (> > .... > > time_out DATETIME YEAR TO SECOND DEFAULT DATETIME(f_my_function()) > YEAR TO SECOND, > > .... > > -- > Adam Tauno Williams <mailto:awilliam@whitemice.org> GPG D95ED383 > Systems Administrator, Python Developer, LPI / NCLA > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

On Wed, 2015-04-08 at 12:40 -0400, Alexandre Marini wrote: > Hello. > You cannot use default values as a "not fixed" format, I think. > The best way to solve your issue is creating a BEFORE INSERT trigger, testing > for the datetime as null, for example, and then you can insert whatever calc > in that field... Thanks, that works. ### CREATE PROCEDURE p_initialize_visit()REFERENCING NEW AS n FOR workorder_visit; IF n.time_in IS NULL THEN LET n.time_in = CURRENT YEAR TO SECOND; END IF; IF n.time_out IS NULL THEN LET n.time_out = CURRENT YEAR TO SECOND + 8 UNITS HOUR; END IF; END PROCEDURE; CREATE TABLE workorder_visit (... time_in DATETIME YEAR TO SECOND, time_out DATETIME YEAR TO SECOND, ... CHECK (time_out > time_in) ) CREATE TRIGGER workorder_visit_insertINSERT ON workorder_visit REFERENCING NEW AS new FOR EACH ROW ( EXECUTE PROCEDURE p_initialize_visit() WITH TRIGGER REFERENCES );