postgresql时间的简单介绍

postgresql时间差计算

记一笔postgresql在时间计算上的方法。

君山ssl适用于网站、小程序/APP、API接口等需要进行数据传输应用场景，ssl证书未来市场广阔！成为创新互联的ssl证书销售渠道，可以享受市场价格4-6折优惠！如果有意向欢迎电话联系或者加微信：18982081108（备注：SSL证书合作）期待与您的合作！

因此，可以通过date_part计算两个时间相差几天，几分钟，几秒钟等。

在计算最近几个月，最近几天，最近几个星期的数据时用到

例如：求最近3个月创建的销售量

时间单位：

year :年

week ：该天在所在的年份里是第几周

timezone_minute：时区偏移量的分钟部分

timezone_hour：时区偏移量的小时部分

timezone：与UTC的时区偏移量，以秒记。正数对应 UTC 东边的时区，负数对应 UTC 西边的时区

second ：秒

quarter：日期中年所在季度(1-4)

month：月(0-11)

minute：分钟(0-59)

milliseconds：

isodow：周中的第几天 [1-7] 星期一：1) 星期天：(7)

dow：周中天的索引(0-6 ；星期天是 0)

doy：一年的第几天(1-365/366)

hour：小时(0-23)

day: 天(1-31)

postgresql中如何修改日期跟时间，比方说2011-11-1 12:10:05 修改为 2012-5-2 11:05:01

update 表名 set 时间字段='2012-5-2 11:05:01' where 时间字段='2011-11-1 12:10:05'

注意，你的Windows的时间日期格式要和上面的 yyyy-mm-dd hh:mm:ss 一致。不一致，字符串不一定能解释成功。要在控制面板的区域语言选项里的时间日期格式那里设好了。有些机器是 2011.11.1 之类的，要改过来

postgresql怎么查看表的创建时间

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

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

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

--1.1 创建表并插入数据

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 "francs.test_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)

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

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

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。

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

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

oid | datname | dattablespace

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

16386 | francs | 16385

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

--1.4 查找 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 中的一些逻辑结构关系，如果自己对环境比较熟悉，可以直接定位到

哪个表空间。

--1.5 查询表空间 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。

--1.6 进入数据目录

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

[postgres@redhat6 tbs_francs]$ ll

total 4.0K

drwx------. 4 postgres postgres 4.0K May 22 10:35 PG_9.2_201204301

[postgres@redhat6 tbs_francs]$ cd PG_9.2_201204301/

[postgres@redhat6 PG_9.2_201204301]$ ll

total 16K

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

drwx------. 2 postgres postgres 4.0K May 22 10:37 pgsql_tmp

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

表 test_ctime 的 relfilenode 值为 24650

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

[postgres@redhat6 16386]$ ll 24650

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

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

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

--1.8 cluster 表

francs= cluster verbose test_ctime using test_ctime_pkey;

INFO: clustering "francs.test_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 0.00s/0.03u sec elapsed 0.08 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

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

[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 的初始创建时间。

--1.10 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

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

[postgres@redhat6 16386]$ ll 24659

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

在PostgreSQL 和 Hive中生成日期序列

## 在PostgreSQL 和 Hive中生成日期序列

### Postgresql实现日期序列

在postgresql中，有 generate_series(start_date, end_date, interval)函数来生成日期序列

```

select date(day) as day

from generate_series('2020-05-22'::timestamp, current_date, '1 day'::interval) as day

```

### Hive实现指定指定开始结束日期的日期序列

在Hive中，可以借助 posexplode(list)、datediff(end_date, start_date)来实现。

首先创建一个表名为calender，字段为day，类型为date，存入一个日期数值作为开始日期，比如2014-01-01。

```

CREATE TABLE default.calender (day DATE);

INSERT INTO TABLE default.calender VALUES(to_date('2014-01-01T00:00'));

```

借助 `datediff(end_date, start_date)`、`space(int_count)`、`split(list,seperator)`、`posexplode(list) `生成n个空格，然后split成list，posexplode将行转多列，同时返回index和value。

```

select date_add(day,idx) as new_day from default.calender

lateral view posexplode( split( space( datediff( current_date, to_date('2014-01-01T00:00:00') ) ), ' ')  ) tt as idx, v;

```

中间过程解释：

比如：

```

select datediff('2020-06-30','2020-05-1'); -- 60

select split(space(datediff('2020-06-30','2020-05-1')),' ') -- 生成60个空格，然后split成list

```

index | value

--- | ---

0 | ' '

1 | ' '

2 | ' '

... | ...

59| ' '

#### Hive在日期序列表添加星期几

新增一列存放星期几

```

ALTER TABLE default.calender ADD COLUMNS(weekday STRING);

```

借助函数`datediff`，`pmod`就可以实现

datediff 是两个日期相减的函数

语法：`datediff(string enddate, string startdate)`

返回值: int

说明: 返回两个时间参数的相差天数。

pmod 是正取余函数

语法: `pmod(int a, int b),pmod(double a, double b)`

返回值: int double

说明: 返回正的a除以b的余数

选取一个日期为星期日的日期作为参照日期，这里我选取了2013-12-29

`pmod(datediff( date, '2012-01-01'), 7)  `

返回值：int 0-6

0-6分别表示星期日-星期六

```

INSERT OVERWRITE TABLE default.calender

select date_add(day,idx) as `date`,

-- 0-6 分别代表星期日-星期六

case pmod(datediff(date_add(day,idx), to_date('2013-12-29T00:00:00')), 7)

    when 0 then '星期日'

    when 1 then '星期一'

    when 2 then '星期二'

    when 3 then '星期三'

    when 4 then '星期四'

    when 5 then '星期五'

    when 6 then '星期六'

END as weekday

from default.calender

lateral view posexplode( split( space( datediff( to_date('2030-01-01T00:00:00') , to_date('2014-01-01T00:00:00') ) ), ' ')  ) tt as idx, v;

```

### 补充：Hive实现缺失日期的补全

在统计一些daily的metrics的时候，通常使用group by，往往会存在某些日期没有数据从而导致最后的结果表的日期其实不是连续的齐全序列。

比如：

store_id | date | count

---- | ---- | -----

1 | 2020-04-02 | 45

2 | 2020-04-02 | 10

2 | 2020-04-03 | 10

1 | 2020-04-05 | 50

2 | 2020-04-06 | 10

1 | 2020-04-08 | 50

... | ...... | ....

针对这种情况，需要进行以下步骤拆解：

1. 按照store_id进行聚合，找出最小、最大日期

2. 此时，基于步骤1的CTE表进行基于每个store_id的最小、最大日期的日期序列补全

```

select t.store_id, date_add(t.min_date, idx) as `date`

from store_with_min_max_usage_date t

lateral view posexplode(split(space(datediff(t.max_date, t.min_date)),' ')) pe as idx, v

```

3. 将步骤2的结果与之前的agg聚合结果表进行`left join`，对`NULL`用`COALESCE(v, 0)`进行缺失值替换。

postgresql 求两个时间差 （相差天数,timestamp类型）

如果是按相差24小时就算1天的话，直接用两个timestamp值相减得到一个interval值，然后获得此interval值的天数部分即可，如下:

select date_part('day', '2015-01-15 17:05'::timestamp - '2013-01-14 16:05'::timestamp);

如果要按timestamp的日期部分做相差天数，则可以转成date值，然后直接相减，如下:

select ('2015-01-15 17:05'::timestamp)::date - ('2015-01-14 19:05'::timestamp)::date;