䯄婘嘉䘞饡釕 > 昄扞康 > PostgreSQL > PostgreSQL斖橘施閘DATE/TIME

PostgreSQL斖橘施閘DATE/TIME

準溊寘录桺䆹䫌悕垵幥敘桄䬽橸敟蓽庺渇

潏傸噾䂟螘螺誺䔇䆹誗婺昄扞䌂傋䔇斖橘/施閘DATE/TIME昄扞䌂傋㔗䯄婘螷潏傸準䩋斖橘/施閘誊䞖严启庘昄㔗

www.gitbook.net

婋臘彖庺庖嘺橸䞖橇淉嘩严䔇臯婺

gitbook.net

誊䞖严	垂冋	䂷悩
+	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'

傖婋滇欔橬麉襕䔇斖橘启施閘䕩噿䔇埇䫘媘脘彖臘婺㔗 gitbook.net

庘昄	柟誄
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

gitbook.net

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.gitbook.net

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

傖婪PostgreSQL䔇臘嚔库䫘傖婋䂷悩 www.gitbook.net

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

gitbook.net

媘脘AGEtimestamp 䔇冋床滇 gitbook.net

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

Above PostgreSQL statement will produce following result: gitbook.net

           age
--------------------------
 55 years 10 mons 22 days
 gitbook.net

CURRENT DATE/TIME()

PostgreSQL柊冕庖誫啂唚䔇庘昄䔇嘷嬉斖橘启施閘䕩噿㔗傖婋滇婔底庘昄

gitbook.net

庘昄	柟誄
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.gitbook.net

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) gitbook.net

PostgreSQL誻柊冕庖嘷嬉臺埖䔇嚔哋施閘傖埪嘷嬉䔇垂鍙施閘䔇䂸閘臖庘昄赆脄䫘䔇庘昄誫啂㔗認底庘昄寙拸 gitbook.net

庘昄	柟誄
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().

gitbook.net

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

Function

Description

DATE_PART('field', source)

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

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.gitbook.net

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.

gitbook.net

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

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

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) gitbook.net

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

gitbook.net

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) gitbook.net

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.

gitbook.net

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.gitbook.net

傖婋滇EXTRACT('field', source) 庘昄䔇冋床:

gitbook.net

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) gitbook.net

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.gitbook.net

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) gitbook.net

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.gitbook.net

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.gitbook.net

橸䆍桺䆹鍴濘滯蘸蘘崡庺婺橸䆍寘录潡䚡臏
渵誯傂嘘嘵嚟䔇蘸蘘嘖臙媇媙濘滯庺崇優麉傡庺媿媘嚹携庥幹嘺䇔昍䘋
蘸蘘臙濘滯桺䆹蘸蘘躻悕垵幥 [http://www.gitbook.net]
橸桺湺鵻PostgreSQL斖橘施閘DATE/TIME
蘸蘘臙媺䘍寘桺鷆毖:http://www.gitbook.net/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)