GBase 8s 获取对象创建时间语句

获取对象创建时间的语句

获取实例初始化时间，通过rootdbs的创建时间来确定

-- 获取实例初始化时间（rootdbs创建时间）
SELECT dbinfo('utc_to_datetime',d.created) AS created 
FROM sysmaster:sysdbstab d 
WHERE dbsnum = 1;

获取dbspace的创建时间，与上面相同的语句

-- 获取dbspace的创建时间
SELECT d.name, dbinfo('utc_to_datetime',d.created) AS created
FROM sysmaster:sysdbstab d
WHERE d.name = 'plogdbs';

获取数据库的创建时间

-- 获取数据库创建时间
SELECT dbinfo('utc_to_datetime',created) AS created 
FROM sysmaster:sysdbspartn 
WHERE name = 'testdb';

获取表的创建时间，需要包含标准表和分片表

-- 获取表创建时间（本库）
SELECT t.tabname,'Normal' AS tabtype,dbinfo('utc_to_datetime',p.created) AS created
FROM systables t,sysmaster:sysptnhdr p
WHERE t.partnum = p.partnum 
AND t.tabtype = 'T'
AND t.tabname = 'tab11'
UNION 
SELECT t.tabname,'Fragment' AS tabtype,dbinfo('utc_to_datetime',p.created) AS created
FROM systables t,sysfragments f,sysmaster:sysptnhdr p
WHERE t.tabid = f.tabid
AND f.partn = p.partnum
AND f.fragtype = 'T'
AND t.tabname = 'tab11';

获取索引的创建时间，如果是分片索引，将返回多行

-- 获取索引的创建时间(本库)
SELECT f.indexname,'INDEX' AS tabtype, dbinfo('utc_to_datetime',p.created) AS created
FROM sysfragments f, sysmaster:sysptnhdr p
WHERE f.partn = p.partnum
AND f.fragtype = 'I'
AND f.indexname = 'ix_tab_1_col1'

获取序列的创建时间

-- 获取序列创建时间（本库）
SELECT t.tabname,'SEQUENCE' AS tabtype,dbinfo('utc_to_datetime',p.created) AS created
FROM systables t,sysmaster:sysptnhdr p
WHERE t.partnum = p.partnum 
AND t.tabtype = 'Q'
AND t.tabname = 'seq1'

获取视图的创建日期（仅日期）

-- 获取视图创建日期（本库）
SELECT t.tabname,'VIEW' AS tabtype, t.created AS created
FROM systables t
WHERE t.tabtype = 'V'
AND t.tabname = 'view_systables';