Informix 7.3 - 在插入/更新时声明具有默认为当前日期的日期列数据类型 [英] Informix 7.3 - Declaring a date column data type with default as current date on insert/update
问题描述
再次寻找您的帮助 Jonathan Leffler ！
我正在Informix 7.3上创建一个表，需要一个时间戳记字段，这个字段在插入和更新上将默认为 。
I am creating a table on Informix 7.3 and need a timestamp field that will default to today on inserts and updates.
如何为默认值为当前时间的表定义日期/ datetime / timestamp列？
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
(
)
我不知道有什么其他的表有这个功能，我甚至不敢100％肯定它是支持，但如果有办法， d喜欢知道。
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.
我发现的主题唯一的好主意是 here
The only good lead I've found on the topic is here
任何人都有任何想法/解决方案？
Anyone have any ideas/solutions?
更多调查结果：
http://www.4js.com/techdocs/genero/fgl/devel/DocRoot/User/DatabaseSchema.html < br>
在Informix中投放日期
我在另一个表中找到了一个 datetime 列类型def：
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}
推荐答案
我不认识问题中使用的元语言，所以我不知道与DBMS有什么相比能够做的的
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
);
这给你一个表，其中4个时间列中的每一个将被分配一个默认值，如果你不要在INSERT操作中指定它：
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");
另一方面，如果指定列，则指定的值优先。我假设DBDATE =Y4MD-，以便DATE值看起来像DATETIME YEAR TO DAY值：
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");
这里的值都是指定的，所以这些是存储的值。请注意，ISQL Perform（和最典型的I4GL程序）等程序将提供所有列的值，因此默认机制不会生效。
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.
您可以玩触发更改UPDATE上的值，因此您可以插入日期和最后更新列（如果需要，则可以输入whodunnit列 - created_by和updated_by）。再次，您必须担心默认值与显式提供的值。
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.
现在，由于您使用的IDS 7.3x，一年或两年前终止服务，您的功能与IDS 11.70中的功能略有不同。你应该在升级。
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.
我发现这个代码（最终）用于更新触发器。它可以从2006年开始。
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;
玩得开心！
这篇关于Informix 7.3 - 在插入/更新时声明具有默认为当前日期的日期列数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
