PostgreSQL日期時間DATE/TIME

我們已經討論過的章節中數據類型的日期/時間（DATE/TIME）數據類型。現在讓我們來看日期/時間運算符和函數。

下表列出了基本算術操作符的行為：

運算符 實例 結果 + 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)

函數 描述 AGE(timestamp, 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');

以上PostgreSQL的表會產生以下結果：

           age
-------------------------
 43 years 9 mons 27 days

功能AGE（timestamp ）的例子是：

testdb=# select age(timestamp '1957-06-13');

Above PostgreSQL statement will produce following result:

           age
--------------------------
 55 years 10 mons 22 days

CURRENT DATE/TIME()

PostgreSQL提供了返回值的函數的當前日期和時間相關。以下是一些函數：

函數 描述 CURRENT_DATE Delivers current date. CURRENT_TIME Deliver values with time zone. CURRENT_TIMESTAMP Deliver values with time zone. 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) Optionally takes a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. LOCALTIME Deliver values without time zone. LOCALTIMESTAMP Deliver values without time zone. LOCALTIME(precision) Optionally take a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field. LOCALTIMESTAMP(precision) Optionally take a precision parameter, which causes the result to be rounded to that many fractional digits in the seconds field.

例子使用的功能表所示：

testdb=# SELECT CURRENT_TIME;
       timetz
--------------------
 08:01:34.656+05:30
(1 row)


testdb=# SELECT CURRENT_DATE;
    date
------------
 2013-05-05
(1 row)


testdb=# SELECT CURRENT_TIMESTAMP;
              now
-------------------------------
 2013-05-05 08:01:45.375+05:30
(1 row)


testdb=# SELECT CURRENT_TIMESTAMP(2);
         timestamptz
------------------------------
 2013-05-05 08:01:50.89+05:30
(1 row)


testdb=# SELECT LOCALTIMESTAMP;
       timestamp
------------------------
 2013-05-05 08:01:55.75
(1 row)

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() It returns the actual current time, but as a formatted text string rather than a timestamp with time zone value. now() It is a traditional PostgreSQL equivalent to transaction_timestamp().

DATE_PART(text, timestamp), 
DATE_PART(text, interval), 
DATE_TRUNC(text, timestamp)

Function Description DATE_PART('field', source)

These functions get the subfields. The field parameter needs to be a string value, not a name.

.

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', source)

This function is conceptually similar to the trunc function for numbers. sourceis a value expression of type timestamp or interval. field selects to which precision to truncate the input value. The return value is of type timestamp orinterval.

The valid values for field are : microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium

Following are examples for DATE_PART('field', source) functions:

testdb=# SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
 date_part
-----------
        16
(1 row)


testdb=# SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
 date_part
-----------
         4
(1 row)

Following are examples for DATE_TRUNC('field', source) functions:

testdb=# SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
     date_trunc
---------------------
 2001-02-16 20:00:00
(1 row)


testdb=# SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
     date_trunc
---------------------
 2001-01-01 00:00:00
(1 row)

EXTRACT(field from timestamp), 
EXTRACT(field from interval)

The EXTRACT(field FROM source) function retrieves subfields such as year or hour from date/time values. source must be a value expression of type timestamp, time, or interval. field is an identifier or string that selects what field to extract from the source value. The EXTRACT function returns values of type double precision.

The following are valid field names (similar to DATE_PART function field names):century, day, decade, dow, doy, epoch, hour, isodow, isoyear, microseconds, millennium, milliseconds, minute, month, quarter, second, timezone, timezone_hour, timezone_minute, week, year.

以下是EXTRACT('field', source) 函數的例子:

testdb=# SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
 date_part
-----------
        20
(1 row)


testdb=# SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part
-----------
        16
(1 row)

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()函數的例子：

testdb=# SELECT isfinite(date '2001-02-16');
 isfinite
----------
 t
(1 row)


testdb=# SELECT isfinite(timestamp '2001-02-16 21:28:30');
 isfinite
----------
 t
(1 row)


testdb=# SELECT isfinite(interval '4 hours');
 isfinite
----------
 t
(1 row)

JUSTIFY_DAYS(interval),
JUSTIFY_HOURS(interval), 
JUSTIFY_INTERVAL(interval)

函數 描述 JUSTIFY_DAYS(interval) Adjusts interval so 30-day time periods are represented as months. Return the interval type JUSTIFY_HOURS(interval) Adjusts interval so 24-hour time periods are represented as days. Return the interval type JUSTIFY_INTERVAL(interval) Adjusts interval using JUSTIFY_DAYS and JUSTIFY_HOURS, with additional sign adjustments. Return the interval type

以下是為ISFINITE（）函數的例子：

testdb=# SELECT justify_days(interval '35 days');
 justify_days
--------------
 1 mon 5 days
(1 row)


testdb=# SELECT justify_hours(interval '27 hours');
 justify_hours
----------------
 1 day 03:00:00
(1 row)


testdb=# SELECT justify_interval(interval '1 mon -1 hour');
 justify_interval
------------------
 29 days 23:00:00
(1 row)