Informix 7.3 - Declaring a date column data type with default as current date on insert/update

Question

Looking for your help again Jonathan Leffler!

I am creating a table on Informix 7.3 and need a timestamp field that will default to today on inserts and updates.

How can I define a date/datetime/timestamp column for a table with a default value of the current time?

Here is a field definition for a simple date field:

column upd_date date
    comments ""
    desc "Last update date"
    heading "Last update date"
    text "Last update date"
    attributes
 (
 )

There is also some other syntax in schema files that have comments about what the default should be:

column beg_date date{DEF: date academic session/subsession officially begins}

    comments ""
    desc "Beginning date."
    heading "Beg Date"
    text "Date - Begin"
    attributes
 (
 )

I'm not sure of any other tables that have this functionality, and I'm not even 100% sure that it is supported, but if there is a way, I'd love to know.

The only good lead I've found on the topic is here

Anyone have any ideas/solutions?

---

More findings:
http://www.4js.com/techdocs/genero/fgl/devel/DocRoot/User/DatabaseSchema.html
Cast Date in Informix

There is a datetime column type that I found in another table def:

column beg_time datetime year to minute
    comments    ""
    desc    "Beginning date and time of period"
    heading "Beg Time"
    text    "Date/Time - Slot Begin"
    attributes
    (
    )
{DEF: date and time this group/person may register}

Solution 1

I don't recognize the meta-language used in the question, so I'm not sure what that is capable of compared with what the DBMS is capable of.

CREATE TABLE ExampleDatesAndTimes
(
    rownumber     SERIAL NOT NULL PRIMARY KEY,
    date_column   DATE DEFAULT TODAY NOT NULL,
    datetime_yd   DATETIME YEAR TO DAY
                  DEFAULT CURRENT YEAR TO DAY NOT NULL,
    datetime_ys   DATETIME YEAR TO SECOND
                  DEFAULT CURRENT YEAR TO SECOND NOT NULL,
    datetime_hs   DATETIME HOUR TO SECOND
                  DEFAULT CURRENT HOUR TO SECOND NOT NULL,
    payload       VARCHAR(255) NOT NULL
);

This gives you a table in which each of the 4 temporal columns will be assigned a default value if you don't specify it in the INSERT operation:

INSERT INTO ExampleDatesAndTimes(Payload) VALUES ("Hello");

On the other hand, if you specify the columns, then the specified values take precedence. I'm assuming the DBDATE="Y4MD-" so that DATE values look like DATETIME YEAR TO DAY values:

INSERT INTO ExampleDatesAndTimes
    VALUES(0, '1066-10-14', '2001-01-01', '2012-11-10 09:08:07',
           '23:23:21', "Gezundheit");

Here, the values are all specified, so those are the values stored. Note that programs such as ISQL Perform (and most typical I4GL programs) will provide values for all the columns so the default mechanism won't take effect.

You can play with triggers to alter the values on UPDATE, so you can have a date inserted and a 'last updated' column (and whodunnit columns - created_by and updated_by - if you want). Again, you have to worry about defaults versus explicitly provided values.

Now, since you are using IDS 7.3x, which finally went out of service a year or two ago, you have slightly different functionality from what is available in IDS 11.70. You should be looking at upgrading.

---

I found this code (eventually) for playing with triggers on update. It dates from 2006.

CREATE TABLE talx_000
(
    i       SERIAL NOT NULL PRIMARY KEY,
    s       CHAR(30) NOT NULL,
    m_user  VARCHAR(32) DEFAULT USER NOT NULL,
    m_time  DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL
);
CREATE PROCEDURE current_user_time()
    RETURNING VARCHAR(32) AS m_user, DATETIME YEAR TO SECOND AS m_time;
    RETURN user(), CURRENT YEAR TO SECOND - 1 UNITS DAY;
END PROCEDURE;

