david=# select extract(year from now()); date_part ----------- 2013 (1 row)
david=# 3.2 取月份 david=# select extract(month from now()); date_part ----------- 4 (1 row)
david=# david=# select extract(day from timestamp '2013-04-13'); date_part ----------- 13 (1 row) david=# david=# SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute'); date_part ----------- 40 (1 row) david=# 3.3 查看今天是一年中的第幾天 david=# select extract(doy from now()); date_part ----------- 102 (1 row)
david=# 3.4 查看現在距1970-01-01 00:00:00 UTC 的秒數 david=# select extract(epoch from now()); date_part ------------------ 1365755907.94474 (1 row)
david=# 3.5 把epoch 值轉換回時間戳記 david=# SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 1369755555 * INTERVAL '1 second'; ?column? ------------------------ 2013-05-28 23:39:15+08 (1 row)
david=# --取day skytf=> select extract(day from now()); date_part ----------- 3 (1 row)
skytf=> select extract(day from timestamp '2011-06-03'); date_part ----------- 3 (1 row) skytf=> select timestamp '2011-06-03'; timestamp --------------------- 2011-06-03 00:00:00 (1 row) --取小時 skytf=> select extract (hour from now()); date_part ----------- 14 (1 row)
--取分鐘 skytf=> select extract (minute from now()); date_part ----------- 59 (1 row)
--取秒 skytf=> select extract (second from now()); date_part ----------- 46.039333 (1 row)
--取所在哪個星期 skytf=> select extract (week from now()); date_part ----------- 22 (1 row)
|