Introduction to PostgreSQL time and date function usage

First, get the system time function

1.1 Get current full time

code is as follows	copy code
Select now (); david=# Select Now ();               now              ------------------------- ------  2013-04-12 15:39:40.399711+08 (1 row) david=# Current_timestamp is equivalent to the now () function. david=# Select Current_timestamp                now              ------- ------------------------  2013-04-12 15:40:22.398709+08 (1 row) david=# 1.2

Get Current date

The code is as follows	Copy Code
Select Current_date; david=# Select Current_date; Date ------------ 2013-04-12 (1 row) david=# 1.3

Get current time

The code is as follows	Copy Code
Select Current_time; david=# Select Current_time; Timetz -------------------- 15:43:31.101726+08 (1 row) David= #二,

Calculation of the time

The code is as follows	Copy Code
david=# Select Now (); Now ------------------------------- 2013-04-12 15:47:13.244721+08 (1 row) David= #2.1

Two years later

The code is as follows

Copy Code

david=# Select Now () + interval ' 2 years '; ? column? ------------------------------- 2015-04-12 15:49:03.168851+08 (1 row) david=# Select Now () + interval ' 2 year '; ? column? ------------------------------- 2015-04-12 15:49:12.378727+08 (1 row) david=# Select Now () + interval ' 2 y '; ? column? ------------------------------ 2015-04-12 15:49:25.46986+08 (1 row) david=# Select Now () + interval ' 2 Y '; ? column? ------------------------------- 2015-04-12 15:49:28.410853+08 (1 row) david=# Select Now () + interval ' 2Y '; ? column? ------------------------------- 2015-04-12 15:49:31.122831+08 (1 row) david=# 2.2

One months later

The code is as follows	Copy Code
david=# Select Now () + interval ' 1 month '; ? column? ------------------------------ 2013-05-12 15:51:22.24373+08 (1 row) david=# Select Now () + interval ' one month '; Error:invalid input syntax for type interval: "One month" Line 1:select Now () + interval ' one month '; ^ david=# 2.3

Three weeks ago

code is as follows	copy code
david=# Select Now ()-Interval ' 3 week ';       ;     ?column?            - ------------------------------  2013-03-22 16:00:04.203735+08 (1 row) david=# 2.4 10 minutes later david=# Select Now () + ' min ';                            ?column?             -------------------------------  2013-04-12 16:12:47.445744+08 (1 row) david=#

Description

Interval can not write, its value can be:

abbreviation meaning
Y Years
M Months (in the date part)
W Weeks
D days
H Hours
M Minutes (in the time part)
S Seconds

2.5 Calculating two time difference

Use Age (timestamp, timestamp)

The code is as follows	Copy Code
david=# Select Age (Now (), timestamp ' 1989-02-05 '); Age ---------------------------------------- Years 2 Mons 7 days 17:05:49.119848 (1 row) david=# david=# Select Age (Timestamp ' 2007-09-15 '); Age ------------------------ 5 years 6 Mons (1 row) david=#

Iii. Interception of Time fields

In the development process, often to take the date of the year, month, day, hour equivalent, PostgreSQL provides a very convenient extract function.

The EXTRACT (field from Source) field represents the time object that is taken, the source represents the date from which it was taken, the type is timestamp, time, or interval.

3.1 Year taken

The code is as follows

Copy Code

david=# Select Extract (Year from now ()); Date_part ----------- 2013 (1 row) david=# 3.2 Month david=# Select Extract (Month from now ()); Date_part ----------- 4 (1 row) david=# david=# Select Extract (Day from timestamp ' 2013-04-13 '); Date_part ----------- 13 (1 row) david=# david=# SELECT EXTRACT (Day from INTERVAL ' 1 minute '); Date_part ----------- 40 (1 row) david=# 3.3 View today is the first day of the year david=# Select Extract (Doy from now ()); Date_part ----------- 102 (1 row) david=# 3.4 View the number of seconds now from 1970-01-01 00:00:00 UTC david=# Select Extract (Epoch from now ()); Date_part ------------------ 1365755907.94474 (1 row) david=# 3.5 Converts the epoch value back to the timestamp david=# SELECT TIMESTAMP with time ZONE ' epoch ' + 1369755555 * INTERVAL ' 1 second '; ? column? ------------------------ 2013-05-28 23:39:15+08 (1 row) david=# --Take Day Skytf=> Select Extract (Day from now ()); Date_part ----------- 3 (1 row) Skytf=> Select Extract (Day from timestamp ' 2011-06-03 '); Date_part ----------- 3 (1 row) Skytf=> Select timestamp ' 2011-06-03 '; Timestamp --------------------- 2011-06-03 00:00:00 (1 row) --Take an hour Skytf=> Select Extract (Hour from now ()); Date_part ----------- 14 (1 row) --Take minutes Skytf=> Select extract (minute from now ()); Date_part ----------- 59 (1 row) --Take seconds Skytf=> Select Extract (second from now ()); Date_part ----------- 46.039333 (1 row) --which week is it taken? Skytf=> Select Extract (Week from now ()); Date_part ----------- 22 (1 row)

The above is the basic PG Time/Date function used to meet the general development of operation and maintenance applications