postgresql怎么查看表的创建时间

方法一：通过查找表数据文件方式

这种方法通过查找表的数据文件的方式从而确定表的创建时间，但是这种方法并不能准备查询表的创建

时间，而且有时候，这种方法得到的信息还有可能是错误的，下面大致演示下。

--11 创建表并插入数据

francs=> create table test_ctime (id int4 primary key ,name varchar(32));

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_ctime_pkey" for table "test_ctime"

CREATE TABLE

francs=> insert into test_ctime select generate_series(1,10000),'create_time test';

INSERT 0 10000

francs=> \d test_ctime;

Table "francstest_ctime"

Column | Type | Modifiers

--------+-----------------------+-----------

id | integer | not null

name | character varying(32) |

Indexes:

"test_ctime_pkey" PRIMARY KEY, btree (id)

francs=> \dt+ test_ctime;

List of relations

Schema | Name | Type | Owner | Size | Description

--------+------------+-------+--------+--------+-------------

francs | test_ctime | table | francs | 536 kB |

(1 row)

备注：表创建好了，接下来演示如何定位表的物理文件。

--12 定位表所在的表空间

francs=> select relname,relfilenode,reltablespace from pg_class where relname='test_ctime';

relname | relfilenode | reltablespace

------------+-------------+---------------

test_ctime | 24650 | 0

(1 row)

备注：在 PostgreSQL 的逻辑结构体系中，表位于数据库中，同时表位于表空间上，面表空间对应系统上一个

文件目录，每个表由一个或者多个文件组成； 根据上面的结果，表 test_ctime 的 reltablespace

值为 0,表示位于所属数据库的默认表空间，注意 relfilenode 值为 24650。

--13 查询数据库 francs 的默认表空间

francs=> select oid,datname,dattablespace from pg_database where datname='francs';

oid | datname | dattablespace

-------+---------+---------------

16386 | francs | 16385

备注：上面查出数据库 francs 的默认表空间的 oid 为 16385。

--14 查找 oid 为 16385 的表空间

francs=> select oid, from pg_tablespace where oid=16385;

oid | spcname | spcowner | spcacl | spcoptions

-------+------------+----------+-----------------------------------------+------------

16385 | tbs_francs | 10 | {postgres=C/postgres,francs=C/postgres} |

(1 row)

备注：查了半天才查到表 test_ctime 的默认表空间为 tbs_francs，这里之所以饶这么大圈，是为

了展示 postgresql 中的一些逻辑结构关系，如果自己对环境比较熟悉，可以直接定位到

哪个表空间。

--15 查询表空间 tbs_francs 对应的物理目录

francs=> \db

List of tablespaces

Name | Owner | Location

------------+----------+------------------------------------------

pg_default | postgres |

pg_global | postgres |

tbs_francs | postgres | /database/1922/pgdata1/pg_tbs/tbs_francs

(3 rows)

备注：表空间 tbs_francs 的数据目录为 /database/1922/pgdata1/pg_tbs/tbs_francs。

--16 进入数据目录

[postgres@redhat6 16386]$ cd /database/1922/pgdata1/pg_tbs/tbs_francs

[postgres@redhat6 tbs_francs]$ ll

total 40K

drwx------ 4 postgres postgres 40K May 22 10:35 PG_92_201204301

[postgres@redhat6 tbs_francs]$ cd PG_92_201204301/

[postgres@redhat6 PG_92_201204301]$ ll

total 16K

drwx------ 2 postgres postgres 12K Jun 26 19:03 16386

drwx------ 2 postgres postgres 40K May 22 10:37 pgsql_tmp

备注：根据前面的步骤 13 查询的信息知道 16386 为数据库 francs 的 oid。 再根据步骤 12 的信息知道

表 test_ctime 的 relfilenode 值为 24650

--17 查找表 test_ctime 的数据文件

[postgres@redhat6 16386]$ ll 24650

-rw------- 1 postgres postgres 512K Jun 26 18:57 24650

备注：根据数据文件 24650 知道表的创建时间为 2012-06-26 18:57。但这种方法并不准确，因为

表上的 *** 作可能导致表重新生成文件，接着演示。

--18 cluster 表

francs=> cluster verbose test_ctime using test_ctime_pkey;

INFO: clustering "francstest_ctime" using index scan on "test_ctime_pkey"

INFO: "test_ctime": found 0 removable, 10000 nonremovable row versions in 64 pages

DETAIL: 0 dead row versions cannot be removed yet

CPU 000s/003u sec elapsed 008 sec

CLUSTER

francs=> select relname,relfilenode,reltablespace from pg_class where relname='test_ctime';

relname | relfilenode | reltablespace

------------+-------------+---------------

test_ctime | 24655 | 0

(1 row)

备注：表 test_ctime 经过 cluster *** 作后，重新生成了数据文件，文件号由原来的 24650 变成了 24655

--19 系统上再次查询表数据文件

[postgres@redhat6 16386]$ ll 24650

-rw------- 1 postgres postgres 0 Jun 26 19:19 24650

[postgres@redhat6 16386]$ ll 24655

-rw------- 1 postgres postgres 512K Jun 26 19:19 24655

备注：显然新文件的时间 24655 并不是表 test_ctime 的初始创建时间。

--110 vacuum full 表

francs=> vacuum full test_ctime;

VACUUM

francs=> select relname,relfilenode,reltablespace from pg_class where relname='test_ctime';

relname | relfilenode | reltablespace

------------+-------------+---------------

test_ctime | 24659 | 0

(1 row)

备注： vacuum full *** 作后，同样产生了新文件，新文件号为 24659

--111 系统上再次查询表数据文件

[postgres@redhat6 16386]$ ll 24659

