Home | 简体中文 | 繁体中文 | 杂文 | 打赏(Donations) | Github | OSChina 博客 | 云社区 | 云栖社区 | Facebook | Linkedin | 知乎专栏 | 视频教程 | About

## 7.5. Function

### 7.5.1. generate_series

test=# select generate_series(1,10) as id;
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)

### 7.5.2. 日期/时间

#### 7.5.2.1. Date/Time Operators

select now() + interval '3 day';
select now() - interval '3 day';

select now() + interval '1 hour';
select now() - interval '1 hour';

select now() + interval '10 minutes';
select now() - interval '10 minutes';

select now() + interval '5 days 3 hours';
select now() + interval '5 days' + interval '3 hours';

#### 7.5.2.2. 当前日期/时间

netkiller=> select current_date;
date
------------
2003-11-28
(1 row)

netkiller=>

netkiller=> select current_time;
timetz
--------------------
19:38:47.270235+08
(1 row)

netkiller=>

netkiller=> select current_timestamp;
timestamptz
-------------------------------
2003-11-28 19:39:25.548505+08
(1 row)
netkiller=>

1.         current_timestamp::timestamp (0)
2.         current_timestamp::timestamp (0) without time zone;

netkiller=> select current_timestamp::timestamp (0);
timestamp
---------------------
2003-11-28 19:44:33
(1 row)

netkiller=>
netkiller=> select current_timestamp::timestamp (0) without time zone;
timestamp
---------------------
2003-11-28 19:40:10
(1 row)

now()

select now();

clock_timestamp() 函数

SELECT clock_timestamp();

#### 7.5.2.3. 时间计算

3.1.5   计算时间差

netkiller=> select to_date('2003-12-2','YYYY-MM-DD')-to_date('2003-12-1','YYYY-MM-DD');
?column?
----------
1
(1 row)

netkiller=>
netkiller=> select to_date('2003-12-2','YYYY-MM-DD')-to_date('2003-10-2','YYYY-MM-DD');
?column?
----------
61
(1 row)
3.1.6   计算时间和

netkiller=> select to_date('2003-12-6','yyyy-mm-dd')+12 ;
?column?
------------
2003-12-18
(1 row)

netkiller=> select to_date('2003-12-6','yyyy-mm-dd')+20 ;
?column?
------------
2003-12-26
(1 row)

#### 7.5.2.4. to_char() / to_date()

to_char()

select count(*) as c, to_char(ctime, 'yyyy-mm') as dt from practice group by dt order by dt;

select count(*) as c, to_char(ctime, 'yyyy-mm-dd') as dt from practice group by dt order by dt;

select count(*) as c, to_char(ctime, 'yyyy-mm-dd hh') as dt from practice group by dt order by dt;

#### 7.5.2.5. EXTRACT, date_part

select extract (year from now());
select extract (month from now());
select extract (day from now());
select extract (day from timestamp '2013-06-03');
select extract (hour from now());
select extract (minute from now());
select extract (second from now());
select extract (week from now());

SELECT extract(century FROM now());

3.1.7   date_part

netkiller=> select date_part('epoch', '2003-12-3 10:20:30' - timestamp '2003-12-1 02:00:00') ;
date_part
-----------
202830
(1 row)

netkiller=> select date_part('day', '2003-12-3 10:20:30' - timestamp '2003-12-1 02:00:00') ;
date_part
-----------
2
(1 row)

netkiller=> select date_part('hour', '2003-12-3 10:20:30' - timestamp '2003-12-1 02:00:00') ;
date_part
-----------
8
(1 row)

netkiller=>

#### 7.5.2.6. date_trunc

select count(*) as c, date_trunc('day', ctime) as dt from practice group by dt;

#### 7.5.2.7. 延迟执行

pg_sleep(seconds)

SELECT pg_sleep(1.5);

#### 7.5.2.8. 时区

SELECT now() AT TIME ZONE 'GMT';
SELECT now() AT TIME ZONE 'GMT+8';

show time zone;
US/Pacific

set time zone 'PRC';

log_timezone = 'PRC'
timezone = 'PRC'

### 7.5.3. uuid

create extension "uuid-ossp";
create table products (product_id  uuid primary key default uuid_generate_v4());

### 7.5.4. tablefunc

#### http://www.postgresql.org/docs/9.1/static/tablefunc.html

\$ ls -1 /usr/pgsql-9.3/share/extension/tablefunc*
/usr/pgsql-9.3/share/extension/tablefunc--1.0.sql
/usr/pgsql-9.3/share/extension/tablefunc.control
/usr/pgsql-9.3/share/extension/tablefunc--unpackaged--1.0.sql

\$ psql test
psql (9.3.1)
Type "help" for help.

test=# create extension tablefunc;
CREATE EXTENSION
test=# \q

-- Table: account

-- DROP TABLE account;

