请教pgsql中时间、统计相关的函数，如date_format、grouping...以及如何优化配置？

[「已注销」]

最近从mysql转到pgsql了，但网上能搜到资料比较匮乏~！ 想请问下关于pgsql中 时间、统计相关的案例

如：mysql中的 date_format、day、weekday、date_part、时间差之类
还有形如：mysql中的 with rollup、mssql中的 grouping
另外想请教下pgsql如何做配置方面的优化设置啊？！
麻烦pgsql的高手帮解惑下吧！多谢！

[ACMAIN_CHM]

PGSQL的帮助手册官网就有啊。 其中有它的时间函数列表， 9.9. Date/Time Functions and Operators Table 9.27, “Date/Time Functions” shows the available functions for date/time value processing, with details appearing in the following subsections. Table 9.26, “Date/Time Operators” illustrates the behaviors of the basic arithmetic operators (+, *, etc.). For formatting functions, refer to Section 9.8, “Data Type Formatting Functions”. You should be familiar with the background information on date/time data types from Section 8.5, “Date/Time Types”. All the functions and operators described below that take time or timestamp inputs actually come in two variants: one that takes time with time zone or timestamp with time zone, and one that takes time without time zone or timestamp without time zone. For brevity, these variants are not shown separately. Also, the + and * operators come in commutative pairs (for example both date + integer and integer + date); we show only one of each such pair. Table 9.26. Date/Time Operators Operator Example Result + date '2001-09-28' + integer '7' date '2001-10-05' + date '2001-09-28' + interval '1 hour' timestamp '2001-09-28 01:00:00' + date '2001-09-28' + time '03:00' timestamp '2001-09-28 03:00:00' + interval '1 day' + interval '1 hour' interval '1 day 01:00:00' + timestamp '2001-09-28 01:00' + interval '23 hours' timestamp '2001-09-29 00:00:00' + time '01:00' + interval '3 hours' time '04:00:00' - - interval '23 hours' interval '-23:00:00' - date '2001-10-01' - date '2001-09-28' integer '3' (days) - date '2001-10-01' - integer '7' date '2001-09-24' - date '2001-09-28' - interval '1 hour' timestamp '2001-09-27 23:00:00' - time '05:00' - time '03:00' interval '02:00:00' - time '05:00' - interval '2 hours' time '03:00:00' - timestamp '2001-09-28 23:00' - interval '23 hours' timestamp '2001-09-28 00:00:00' - interval '1 day' - interval '1 hour' interval '1 day -01:00:00' - timestamp '2001-09-29 03:00' - timestamp '2001-09-27 12:00' interval '1 day 15:00:00' * 900 * interval '1 second' interval '00:15:00' * 21 * interval '1 day' interval '21 days' * double precision '3.5' * interval '1 hour' interval '03:30:00' / interval '1 hour' / double precision '1.5' interval '00:40:00' Table 9.27. Date/Time Functions Function Return Type Description Example Result age(timestamp, timestamp) interval Subtract arguments, producing a “symbolic” result that uses years and months age(timestamp '2001-04-10', timestamp '1957-06-13') 43 years 9 mons 27 days age(timestamp) interval Subtract from current_date (at midnight) age(timestamp '1957-06-13') 43 years 8 mons 3 days clock_timestamp() timestamp with time zone Current date and time (changes during statement execution); see Section 9.9.4, “Current Date/Time” current_date date Current date; see Section 9.9.4, “Current Date/Time” current_time time with time zone Current time of day; see Section 9.9.4, “Current Date/Time” current_timestamp timestamp with time zone Current date and time (start of current transaction); see Section 9.9.4, “Current Date/Time” date_part(text, timestamp) double precision Get subfield (equivalent to extract); see Section 9.9.1, “EXTRACT, date_part” date_part('hour', timestamp '2001-02-16 20:38:40') 20 date_part(text, interval) double precision Get subfield (equivalent to extract); see Section 9.9.1, “EXTRACT, date_part” date_part('month', interval '2 years 3 months') 3 date_trunc(text, timestamp) timestamp Truncate to specified precision; see also Section 9.9.2, “date_trunc” date_trunc('hour', timestamp '2001-02-16 20:38:40') 2001-02-16 20:00:00 extract(field from timestamp) double precision Get subfield; see Section 9.9.1, “EXTRACT, date_part” extract(hour from timestamp '2001-02-16 20:38:40') 20 extract(field from interval) double precision Get subfield; see Section 9.9.1, “EXTRACT, date_part” extract(month from interval '2 years 3 months') 3 isfinite(date) boolean Test for finite date (not +/-infinity) isfinite(date '2001-02-16') true isfinite(timestamp) boolean Test for finite time stamp (not +/-infinity) isfinite(timestamp '2001-02-16 21:28:30') true isfinite(interval) boolean Test for finite interval isfinite(interval '4 hours') true justify_days(interval) interval Adjust interval so 30-day time periods are represented as months justify_days(interval '35 days') 1 mon 5 days justify_hours(interval) interval Adjust interval so 24-hour time periods are represented as days justify_hours(interval '27 hours') 1 day 03:00:00 justify_interval(interval) interval Adjust interval using justify_days and justify_hours, with additional sign adjustments justify_interval(interval '1 mon -1 hour') 29 days 23:00:00 localtime time Current time of day; see Section 9.9.4, “Current Date/Time” localtimestamp timestamp Current date and time (start of current transaction); see Section 9.9.4, “Current Date/Time” now() timestamp with time zone Current date and time (start of current transaction); see Section 9.9.4, “Current Date/Time” statement_timestamp() timestamp with time zone Current date and time (start of current statement); see Section 9.9.4, “Current Date/Time” timeofday() text Current date and time (like clock_timestamp, but as a text string); see Section 9.9.4, “Current Date/Time” transaction_timestamp() timestamp with time zone Current date and time (start of current transaction); see Section 9.9.4, “Current Date/Time” In addition to these functions, the SQL OVERLAPS operator is supported: (start1, end1) OVERLAPS (start2, end2) (start1, length1) OVERLAPS (start2, length2) This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints can be specified as pairs of dates, times, or time stamps; or as a date, time, or time stamp followed by an interval. SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); Result: true SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30'); Result: false When adding an interval value to (or subtracting an interval value from) a timestamp with time zone value, the days component advances (or decrements) the date of the timestamp with time zone by the indicated number of days. Across daylight saving time changes (with

