现在位置：首页 > 数据库 > PostgreSQL > PostgreSQL日期时间DATE/TIME

PostgreSQL日期时间DATE/TIME

来源：原创文章由易百更新版本浏览：人次

我们已经讨论过的章节中数据类型的日期/时间（DATE/TIME）数据类型。现在让我们来看日期/时间运算符和函数。

www.yiibai.com

下表列出了基本算术操作符的行为：

yiibai.com

运算符	实例	结果
+	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'

以下是所有重要的日期和时间相关的可用功能列表中。 yiibai.com

函数	描述
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

yiibai.com

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)是：

www.yiibai.com

testdb=# SELECT AGE(timestamp '2001-04-10', timestamp '1957-06-13'); yiibai.com

以上PostgreSQL的表会产生以下结果： www.yiibai.com

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

yiibai.com

功能AGE（timestamp ）的例子是： yiibai.com

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

Above PostgreSQL statement will produce following result: yiibai.com

           age
--------------------------
 55 years 10 mons 22 days
 yiibai.com

CURRENT DATE/TIME()

PostgreSQL提供了返回值的函数的当前日期和时间相关。以下是一些函数：

yiibai.com

函数	描述
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.

例子使用的功能表所示：

www.yiibai.com

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) yiibai.com

PostgreSQL还提供了当前语句的开始时间，以及当前的实际时间的瞬间，该函数被调用的函数返回。这些函数包括： yiibai.com

函数	描述
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().

yiibai.com

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. 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. www.yiibai.com
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. yiibai.com The valid values for field are : microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, millennium www.yiibai.com

Function

Description

DATE_PART('field', source)

These functions get the subfields. The field parameter 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.

www.yiibai.com

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.

yiibai.com

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

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

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) yiibai.com

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

yiibai.com

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) yiibai.com

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.

yiibai.com

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. www.yiibai.com

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

yiibai.com

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

www.yiibai.com

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) yiibai.com

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（）函数的例子：

www.yiibai.com

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) www.yiibai.com

本站文章除注明转载外，均为本站原创或编译
欢迎任何形式的转载，但请务必注明出处，尊重他人劳动，传播学习教程；
转载请注明：文章转载自：易百教程 [http://www.yiibai.com]
本文标题：PostgreSQL日期时间DATE/TIME
转载请保留原文链接:http://www.yiibai.com/html/postgresql/2013/080783.html

上一篇：PostgreSQL权限下一篇：PostgreSQL函数

易百教程

教程目录

高级教程

关于我们

PostgreSQL日期时间DATE/TIME

AGE(timestamp, timestamp),
AGE(timestamp)

CURRENT DATE/TIME()

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

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

ISFINITE(date),
ISFINITE(timestamp),
ISFINITE(interval)

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

随机推荐

相关文章

易百教程

教程目录

高级教程

关于我们

PostgreSQL日期时间DATE/TIME

AGE(timestamp, timestamp), AGE(timestamp)

CURRENT DATE/TIME()

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

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

ISFINITE(date), ISFINITE(timestamp), ISFINITE(interval)

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

随机推荐

相关文章

AGE(timestamp, timestamp),
AGE(timestamp)

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

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

ISFINITE(date),
ISFINITE(timestamp),
ISFINITE(interval)

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