PG processing time is very flexible, +-*/both have support
It's useful to have a extract function in the meantime, let's take a look at a few examples: [code]
postgres=# Select Extract (Epoch from ' 1970-01-01 ':: timestamp);
Date_part
-----------
0
(1 row)
postgres=# Select Extract (Epoch from ' 1970-01-01 00:00:01 ':: timestamp);
Date_part
-----------
1
(1 row)
postgres=# Select Extract (Epoch from now ()-' 2013-07-01 ':: timestamp);
Date_part
----------------
1936767.072764
(1 row)
postgres=#
[/code] The above example is to find out the number of seconds starting from 1970-01-01 00:00:00
The function of the extract function is to extract the corresponding fields from the time
His use of the format:
EXTRACT (field from source)
Where field contains the following values:
Century: Century
postgres=# Select Extract (Century from ' 2013-07-01 '::d ate);
Date_part
-----------
21st
(1 row)
Day: The first days of one months [code]
postgres=# Select Extract (Day from ' 2013-07-23 '::d ate);
Date_part
-----------
23
(1 row)
postgres=# Select Extract (Day from ' 2013-07-23 09:15:23 ':: timestamp);
Date_part
-----------
23
(1 row)
postgres=# Select Extract (Day from ' 2013-07-23 09:15:23 '::d ate);
Date_part
-----------
23
(1 row)
postgres=# Select Extract (Day from interval ' 3 hours ');
Date_part
-----------
40
(1 row)
[/code]Decade:10, number of years 10
postgres=# Select Extract (decade from ' 2013-07-23 09:15:23 '::d ate);
Date_part
-----------
201
(1 row)
Dow Day of the week (Sunday =0 saturday=6)
postgres=# Select Extract (Dow from ' 2013-07-23 09:15:23 '::d ate);
Date_part
-----------
2
(1 row)
postgres=# Select Extract (Dow from ' 2013-07-21 09:15:23 '::d ate);
Date_part
-----------
0
(1 row)
Doy: The first day of the year(1-365/366)
postgres=# Select Extract (Doy from ' 2013-07-21 09:15:23 '::d ate);
Date_part
-----------
202
(1 row)
Hour: Hours of the day (0-23)
postgres=# SELECT EXTRACT (HOUR from TIMESTAMP ' 2013-07-21 09:15:23 ');
Date_part
-----------
9
(1 row)
postgres=# Select Extract (Hour from ' 2013-07-21 09:15:23 '::d ate);
Date_part
-----------
0
(1 row)
Notice here, because we have the ' 2013-07-21 09:15:23 '::d ate) to the date type is no hour, so return 0, the upper timestamp is an hour, correctly returned
Isodow:iso standard Days of the week sunday=7 Monday=1
postgres=# Select Extract (Isodow from ' 2013-07-21 09:15:23 '::d ate);
Date_part
-----------
7
(1 row)
postgres=# Select Extract (Dow from ' 2013-07-21 09:15:23 '::d ate);
Date_part
-----------
0
(1 row)
postgres=# Select Extract (Isodow from ' 2013-07-21 09:15:23 ':: timestamp);
Date_part
-----------
7
(1 row)
Year of the Isoyear:iso Standard: (
The ISO standard year is starting from Monday, with the beginning of the new Year of Monday before January 4, which differs from the A.D., so the first few days of January, or the days after December, may differ from the A.D.:
postgres=# Select Extract (isoyear from ' 2013-01-01 '::d ate);
Date_part
-----------
2013
(1 row)
postgres=# Select Extract (isoyear from ' 2012-12-31 '::d ate);
Date_part
-----------
2013
(1 row)
postgres=# Select Extract (isoyear from ' 2012-12-30 '::d ate);
Date_part
-----------
2012
(1 row)
postgres=# Select Extract (Dow from ' 2012-12-31 '::d ate);
Date_part
-----------
1
(1 row)
microseconds: microseconds
The part of the second that is identified in microseconds, including the following fractional parts:
postgres=# Select Extract (microseconds from interval ' 3 days 5 mins 3.5 sec ');
Date_part
-----------
3500000
(1 row)
postgres=# Select Extract (microseconds from ' 2013-07-21 09:15:23 ':: timestamp)
postgres-#;
Date_part
-----------
23000000
(1 row)
Millennium: Millennium, Millennium Year
It is 21st century, the 3rd millennium
postgres=# Select Extract (Millennium from ' 2013-07-21 09:15:23 ':: timestamp);
Date_part
-----------
3
(1 row)
minute: minutes (0-59)
postgres=# Select extract (minute from ' 2013-07-21 09:15:23 ':: timestamp);
Date_part
-----------
15
(1 row)
Month : Returns 1-12 for the timestamp type, and returns 0-11 for the interval type
postgres=# Select Extract (month from ' 2013-07-21 09:15:23 ':: timestamp);
Date_part
-----------
7
(1 row)
postgres=# Select Extract (Month from interval ' 7 months 5 days ');
Date_part
-----------
7
(1 row)
postgres=# Select Extract (Month from interval ' 5 days ');
Date_part
-----------
0
(1 row)
postgres=# Select Extract (month from interval ' months 5 days ');
Date_part
-----------
0
(1 row)
postgres=# Select Extract (month from interval ' One months 5 days ');
Date_part
-----------
11
(1 row)
Quarter: Quarterly
postgres=# Select Extract (Quarter from ' 2013-07-21 09:15:23 ':: timestamp);
Date_part
-----------
3
(1 row)
postgres=# Select Extract (Quarter from ' 2013-06-21 09:15:23 ':: timestamp);
Date_part
-----------
2
(1 row)
Second: seconds (0-59)
postgres=# Select Extract (second from ' 2013-06-21 09:15:23 ':: timestamp);
Date_part
-----------
23
(1 row)
Week: Zhou Kee
postgres=# Select Extract (Week from ' 2013-06-21 09:15:23 ':: timestamp);
Date_part
-----------
25
(1 row)
Year : Age
postgres=# Select Extract (Year from ' 2013-06-21 09:15:23 ':: timestamp);
Date_part
-----------
2013
(1 row)
PG Extract Function Example