Question

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. 当前日期/时间

当前日期 current_date

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

netkiller=>			

当前时间 current_time

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

netkiller=>			

当前日期时间 current_timestamp

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

永久生效到pgdata下的postgresql.conf配置里修改两项

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)