[「已注销」]

英文太菜了，多谢~

[clqaitxp]

1.获取系统时间函数 select now(); --2012-05-12 18:51:59.562+08 select current_timestamp; --2012-05-12 18:52:12.062+08 select current_date; --2012-05-12 select current_time; --18:53:23.234+08 2.时间的计算 --使用interval select now()+interval '2 day'; --2012-05-14 20:05:32.796+08 2天后 select now()-interval '2 day'; --2012-05-10 20:07:23.265+08 2天前 select now()+interval '2 hour'; --2012-05-12 22:06:38.375+08 2小时后 .... interval可以不写，其值可以是 Abbreviation Meaning Y Years M Months (in the date part) W Weeks D Days H Hours M Minutes (in the time part) 3.时间的截取 --使用extract extract(interval,timestamp); select extract(year from now()); --2012 select extract(mon from now()); --5 5月份 ... interval值参考上面 4.时间的转换 select timestamp '2012-05-12 18:54:54'; --2012-05-12 18:54:54 select date '2012-05-12 18:54:54'; --2012-05-12 select time '2012-05-12 18:54:54'; --18:54:54 select TIMESTAMP WITH TIME ZONE '2012-05-12 18:54:54' --2012-05-12 18:54:54+08 --与unix时间戳的转换 SELECT TIMESTAMP 'epoch' + 1341174767 * INTERVAL '1 second'; --2012-07-01 20:32:47 参考：http://my.oschina.net/Kenyon/blog/57188

[「已注销」]

引用 3 楼 clqaitxp 的回复:

1.获取系统时间函数 select now(); --2012-05-12 18:51:59.562+08 select current_timestamp; --2012-05-12 18:52:12.062+08 select current_date; --2012-05-12 select current……

非常感谢！