【PostgreSQL-9.6.3】extract函数
extract函数格式:
extract (field from source)
extract函数是从日期或者时间数值里面抽取子域,比如年、月、日等。source必须是timestamp、time、interval类型的值表达式。field是一个标识符或字符串,是从源数据中的抽取的域。
1. century (世纪) test=# select extract (century from timestamp '2017-07-31 22:18:00'); date_part ----------- 21 (1 row) 2. year (年) test=# select extract (year from timestamp '2017-07-31 22:18:00'); date_part ----------- 2017 (1 row) 3. decade (得到年份除10的值) test=# select extract (decade from timestamp '2017-07-31 22:18:00'); date_part ----------- 201 (1 row) 4. millennium(得到第几个千年,0-1000第一个,1001-2000第二个,2001-3000第三个) test=# select extract (millennium from timestamp '2017-07-31 22:18:00'); date_part ----------- 3 (1 row) 5. quarter (季度) test=# select extract (quarter from timestamp '2017-07-31 22:18:00'); date_part ----------- 3 (1 row) 6. month (月份) test=# select extract (month from timestamp '2017-07-31 22:18:00'); date_part ----------- 7 (1 row) test=# select extract (month from interval '2 years 11 months'); date_part ----------- 11 (1 row) 7. week (返回当前是几年的第几个周) test=# select extract (week from timestamp '2017-07-31 22:18:00'); date_part ----------- 31 (1 row) 8. dow (返回当前日期是周几,周日:0,周一:1,周二:2,...) test=# select extract (dow from timestamp '2017-07-31 22:18:00'); date_part ----------- 1 (1 row) 9. day (本月的第几天) test=# select extract (day from timestamp '2017-07-31 22:18:00'); date_part ----------- 31 (1 row) 10. doy (本年的第几天) test=# select extract (doy from timestamp '2017-07-31 22:18:00'); date_part ----------- 212 (1 row) 11. hour (小时) test=# select extract (hour from timestamp '2017-07-31 22:18:00'); date_part ----------- 22 (1 row) 12. min (得到时间中的分钟) test=# select extract (min from timestamp '2017-07-31 22:18:00'); date_part ----------- 18 (1 row) 13. sec (返回时间中的秒) test=# select extract (sec from timestamp '2017-07-31 22:18:00'); date_part ----------- 0 (1 row)