CREATE TRIGGER upd_talx_000 UPDATE ON talx_000
    REFERENCING NEW AS NEW FOR EACH ROW
    (EXECUTE PROCEDURE current_user_time() INTO m_user, m_time);

INSERT INTO talx_000(s) VALUES("cached nonsense");
INSERT INTO talx_000(s, m_user) VALUES("inserted user", "sphinx");
INSERT INTO talx_000(s, m_time)
    VALUES("inserted time", DATETIME(1066-10-14 15:23:31) YEAR TO SECOND);
INSERT INTO talx_000(s, m_time, m_user)
    VALUES("inserted both", DATETIME(1805-10-21 13:15:00) YEAR TO SECOND,
           "nelson");

SELECT * FROM talx_000;

DROP TRIGGER upd_talx_000;

CREATE PROCEDURE upd_talx_000(i_val INTEGER);
    UPDATE talx_000
        SET m_user = "brandywine",
            m_time = DATETIME(3019-03-25 13:00:00) YEAR TO SECOND
        WHERE i = i_val;
END PROCEDURE;

CREATE TRIGGER upd_talx_000 UPDATE ON talx_000
    REFERENCING NEW AS NEW FOR EACH ROW
    (EXECUTE PROCEDURE upd_talx_000(NEW.i));

INSERT INTO talx_000(s) VALUES("cached nonsense");
INSERT INTO talx_000(s, m_user) VALUES("inserted user", "sphinx");
INSERT INTO talx_000(s, m_time)
    VALUES("inserted time", DATETIME(1066-10-14 15:23:31) YEAR TO SECOND);
INSERT INTO talx_000(s, m_time, m_user)
    VALUES("inserted both", DATETIME(1805-10-21 13:15:00) YEAR TO SECOND,
           "nelson");

SELECT * FROM talx_000;

Have fun!

Solution 2

TABLE sample
(
timestamp DATETIME(YEAR TO SECONDS)
)


In Informix-SQL Perform screen:

INSTRUCTIONS

AFTER EDITADD OF sample.timestamp
LET screen_tag = CURRENT

AFTER EDITUPDATE OF sample.timestamp
LET screen_tag = CURRENT

NOTE: I never use WITHOUT NULL on a DATE or DATETIME column since it's better to have an absent value as opposed to 12/31/1899 when WITHOUT NULL is specified on a temporal column.

Solution 3

This is tested & works for me - ONLY FOR INSERTS. I assume that you can handle the update scenario with a trigger if you need the field to update to the current date on every update

column use_date date default today not null
        comments ""
        desc "Date this use case was executed"
        heading "Usage date"
        text "Usage date"
        attributes
     (
     )

Comments

[CheeseConQueso]

@FComp - i hear you, but it will insert, by default, the current date on inserts. I just tried updating the record and the date remained the same. I suppose a trigger is needed for the date field update to current date on sql updates

[CheeseConQueso]

thanks jon - yeah you warned me about upgrading a couple informix questions i had ago. its not my call, but i appreciate you looking out for me. as far as the meta-lang is concerned, Jenzabar built their SIS on Informix and some of the native processes are altered as such

[CheeseConQueso]

also, thanks for the extra info - i do have whodunnit uid, id, affected area, action taken, etc.... and i did a test insert via isql and the date field automatically populated with the current date. today i tried the update and that failed to modify the field

[CheeseConQueso]

btw - wtf is payload="Gezundheit" about hahahah?

[FrankRuperto]

@QuesoConQueso: Si muy bien!.. you could CREATE TRIGGER to do that, but if you can do it on the client side only, like BEFORE EDITADD EDITUPDATE OF table, better yet. TRIGGER's seem to impose a lot of overhead on an engine and I try to avoid them unless absolutely necessary.

[CheeseConQueso]

btw - with regards to the meta-language, this syntax (i suppose) is specific to a Jenzabar CARS cx system running on Informix. tables are created through make build F=table_file_name and not directly with using SQL

[CheeseConQueso]

thanks for the update - i hope that one day i can return the favors