CREATE TABLE account
(
id SERIAL NOT NULL,
name character varying(10) NOT NULL, -- 账号
balance money NOT NULL DEFAULT 0.00, -- 余额
datetime timestamp without time zone NOT NULL DEFAULT (now())::timestamp(0) without time zone,
CONSTRAINT account_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE account
OWNER TO dba;
COMMENT ON COLUMN account.name IS '账号';
COMMENT ON COLUMN account.balance IS '余额';

-- Index: account_name_idx

-- DROP INDEX account_name_idx;

CREATE INDEX account_name_idx
ON account
USING btree
(name COLLATE pg_catalog."default");

INSERT INTO account (id, name, balance, datetime) VALUES (1, 'Neo', '\$0.00', '2013-10-09 10:51:10');
INSERT INTO account (id, name, balance, datetime) VALUES (2, 'Neo', '\$12.60', '2013-10-09 10:51:22');
INSERT INTO account (id, name, balance, datetime) VALUES (3, 'Neo', '\$200.00', '2013-10-09 10:11:52');
INSERT INTO account (id, name, balance, datetime) VALUES (4, 'Neo', '-\$6.80', '2013-10-09 10:51:42');
INSERT INTO account (id, name, balance, datetime) VALUES (5, 'Neo', '\$100.00', '2013-10-09 10:52:49');
INSERT INTO account (id, name, balance, datetime) VALUES (6, 'Jerry', '\$200.00', '2013-10-09 10:56:35');
INSERT INTO account (id, name, balance, datetime) VALUES (7, 'Jerry', '\$50.45', '2013-10-09 10:57:23');
INSERT INTO account (id, name, balance, datetime) VALUES (8, 'Jerry', '\$75.50', '2013-10-09 10:57:31');
INSERT INTO account (id, name, balance, datetime) VALUES (9, 'Jerry', '-\$55.30', '2013-10-09 10:59:28');
INSERT INTO account (id, name, balance, datetime) VALUES (10, 'Jerry', '-\$200.00', '2013-10-09 10:59:44');
INSERT INTO account (id, name, balance, datetime) VALUES (11, 'Tom', '\$5.00', '2013-10-15 13:26:19');
INSERT INTO account (id, name, balance, datetime) VALUES (12, 'Neo', '\$50.60', '2013-10-15 13:26:34');
INSERT INTO account (id, name, balance, datetime) VALUES (13, 'Jerry', '\$62.08', '2013-10-15 13:26:51');
INSERT INTO account (id, name, balance, datetime) VALUES (14, 'Tom', '\$70.00', '2013-10-15 13:27:01');
INSERT INTO account (id, name, balance, datetime) VALUES (15, 'Neo', '-\$20.56', '2013-10-15 13:27:19');
INSERT INTO account (id, name, balance, datetime) VALUES (16, 'Tom', '\$70.00', '2013-10-16 13:27:01');
INSERT INTO account (id, name, balance, datetime) VALUES (17, 'Jerry', '\$70.00', '2013-10-16 13:27:01');
INSERT INTO account (id, name, balance, datetime) VALUES (18, 'Jerry', '-\$30.00', '2013-10-16 13:30:01');
INSERT INTO account (id, name, balance, datetime) VALUES (19, 'Neo', '-\$30.00', '2013-10-16 13:35:01');
INSERT INTO account (id, name, balance, datetime) VALUES (20, 'Tom', '-\$30.00', '2013-10-16 13:35:01');

SELECT * FROM crosstab('select datetime,name,balance from account order by 1,2','select name from account group by name order by 1')  AS account(datetime timestamp, Jerry character varying, Neo character varying, Tom character varying);

datetime       |  jerry   |   neo   |   tom
---------------------+----------+---------+---------
2013-10-09 10:11:52 |          | \$200.00 |
2013-10-09 10:51:10 |          | \$0.00   |
2013-10-09 10:51:22 |          | \$12.60  |
2013-10-09 10:51:42 |          | -\$6.80  |
2013-10-09 10:52:49 |          | \$100.00 |
2013-10-09 10:56:35 | \$200.00  |         |
2013-10-09 10:57:23 | \$50.45   |         |
2013-10-09 10:57:31 | \$75.50   |         |
2013-10-09 10:59:28 | -\$55.30  |         |
2013-10-09 10:59:44 | -\$200.00 |         |
2013-10-15 13:26:19 |          |         | \$5.00
2013-10-15 13:26:34 |          | \$50.60  |
2013-10-15 13:26:51 | \$62.08   |         |
2013-10-15 13:27:01 |          |         | \$70.00
2013-10-15 13:27:19 |          | -\$20.56 |
2013-10-16 13:27:01 | \$70.00   |         | \$70.00
2013-10-16 13:30:01 | -\$30.00  |         |
2013-10-16 13:35:01 |          | -\$30.00 | -\$30.00
(18 rows)