7.5. Function

7.5. Function
上一頁	第 7 章 SQL	下一頁

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)

上一頁	上一級	下一頁
7.4. returning	起始頁	第 8 章事務處理與鎖