斜槓人生

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
);

資料呈現如下：

用ASP.NET的GridView呈現如下：

(DateTime欄位預設值，另一個參考範例： 

CREATE TABLE myTable (
    start_time DATETIME YEAR TO FRACTION NOT NULL DEFAULT
               DATETIME(0001-01-01 09:00:00.000) YEAR TO FRACTION,
    end_time   DATETIME YEAR TO FRACTION NOT NULL DEFAULT
               DATETIME(0001-01-01 17:00:00.000) YEAR TO FRACTION,
    daily_max  INT NOT NULL,
    …some other columns…
)

 

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

)

 

注意：Year to Second格式，雖然在資料庫看起來秒後面有".0"，實際上給值和下查詢條件時，請忽略".0"，處理到秒就可以了。

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;

Select 時Where條件如何下：

使用範例：

SELECT id,name,create_date FROM Table1
  Where create_date between '2018-03-26 00:00:00' and  '2018-03-26 23:59:59';

注意：不能把日期格式中的"-"換成"/"喔！會發生語法錯誤：

"字串未被辨認為有效的 DateTime。"

between '2018/03/26 00:00:00' and  '2018/03/26 23:59:59'

you must use '1994-10-30' or '94-10-30' as the DATETIME string.

來源：http://www.pacs.tju.edu/informix/answers/english/docs/visionary/infoshelf/sqlr/02.fm3.html

 

※附錄：在C# 中如何將字串轉成符合上述資料庫型別的值，帶入SQL語句？

//informix DateTime(From Year to Second)欄位接受傳入的日期格式如下：
//'2018-03-26 00:00:00'
//'2018-03-26 23:59:59'

string SDate = "20180301";
string EDate = "20180331";

string sd = DateTime.ParseExact(SDate, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd 00:00:00");
string ed = DateTime.ParseExact(EDate, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd 23:59:59");

string sql = " Select * from Table1 Where CreateDateTime Between '{0}' and '{1}'; ";
sql = String.Format(sql,sd,ed);