Home | 簡體中文 | 繁體中文 | 雜文 | 打賞(Donations) | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 知乎專欄 | Search | Email

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. 當前日期/時間

3.1.1   當前日期

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

netkiller=>

3.1.2   當前時間

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

netkiller=>

3.1.3   當前日期時間

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

netkiller=>
3.1.4   除去時區

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() / clock_timestamp() 函數

select now();			
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';			
			

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)