-rw------- 1 postgres postgres 512K Jun 26 19:22 24659

PostgreSQL中直接用两个date(或者timestamp)值相减，其返回的是一个interval值，再有该interval值取出天数转换成分钟或秒数，再加上interval中分钟(和秒数)部分的值就可以了。

示例SQL:

select interval_value, date_part('day', interval_value) as day_value, date_part('day', interval_value)  24  60 + date_part('minute', interval_value) as minutes

from (

select (current_timestamp - to_timestamp('2013-08-21 13:23', 'yyyy-mm-dd hh24:mi')) as interval_value

) s;

PostgreSQL命令 EXPLAIN ANALYZE 是日常工作中了解和优化SQL查询过程所用到的最强大工具，后接如 SELECT ， UPDATE 或者 DELETE 等SQL语句，命令执行后并不返回数据，而是输出查询计划，详细说明规划器通过何种方式来执行给定的SQL语句。

下面是从 Postgres Using EXPLAIN 提取的查询：

它生成的查询计划：

Postgres构建了一个规划节点的树结构，以表示所采取的不同 *** 作，其中root根和每个 -> 指向其中一个 *** 作。在某些情况下， EXPLAIN ANALYZE 会提供除执行时间和行数之外的额外执行统计信息，例如上面例子中的 Sort 及 Hash 。除第一个没有 -> 的行之外的任何行都是诸如此类的信息，因此查询的结构是：

每个树分支代表子动作，从里到外以确定哪个是“第一个”发生（尽管同一级别的节点顺序可能不同）。

在 tenk_unique1 索引上执行的第一个 *** 作是 Bitmap Index Scan ：

这对应于SQL WHERE t1unique1 < 100 。Postgres查找与条件 unique1 < 100 匹配的行位置。此处不会返回行数据本身。成本估算 (cost=000504 rows=101 width=0) 意味着Postgres预期将“花费” 任意计算单位的 504 来找到这些行。000是此节点开始工作的成本（在这种情况下，即为查询的启动时间）。 rows 是此索引扫描将返回的预估行数， width 是这些返回行的预估大小（以字节为单位）（0是因为这里只关心位置，而不是行数据的内容）。

因为使用了 ANALYZE 选项运行 EXPLAIN ，所以查询被实际执行并捕获了计时信息。 (actual time=00490049 rows=100 loops=1) 表示索引扫描执行了1次（ loops 值），结果返回了100行，实际时间是0 如果节点执行了多次，实际时间是每次迭代的平均值，可以将该值乘以循环次数以获取实际时间。基于成本的最小/最大时间的概念，范围值也可能会有所不同。通过这些值，我们可以为该查询生成一个成本比率，每个成本单位为0049ms / 504单位≈001ms/单位。

索引扫描的结果将传递给 Bitmap Heap Scan *** 作。在此节点中，Postgres将获取别名为t1的tenk1表中行的位置，根据 unique1 < 100 条件筛选并获取行。

当乘以之前计算的001值时，我们可以得到成本预期的大概时间（22920 - 507）001≈224ms，同时每行实际时间为除以4后的结果：0526ms。这可能是因为成本估算是取的上限而不是取所有需读取的行，也或者因为Recheck条件总是生效。

和表顺序读取行（a Seq Scan ）相比， Bitmap Index Scan 和 Bitmap Heap Scan 关联 *** 作成本要昂贵得多，但是因为在这种情况下只需要访问相对较少的行，所以关联 *** 作最终会变得更快。通过在获取行之前将行按照物理顺序排序来进一步加速，这会将单独获取的成本降到最低。节点名称中的“Bitmap”完成了排序 *** 作。

表扫描的结果（tenk1表中满足 unique1 < 100 条件的那些行）将在读取时被插入到内存的哈希表中。正如我们从成本中看到的那样，这根本不需要时间。

哈希节点包括散列桶(hash buckets)和批次数(batches)相关的信息，以及内存使用峰值情况。如果批次> 1，则还会包括未显示的磁盘使用信息。内存占用在100行 244字节= 244 kB时是有意义的，它非常接近28kB，我们假定这是哈希键本身所占用的内存。

接下来，Postgres从别名为t2的tenk2表读取所有的10000行，并根据tenk1表行的Hash检查它们。散列连接意味着将一个表的行输入到内存中的散列（先前的 *** 作中已构建），之后扫描另一个表的行，并根据散列表探测其值以进行匹配。在第二行可以看到“匹配”的条件， Hash Cond: (t2unique2 = t1unique2) 。请注意，因为查询是从tenk1和tenk2中选择所有值，所以在散列连接期间每行的宽度加倍。

现在已经收集了满足条件的所有行，可以对结果集进行排序 Sort Key: t1fivethous 。

Sort节点包含排序算法 quicksort 相关的信息 ，排序是在内存中还是在磁盘上完成（这将极大地影响速度），以及排序所需的内存/磁盘空间量。

熟悉如何解读查询计划会非常有助于优化查询。例如，Seq Scan节点通常表示添加索引的必要性，读取速度可能要快得多。

翻译并编辑，原文出处： >

select from (select 除了时间所有字段写出来,to_date('COMMENTTIME','yyyy-mm-dd hh24:mi:ss') time from T_SCHOOL_RECORDCOMMENT where RECORDID = :p_cid) order by time desc;

这样试试，但是你的时间字段用该保存为date类型，这样好像很麻烦。。

以上就是关于postgresql怎么查看表的创建时间全部的内容，包括:postgresql怎么查看表的创建时间、postgresql里面怎么得到两个日期相差多少秒，或者多少分钟、PostgreSQL学习系列—EXPLAIN ANALYZE查询计划解读等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！