select now()::date
select date_part('day',now()::timestamp - '2016-1-11'::timestamp)
下表列出了基本算术操作符的行为：
| 运算符 | 实例 | 结果 |
|---|---|---|
| + | 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' |
以下是所有重要的日期和时间相关的可用功能列表中。
| 描述 | |
|---|---|
| AGE() | Subtract arguments |
| CURRENT DATE/TIME() | Current date and time |
| DATE_PART() | Get subfield (equivalent to extract) |
| EXTRACT() | Get subfield |
| ISFINITE() | Test for finite date,time and interval (not +/-infinity) |
| JUSTIFY | Adjust interval |
AGE(timestamp,timestamp),
AGE(timestamp)
| When invoked with the TIMESTAMP form of the second argument,AGE() subtract arguments,producing a "symbolic" result that uses years and months and is of type INTERVAL. | |
| AGE(timestamp) | When invoked with only the TIMESTAMP as argument,AGE() subtracts from the current_date (at midnight). |
例如时代功能AGE(timestamp,timestamp)是：
testdb=# SELECT AGE(timestamp '2001-04-10', timestamp '1957-06-13'); yiibai.com
以上Postgresql的表会产生以下结果：
age ------------------------- 43 years 9 mons 27 days
功能AGE（timestamp ）的例子是：
=# select age Above Postgresql statement will produce following result:age -------------------------- 55 years 10 mons 22 days yiibai.com
CURRENT DATE/TIME()Postgresql提供了返回值的函数的当前日期和时间相关。以下是一些函数：
CURRENT_DATE
Delivers current date. CURRENT_TIME Deliver values with time zone. CURRENT_TIMESTAMP CURRENT_TIME(precision) Optionally takes a precision parameter,which causes the result to be rounded to that many fractional digits in the seconds field. CURRENT_TIMESTAMP(precision) LOCALTIME Deliver values without time zone. LOCALTIMESTAMP LOCALTIME(precision) Optionally take a precision parameter,214); padding:5px; margin:0px"> LOCALTIMESTAMP(precision) 例子使用的功能表所示： =# SELECT CURRENT_TIME; timetz -------------------- 08:0134.656+0530 (1 row) testdb=# SELECT CURRENT_DATE; date ------------ 2013-05 =# SELECT CURRENT_TIMESTAMP; now ------------------------------- 05 45.3752); timestamptz ------------------------------ 50.89=# SELECT LOCALTIMESTAMP; timestamp ------------------------ 55.75 )
Postgresql还提供了当前语句的开始时间，以及当前的实际时间的瞬间，该函数被调用的函数返回。这些函数包括：
transaction_timestamp()
它相当于CURRENT_TIMESTAMP，但被命名为清楚地反映它返回什么。 statement_timestamp() 它返回当前语句的开始时间。 clock_timestamp() It returns the actual current time,and therefore its value changes even within a single sql command. timeofday() now() It is a traditional Postgresql equivalent to transaction_timestamp().
DATE_PART(text,
DATE_PART(text,interval),
DATE_TRUNC(text,timestamp)Function
Description DATE_PART('field',source) These functions get the subfields. Thefieldparameter needs to be a string value,not a name.www.yiibai.com
.The valid field names are:century,day,decade,dow,doy,epoch,hour,isodow,isoyear,microseconds,millennium,milliseconds,minute,month,quarter,second,timezone,timezone_hour,timezone_minute,week,year.
DATE_TRUNC('field',214); padding:5px; margin:0px"> This function is conceptually similar to thetruncfunction for numbers.sourceis a value expression of type timestamp or interval.fieldselects to which precision to truncate the input value. The return value is of typetimestamporinterval.
The valid values forfieldare :microseconds,year,century,millennium
Following are examples for DATE_PART('field',source) functions:
=# SELECT date_part('day' TIMESTAMP '2001-02-16 20:38:40'); date_part ----------- 16 'hour' INTERVAL '4 hours 3 minutes'----------- 4 Following are examples for DATE_TRUNC('field',source) functions:=#
SELECT date_trunc); date_trunc --------------------- 20010216 200000 'year'01 )
EXTRACT(field from timestamp),
EXTRACT(field from interval)TheEXTRACT(field FROM source)function retrieves subfields such as year or hour from date/time values.sourcemust be a value expression of typetimestamp,time,or interval.fieldis an identifier or string that selects what field to extract from the source value. The EXTRACT function returns values of typedouble precision.
The following are valid field names (similar to DATE_PART function field names):century,year.
以下是EXTRACT('field',source) 函数的例子:
=# SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13'20 (DAY FROM TIMESTAMP ) yiibai.com
ISFINITE(date),
ISFINITE(timestamp),
ISFINITE(interval)ISFINITE(date)
Tests for finite date. ISFINITE(timestamp) Tests for finite time stamp. ISFINITE(interval) Tests for finite interval. 以下是为ISFINITE()函数的例子：
=# SELECT isfinite(date '2001-02-16'); isfinite ---------- t '2001-02-16 21:28:30'(interval '4 hours' JUSTIFY_DAYS(interval),
JUSTIFY_HOURS(interval),
JUSTIFY_INTERVAL(interval)JUSTIFY_DAYS(interval)
Adjusts interval so 30-day time periods are represented as months. Return theintervaltype JUSTIFY_HOURS(interval) Adjusts interval so 24-hour time periods are represented as days. Return theintervaltype JUSTIFY_INTERVAL(interval) Adjusts interval using JUSTIFY_DAYS and JUSTIFY_HOURS,with additional sign adjustments. Return theintervaltype 以下是为ISFINITE（）函数的例子：
=#
SELECT justify_days'35 days'); justify_days -------------- 1 mon 5 days =# SELECT justify_hours'27 hours'); justify_hours ---------------- 1 day 03=# SELECT justify_interval'1 mon -1 hour'); justify_interval ------------------ 29 days 23)猜你在找的Postgre SQL相关文章
